Oracle Profiles
While the locking and expiring of user accounts, through commands, is the first step in securing who can connect to an Oracle database, these single commands are more for, in my opinion, for use in singular and pointed scenarios. You might find a user abusing their account so you immediately lock it. When we talk about security practices as a whole, it is much better to look at setting guidelines or rules of use upfront and then let some process monitor and implement the security constraints. In Oracle, for password usage, there are various initialization and profile parameters that can be set to help enforce password usage through profiles. Profiles are nothing more than a collection of rules that will apply to the users that profile is assigned to. When we create a user we assign a profile very simply by:
SQL> CREATE USER auser IDENTIFIED BY apassword DEFAULT TABLESPACE dfts TEMPORARY TABLESPACE tpts PROFILE aprofile;
Now, depending on what that PROFILE (aprofile) contains will determine a set of rules or limits on resources or password usage for the user—noting that if you don’t declare a profile for a user then the user is assigned the DEFAULT PROFILE. You can easily look at the password type rules and limits for the default profile, or any other profile you may create, by:
SQL> SELECT * FROM dba_profiles
2 WHERE profile = 'DEFAULT'
3 AND resource_type = 'PASSWORD';
PROFILE RESOURCE_NAME RESOURCE LIMIT
---------- -------------------------------- -------- ----------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
7 rows selected.
So the question quickly becomes how do I create a profile? The following is for password resource types profile options:
CREATE PROFILE profile LIMIT{ { FAILED_LOGIN_ATTEMPTS | PASSWORD_LIFE_TIME | PASSWORD_REUSE_TIME | PASSWORD_REUSE_MAX | PASSWORD_LOCK_TIME | PASSWORD_GRACE_TIME } { expr | UNLIMITED | DEFAULT } | PASSWORD_VERIFY_FUNCTION { function | NULL | DEFAULT }};
As you can see each of the rows returned in the query from DBA_PROFILES is represented here. While the parameters seem semi-self explanatory a brief explanation is in order:
| FAILED_LOGIN_ATTEMPTS | if a user attempts to login more than the specified number of times the account will be locked. Default is 10 days. |
| PASSWORD_LIFE_TIME | number of days the same password can be used unless a grace period is specified. Default is 108 days. |
| PASSWORD_REUSE_TIME | number of days that must pass before the same password can be used again. Default is unlimited. |
| PASSWORD_REUSE_MAX | number of times a password must be changed before a previous password can be used again. Default is unlimited. |
| PASSWORD_LOCK_TIME | number of days an account will remain locked after failed login attempts has been triggered. Default is 1 day. |
| PASSWORD_GRACE_TIME | number of grace days for user to change password. Default is 7 days. |
| PASSWORD_VERIFY_FUNCTION | allows you to define PL/SQL that can be used for password verification. |
To create a profile, using the above parameters, we could issue the following to create a very restrictive profile that only give one failed login attempt, must be changed after 30 days, will lock for 7 days if a failed login attempt is made, and has no grace period. While this may seem very very restrictive, you can imagine, hopefully, that this just might be a profile used for very sensitive and administrative accounts. Granted, you might not want to use this on “known” accounts as someone could easily lock up your system for 7 days unless you had another administrative account that you could login with to unlock the locked accounts.
SQL> CREATE PROFILE restrictive
LIMIT FAILED_LOGIN_ATTEMPTS 1
PASSWORD_LIFE_TIME 30
PASSWORD_LOCK_TIME 7
PASSWORD_GRACE_TIME 0;
You could now use this profile by assigning it to an existing user or when you create a new user. Above, in this article, was shown how this could be done through the CREATE USER command but you can also use the ALTER USER command to assign or reassign profiles:
SQL> ALTER USER scott PROFILE restrictive;
User altered.
Now if user scott tries to login with the wrong password just once, he will get locked out immediately even if scott supplies the right password the second time.
SQL> connect scott/badpwd
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect scott/tiger
ERROR:
ORA-28000: the account is locked
Now it is up to the user scott to plead with the database administrator to unlock the account with:
SQL> ALTER USER scott ACCOUNT UNLOCK;
User altered.
If you ever wanted to assign the user scott the DEFAULT profile it is just another ALTER USER command that designates ‘PROFILE default’.
SQL> ALTER USER scott PROFILE default;
User altered.
Profiles can be altered with the ALTER PROFILE command. So if you didn’t like the default profile you could just issue an ALTER command such as:
SQL> ALTER PROFILE default
LIMIT PASSWORD_REUSE_TIME 3
PASSWORD_REUSE_MAX unlimited;
