Oracle 11g Security - The Power of PROFILES

How many users do you have in your database? Are they all being used? Come for a ride as we trim down the installed base of Oracle default users.

Within the last security article, we took a look at expiring and locking user accounts. 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, 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 up front and then letting some process monitor and implement the security constraints.

In Oracle, for password usage, the 2 Day+ Security Guide introduces 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:

To create a profile, using the above parameters, we could issue the following to create a very restrictive profile that only allows 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 creating a new user. Earlier in this article we discussed 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 be 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

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;

Dropping profiles requires that there be no users assigned to that profile unless you want to use the CASCADE option. Using the CASCADE option will automatically revert the effected users back to using the DEFAULT profile.

SQL> DROP PROFILE restrictive;
DROP PROFILE restrictive
*
ERROR at line 1:
ORA-02382: profile RESTRICTIVE has users assigned, cannot drop without CASCADE

To find out which users are assigned to a particular profile you need only query the DBA_USER view:

SQL> SELECT username,profile FROM dba_users WHERE username = 'SCOTT';

USERNAME                       PROFILE
------------------------------ ------------------------------
SCOTT                          DEFAULT

Using profiles for password management is an effective way to restrict unsolicited database usage. While it could prove to be a nuisance for users at times, especially when someone purposefully locks their account, it does thwart off multiple attempts and limit access. Every DBA should verify the password restrictions and setup an appropriate number of profiles for the groups or types of users within the database.

» See All Articles by Columnist James Koopmann