Home » Oracle DBA » Creating Profiles In Oracle For User Security

Creating Profiles In Oracle For User Security

Profiles must first be created by the DBA using the CREATE PROFILE command. Profiles are used for two purposes, as a means of controlling resources used by an account and for enforcing password and other authentication rules. Here is an example of password and login control:
Create Profile crp_security_profile 
    failed_login_attempts 5
    password_lock_time 1
    password_life_time 90
   password_grace_time 3
   password_reuse_max 5
   password_reuse_time 365
   password_verify_function func_crp_pwd_vfy;
Most organizations will not necessarily implement all of the available password rules as shown in this exhaustive example. This command says that any user who has been assigned the crp_security_profile will have his or her account locked after five successive failed attempts to log in and that the account will remain locked for 24 hours unless explicitly unlocked by the DBA. Any password can be used for no longer than 90 days, but the user will receive the advance warnings for three days before the actual password expriation. A user's password can not be used until five other password changes have been made, and a password cant be reused within 365 days.
Finally, that profile specifies that all passwords will be validated using a verification function, named func_crp_pwd_vfy. This function must be created by the privileged user SYS in order to be used in profile.
Create or Replace Function func_crp_pwd_vfy
(in_username in varchar2,
in_new_password in varchar2,
in_old_password in varchar2)
return boolean
pwd_okay boolean;
-- you can wright your own validation...
if in_new_password = in_username then
  raise_application_error(-20001, 'Password may not be user name.');
end if;
return true;
end func_crp_pwd_vfy;