Several customers are applying GDPR, General Data Protection Regulation, rules or security requests from CISO, Chief information security officer.
One of them is to enforce password on user or technical accounts as the following :
- A minimum of 1 lower case letter [a-z] and
- A minimum of 1 upper case letter [A-Z] and
- A minimum of 1 numeric character [0-9] and
- A minimum of 1 special character: ~`!@#$%^&*()-_+={}[]|\;: »<>,./?
- Passwords must be at least n characters in length.
- N attempts to block login
- Set password expiration to N days
Even with Oracle Database 12c, the quality of the database passwords is not enforced by default.
The first five criteria can be dealt using a password verify function. As a basis you can use the script utlpwdmg.sql to create your own, by example :
cat $ORACLE_HOME/rdbms/admin/utlpwdmg_mine.sql
. . .
CREATE OR REPLACE FUNCTION ora12c_verify_function_tech
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
differ integer;
pw_lower varchar2(256);
db_name varchar2(40);
i integer;
simple_password varchar2(10);
reverse_user varchar2(32);
BEGIN
IF NOT ora_complexity_check(password, chars => 15, letter => 1, digit => 1, upper => 1, lower => 1, special => 1) THEN
RETURN(FALSE);
. . .
Without modification, utlpwdmg.sql updates the profile DEFAULT, which is the default profile for all users. This is not necessarily recommended.
The other criteria have to be set on PROFILE, by sample :
SQL> CREATE PROFILE MINE LIMIT
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION ora12c_verify_function_tech;
. . .
According to the descriptions:
- PASSWORD_LIFE_TIME: Sets the number of days the user can use his current password.
- PASSWORD_GRACE_TIME: Sets the number of days that a user has to change his password before it expires.
- PASSWORD_REUSE_TIME: Sets the number of days before which a password cannot be reused.
- PASSWORD_REUSE_MAX: Sets the number of password changes required before the current password can be reused.
- FAILED_LOGIN_ATTEMPTS: Specify the number of failed attempts to log in to the user account before the account is locked.
- PASSWORD_LOCK_TIME: Specify the number of days an account will be locked after the specified number of consecutive failed login attempts.
Don’t forget to apply the profile to the accounts :
SQL> alter user USERNAME profile MINE;
It is important to monitor your users to avoid to lock your applications or your users before to set these rules. You can use Shinken Entreprise for example (https://www.shinken-enterprise.com/fr/accueil/).
What is the behaviour with the SYS account? Can we do « SQL> Alter user sys identified by Toto » with a password verify function?
We requested Oracle support help and created a SR : » SYS Profile not used ». After one month, we receive only the link on « ORA-28003, ORA-20001, ORA-20002, ORA-20003, ORA-20004 After Running utlpwdmg.sql ( Doc ID 124648.1 ) ». It is interesting but not our point …
Hopefully, I have found the right note « User SYS Does Not Get ORA-28002 Nor ORA-28001 Even When PASSWORD_LIFE_TIME or PASSWORD_GRACE_TIME are Set (Doc ID 289898.1) »
« The PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME parameters of a resource profile seem to be working for all the users but not for SYS
This is the expected behavior and it cannot be changed. »
And so, is it possible to lock SYS account?
The best answer is in a post from Ask TOM: https://asktom.oracle.com/pls/apex/asktom.search?tag=11gr2-profile-issue In summary :
« Definately a sys user password can not be expired or locked.
. . .
« as sysdba » is not SYS (it uses sys as the schema name) but you are NOT using the SYS user really, you are « as sysdba » which is totally different again.
You need to be able to use « as sysdba » even when the database isn’t up and running – it is done via the OS or the external password file. »
Setting the ORAPWD utility FORMAT parameter to 12.2 enables you to manage the password profile parameters for administrative users. It will be an other post.
In all case, store your password carefully !
References
- Password Complexity check for SYS user (Doc ID 2157218.1)
- RDBMS 12.2 – Users Being granted The ALTER USER System Privilege Cannot Change SYS Password (Doc ID 2263715.1)
- https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dbseg/configuring-authentication.html#GUID-00632C9C-5C01-4C8F-A4D0-5E575502A6AE
- https://www.oradba.ch/2013/07/oracle-12c-new-password-verify-function/