Define Your Own Security Policy using Oracle Profiles - Define Password Reuse, grace time, lifetime and other ploicies

What is Security Policy?

You would have seen with Oracle asking you for a password change automatically once per a month. Also when you try to provide a new password, Oracle would restrict you not to use old passwords. These are all security Policy.

Well I would say this as a "Set of rules to strengthen the system security across various group of users".

A Real Example:

Take a look at below requirement with a set of rules for defining the security policy in our system.

a) Security Policy for Offshore

  • Users should change their password once in a month with 5 days grace time.
  • Last three Passwords should not be used.
  • A password should not be reused in 1 months time span.
  • Session should expire after 20 idle minutes.
  • Do not allow more than 3 successive failure attempts for login.
  • Unlock a account 2 hours after the lock.

b) Security Policy for Onshore

  • Users should change their password once in two months with 10 days grace time.
  • Last five Passwords should not be used.
  • A password should not be reused in 2 months time span.
  • Session should expire after 20 idle minutes.
  • Do not allow more than 3 successive failure attempts for login.
  • Unlock a account 2 hours after the lock.

Watch the Example Here

Click thumbnail to view full-size

The Real life implementation

Well i hop the above sounds interesting. But how to implement? Profiles are one of the greatest features provided with Oracle that allows us to define security policies. No matter if you are not heard this before. Just go through the below steps and code to create the policy a.

CREATE PROFILE offshore_users LIMIT
PASSWORD_LIFE_TIME 30 -- Users should change their password once in 
                      -- a month.
PASSWORD_GRACE_TIME 5 -- Password grace time
PASSWORD_REUSE_MAX 3 -- Last three Passwords should not be used.
PASSWORD_REUSE_TIME 30 -- A password should not be reused in 
                       -- 1 months time span.
IDLE_TIME 20 -- Session should expire after 20 idle mins.
FAILED_LOGIN_ATTEMPTS 3 -- Do not allow more than 3 successive 
                        -- failure attempts for login.
PASSWORD_LOCK_TIME 2/24; -- Unlock a account 2 hours after the lock.

See the comment line at the end of each line (followed by ‘-‘ ), you can see each line defines the rules under policy.

Ok How I can check this. Do one of the following to check this.

a) Create a user and assign the profile as offshore_users.

 
create user peter identified by john123#
profile offshore_users;

b) To test the security policy defined, try changing the password

 
alter user peter identified by john1234#;
alter user peter identified by john123#;

Below is the output for the first alter user statement. As the password is not used earlier, this will be accepted.

alter user peter succeeded.
 

As the password with second alter user statement, is used during creation of user peter, it will be not accepted. You would encounter the below error.

Error starting at line 5 in command:
alter user peter
identified by john123#
profile offshore_users
Error report:
SQL Error: ORA-28007: the password cannot be reused
28007. 00000 - "the password cannot be reused"
*Cause: The password cannot be reused for the specified number of
days or for the specified number of password changes
*Action: Try the password that you have not used for the specified
number of days or the specified number of password changes
Refer to the password parameters in the CREATE PROFILE statement

Refer the pictures in slide show to see how the above example works.

Hope you understood well. With this, I leave the policy-b for you and would appreciate if you post the results here.

PCI Compliance

More by this Author


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working