![Oracle 12c STIG Password Generator in PL/SQL 20188367001]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Oracle 12c STIG Password Generator in PL/SQL 1")
Creating or modifying an Oracle Database user password can be done by using any standard password generator you can find on the WEB. But I wanted a password to comply to “ora12c_strong_verify_function”, and this isn’t as easy to generate as you might expect. Though most generators provide options to include or exclude certain character groups, I wasn’t able to find one that will let you choose options in any detail and also let you set specific requirements.
So I took up the idea to code a PL/SQL based password generator that is as flexible as possible in the choice of included or excluded characters and can be configured to comply to Oracle requirements.
This is what the Oracle 12c documentation states in respect to the “ora12c_strong_verify_function”:
The ora12c_strong_verify_function provides a set of requirements that are recommended by the Department of Defense Database Security Technical Implementation Guide. This function checks for the following requirements when users create or modify passwords:
1. The password must contain at least two upper case characters, two lower case characters, two numeric characters, and two special characters. Specials are: ‘ ~ ! @ # $ % ^ & * ( ) _ – + = { } [ ] \ / < > , . ; ? ‘ : | (space)
2. The password must differ from the previous password by at least four characters.The following internal checks are also applied:
3. The password contains no fewer than 9 characters and does not exceed 30 characters.
4. The password does not contain the double-quotation character (“). It can be surrounded by double-quotation marks, however.
What kind of Oracle password:
I’m familiar with the fact that Oracle passwords can be max 30 characters long, must
start with an uppercase or lowercase letter and – with numbers and letters – may contain only
3 special characters: # $ _
But what I discovered after some investigation and didn’t know yet, is that the restriction on start
and special characters is off when you encase the password in double quotes. This kind of Oracle
password now seems better practice to me because of the extra complexity, but I would still like
to avoid some tricky characters for easier command line or shell script password management:
` ‘ ^ @ ” (space)
About exclusion (or inclusion) of characters:
For easy configuration I decided to use a regular table with the first 127 ascii codes and their
respective characters. Some extra – true (1) or false (0) – colums serve as profiles for [ex|in]clusion
of specific characters.
I reckoned a random number generator, configured to produce an integer between 0 and 127, should be
able to produce – in combination with Oracle supplied function CHR – any character in the ascii table.
And If it happens to generate an excluded ascii code, well, I simply let the PL/SQL try again until
the generator hits an included ascii code.
How about password verification:
To address some specific STIG requirements like a minimum number of specific characters, as before, my
coding strategy is to apply a brute force tactic. The algorithm keeps on generating passwords until
one finally fulfills all requirements. This method of “retry until OK” could be time consuming.
Well, it isn’t… check this out:
Passwords generated with ORA_STIG01 profile may contain all characters with ascii code 32 to and including 126.
The only exclusion is the double-quoted mark with ascii code 34: ”
Default length: 14 characters
SQL> set timing on SQL> select ams_password_pck.get_password_stig ( P_ora_stig => 1 ) from dual; AMS_PASSWORD_PCK.GET_PASSWORD_STIG(P_ORA_STIG=>1) ---------------------------------------------------------------------------------------------------- 81EPW]76z,q_i$ 1 row selected. Elapsed: 00:00:00.00 SQL> / AMS_PASSWORD_PCK.GET_PASSWORD_STIG(P_ORA_STIG=>1) ---------------------------------------------------------------------------------------------------- _2BtA_>VmdA58u 1 row selected. Elapsed: 00:00:00.00
Passwords generated with ORA_STIG02 profile are like passwords generated with ORA_STIG01 profile, but with
five extra exclusions on top of the double-quoted mark: ` ‘ ^ @ (space)
Default length: 14 characters
SQL> select ams_password_pck.get_password_stig ( P_ora_stig => 2 ) from dual; AMS_PASSWORD_PCK.GET_PASSWORD_STIG(P_ORA_STIG=>2) ---------------------------------------------------------------------------------------------------- =DCg|Y}2DTxZu5 1 row selected. Elapsed: 00:00:00.01 SQL> / AMS_PASSWORD_PCK.GET_PASSWORD_STIG(P_ORA_STIG=>2) ---------------------------------------------------------------------------------------------------- 92y0IB|+(eEVk? 1 row selected. Elapsed: 00:00:00.00
ORA_DEF profile is meant for passwords without enclosed double quotes, and without any verification function.
Only numeric characters, uppercase and lowercase letters and 3 special characters are allowed: # $ _
In addition the password must start with an uppercase or lowercase letter.
Default length: 14 characters
SQL> select ams_password_pck.get_password( P_ora_def => 1 ) from dual; AMS_PASSWORD_PCK.GET_PASSWORD(P_ORA_DEF=>1) ---------------------------------------------------------------------------------------------------- h4srali4#z2BLh 1 row selected. Elapsed: 00:00:00.01 SQL> / AMS_PASSWORD_PCK.GET_PASSWORD(P_ORA_DEF=>1) ---------------------------------------------------------------------------------------------------- PdaDEFZu6$VR1o 1 row selected. Elapsed: 00:00:00.00 SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options (with complications) $
Before installing the following code in some schema, connect with sys first and grant this schema owner execute on sys.ora12c_strong_verify_function… use the code at your discretion:
CREATE TABLE AMS_ASCII
(
ASCII# NUMBER(3),
ASCIICHR VARCHAR2(3 CHAR) NOT NULL,
UPPERCHR NUMBER(1) DEFAULT 0 NOT NULL,
LOWERCHR NUMBER(1) DEFAULT 0 NOT NULL,
ORA_DEF NUMBER(1) DEFAULT 0 NOT NULL,
ORA_STIG01 NUMBER(1) DEFAULT 0 NOT NULL,
ORA_STIG02 NUMBER(1) DEFAULT 0 NOT NULL,
NUM NUMBER(1) DEFAULT 0 NOT NULL
);
CREATE UNIQUE INDEX AMS_ASCII_PK ON AMS_ASCII(ASCII#);
ALTER TABLE AMS_ASCII ADD (CONSTRAINT AMS_ASCII_PK PRIMARY KEY (ASCII#));
ALTER TABLE AMS_ASCII ADD (CONSTRAINT AMS_ASCII_CK06 CHECK (NUM in (0,1)));
ALTER TABLE AMS_ASCII ADD (CONSTRAINT AMS_ASCII_CK05 CHECK (ORA_STIG02 in (0,1)));
ALTER TABLE AMS_ASCII ADD (CONSTRAINT AMS_ASCII_CK04 CHECK (ORA_STIG01 in (0,1)));
ALTER TABLE AMS_ASCII ADD (CONSTRAINT AMS_ASCII_CK03 CHECK (ORA_DEF in (0,1)));
ALTER TABLE AMS_ASCII ADD (CONSTRAINT AMS_ASCII_CK02 CHECK (LOWERCHR in (0,1)));
ALTER TABLE AMS_ASCII ADD (CONSTRAINT AMS_ASCII_CK01 CHECK (UPPERCHR in (0,1)));
CREATE OR REPLACE TRIGGER AMS_ASCII_B4IU
BEFORE INSERT OR UPDATE OF ASCII# ON AMS_ASCII
FOR EACH ROW
BEGIN
:NEW.ASCIICHR := CHR(:NEW.ASCII#);
END;
/
SET DEFINE OFF;
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (0, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (1, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (2, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (3, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (4, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (5, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (6, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (7, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (8, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (9, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (10, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (11, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (12, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (13, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (14, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (15, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (16, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (17, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (18, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (19, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (20, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (21, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (22, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (23, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (24, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (25, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (26, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (27, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (28, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (29, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (30, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (31, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (32, 0, 0, 0, 1, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (33, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (34, 0, 0, 0, 0, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (35, 0, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (36, 0, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (37, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (38, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (39, 0, 0, 0, 1, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (40, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (41, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (42, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (43, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (44, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (45, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (46, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (47, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (48, 0, 0, 1, 1, 1, 1);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (49, 0, 0, 1, 1, 1, 1);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (50, 0, 0, 1, 1, 1, 1);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (51, 0, 0, 1, 1, 1, 1);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (52, 0, 0, 1, 1, 1, 1);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (53, 0, 0, 1, 1, 1, 1);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (54, 0, 0, 1, 1, 1, 1);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (55, 0, 0, 1, 1, 1, 1);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (56, 0, 0, 1, 1, 1, 1);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (57, 0, 0, 1, 1, 1, 1);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (58, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (59, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (60, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (61, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (62, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (63, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (64, 0, 0, 0, 1, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (65, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (66, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (67, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (68, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (69, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (70, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (71, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (72, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (73, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (74, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (75, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (76, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (77, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (78, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (79, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (80, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (81, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (82, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (83, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (84, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (85, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (86, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (87, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (88, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (89, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (90, 1, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (91, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (92, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (93, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (94, 0, 0, 0, 1, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (95, 0, 0, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (96, 0, 0, 0, 1, 0, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (97, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (98, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (99, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (100, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (101, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (102, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (103, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (104, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (105, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (106, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (107, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (108, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (109, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (110, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (111, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (112, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (113, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (114, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (115, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (116, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (117, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (118, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (119, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (120, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (121, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (122, 0, 1, 1, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (123, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (124, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (125, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (126, 0, 0, 0, 1, 1, 0);
Insert into AMS_ASCII (ASCII#, UPPERCHR, LOWERCHR, ORA_DEF, ORA_STIG01, ORA_STIG02, NUM) Values (127, 0, 0, 0, 0, 0, 0);
COMMIT
/
CREATE OR REPLACE PACKAGE Ams_Password_Pck
AS
--------------------------------------------------------------------------------
FUNCTION Get_Password
( P_length_password PLS_INTEGER := 14
, P_ora_def PLS_INTEGER := 0 )
RETURN VARCHAR2;
-----------------------------------------------------------------
-- Create an oracle password that is validated by ora12c_strong_verify_function
/****************************************************************************************************
The ora12c_strong_verify_function function provides a set of requirements that
are recommended by the Department of Defense Database Security Technical Implementation Guide.
This function checks for the following requirements when users create or modify passwords:
--1--
The password must contain at least:
two upper case characters,
two lower case characters,
two numeric characters, and
two special characters. These special characters are as follows:
‘ ~ ! @ # $ % ^ & * ( ) _ - + = { } [ ] \ / < > , . ; ? ' : | (space)
--2--
The password must differ from the previous password by at least four characters.
--3--
The following internal checks are also applied:
The password contains no fewer than 9 characters and does not exceed 30 characters.
The password does not contain the double-quotation character (").
It can be surrounded by double-quotation marks, however.
*****************************************************************************************************/
FUNCTION Get_Password_Stig
( P_old_password VARCHAR2 := NULL
, P_length_password PLS_INTEGER := 14
, P_ora_stig PLS_INTEGER := 2 )
RETURN VARCHAR2;
--------------------------------------------------------------------------------
END Ams_Password_Pck;
/
SHOW ERRORS;
CREATE OR REPLACE PACKAGE BODY Ams_Password_Pck
AS
--------------------------------------------------------------------------------
m CONSTANT NUMBER := 100000000;
m1 CONSTANT NUMBER := 10000;
b CONSTANT NUMBER := 31415821;
a NUMBER := TO_NUMBER( TO_CHAR( SYSTIMESTAMP, 'FFFFFFFFFF' ) );
--------------------------------------------------------------------------------
FUNCTION RANDOM
( r IN PLS_INTEGER := 127 )
RETURN PLS_INTEGER
IS
FUNCTION f
( p IN NUMBER
, q IN NUMBER)
RETURN NUMBER
IS
p1 NUMBER;
p0 NUMBER;
q1 NUMBER;
q0 NUMBER;
BEGIN
p1 := TRUNC( p / m1 );
p0 := MOD( p, m1 );
q1 := TRUNC( q / m1 );
q0 := MOD( q, m1 );
RETURN( MOD( ( MOD( p0 * q1 + p1 * q0, m1 ) * m1 + p0 * q0 ), m ) );
END f;
BEGIN
/* generate a random number and set it to be the new seed */
a := MOD( f( a, b ) + 1, m );
-- dbms_output.put_line(a);
/* return random integer between [0,r-1] */
RETURN ( TRUNC( ( TRUNC( a / m1 ) * r ) / m1 ));
END RANDOM;
--------------------------------------------------------------------------------
FUNCTION Get_Password
( P_length_password PLS_INTEGER := 14
, P_ora_def PLS_INTEGER := 0 )
RETURN VARCHAR2
IS
pwd VARCHAR2(30);
pwd_part PLS_INTEGER;
/* password length min 9, max 30 characters */
v_length_password PLS_INTEGER := CASE WHEN P_length_password < 9 THEN 9 WHEN P_length_password > 30 THEN 30
ELSE P_length_password END;
BEGIN
-- start with a letter ( upper or lower ) if generating a regular oracle password
IF P_ora_def = 1 THEN
LOOP
EXIT WHEN length(pwd) = 1;
pwd_part := random(127);
for i in (select asciichr chr
from ams_ascii
where ( upperchr = 1
or lowerchr = 1 )
and ascii# = pwd_part )
loop
pwd := pwd||i.chr;
end loop;
END LOOP;
END IF;
LOOP
EXIT WHEN length(pwd) = v_length_password;
pwd_part := random(127);
/* use profile ora_def to include only letters, numbers and specials # $ _ */
if P_ora_def = 1 then
for i in (select asciichr chr
from ams_ascii
where ora_def = 1
and ascii# = pwd_part )
loop
pwd := pwd||i.chr;
end loop;
/* only exclude (space) and control characters */
else
for i in (select asciichr chr
from ams_ascii
where ascii# > 32
and ascii# = pwd_part )
loop
pwd := pwd||i.chr;
end loop;
end if;
END LOOP;
RETURN pwd;
END Get_Password;
---------------------------------------------------------------------------------------
FUNCTION Get_Password_Stig
( P_old_password VARCHAR2 := NULL
, P_length_password PLS_INTEGER := 14
, P_ora_stig PLS_INTEGER := 2 )
RETURN VARCHAR2
IS
pwd VARCHAR2(30);
pwd_part PLS_INTEGER;
/* password length min 9, max 30 characters */
v_length_password PLS_INTEGER := CASE WHEN P_length_password < 9 THEN 9 WHEN P_length_password > 30 THEN 30
ELSE P_length_password END;
BEGIN
LOOP
EXIT WHEN length(pwd) = v_length_password;
pwd_part := random(127);
/* use profile ora_stig01 to exclude special character " and control characters */
if P_ora_stig =1
then
for i in (select asciichr chr
from ams_ascii
where ora_stig01 = 1
and ascii# = pwd_part )
loop
pwd := pwd||i.chr;
end loop;
/* use profile ora_stig02 to exclude special characters "` ' ^ @ " (space) and control characters */
elsif P_ora_stig = 2
then
for i in (select asciichr chr
from ams_ascii
where ora_stig02 = 1
and ascii# = pwd_part )
loop
pwd := pwd||i.chr;
end loop;
end if;
END LOOP;
/* check if the new password contains two upper case, two lower case, two numeric,
and two special characters. if not... try again */
IF ( ( length(pwd) = trim(length(translate( pwd,' 01234',' '))) )
OR ( length(pwd) = trim(length(translate( pwd,' 56789',' '))) )
OR ( length(pwd) = trim(length(translate( pwd,' abcdefghijklm',' '))) )
OR ( length(pwd) = trim(length(translate( pwd,' nopqrstuvwxyz',' '))) )
OR ( length(pwd) = trim(length(translate( pwd,' ABCDEFGHIJKLM',' '))) )
OR ( length(pwd) = trim(length(translate( pwd,' NOPQRSTUVWXYZ',' '))) )
OR ( length(pwd) = trim(length(translate( pwd,' !#$%^*()_-+={',' '))) )
OR ( length(pwd) = trim(length(translate( pwd,' }[]\/<>,.;?:|',' '))) )
) THEN
pwd := Get_Password_Stig ( P_old_password
, P_length_password
, P_ora_stig );
END IF;
/* if P_old_password is not null, check if the new password differs at least 4
characters from the old one. if not, this function raises an application error */
IF sys.ora12c_strong_verify_function (NULL,pwd,P_old_password) then
RETURN pwd;
END IF;
RETURN ('NO VALUE');
END Get_Password_Stig;
---------------------------------------------------------------------------------------
END Ams_Password_Pck;
/
SHOW ERRORS;
Very usefull, thanks for time and sharing