A user profile controls user password policies and resource control. Every user has an allocated profile.
The DEFAULT
profile will be assigned if another profile is not specified for a user. It is recommended that you use your own custom profiles to enforce password aging policies, strong passwords, and resource utilization. In this recipe, we will create a customized profile named CUSTPROF
that establishes a strong password policy through the use of password related profile resources and the use of a password verification function.
The profile CUSTPROF
will be assigned to the HR
user in the following steps:
CUSTPROF
using the following statement:SQL> create profile custprof limit password_reuse_max 15; Profile created.
CUSTPROF
, issue the following query:SQL> select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where profile='CUSTPROF'; PROFILE RESOURCE_NAME LIMIT ------------------------------ ------------------------------------------ CUSTPROF COMPOSITE_LIMIT DEFAULT CUSTPROF SESSIONS_PER_USER DEFAULT CUSTPROF CPU_PER_SESSION DEFAULT CUSTPROF CPU_PER_CALL DEFAULT CUSTPROF LOGICAL_READS_PER_SESSION DEFAULT CUSTPROF LOGICAL_READS_PER_CALL DEFAULT CUSTPROF IDLE_TIME DEFAULT CUSTPROF CONNECT_TIME DEFAULT CUSTPROF PRIVATE_SGA DEFAULT CUSTPROF FAILED_LOGIN_ATTEMPTS DEFAULT CUSTPROF PASSWORD_LIFE_TIME DEFAULT CUSTPROF PASSWORD_REUSE_TIME DEFAULT CUSTPROF PASSWORD_REUSE_MAX 15 CUSTPROF PASSWORD_VERIFY_FUNCTION DEFAULT CUSTPROF PASSWORD_LOCK_TIME DEFAULT CUSTPROF PASSWORD_GRACE_TIME DEFAULT 16 rows selected. SQL>
SQL> alter profile custprof limit PASSWORD_LIFE_TIME 50 PASSWORD_GRACE_TIME 10; Profile altered. SQL>
SQL> alter profile custprof limit FAILED_LOGIN_ATTEMPTS 15 PASSWORD_LOCK_TIME 3; Profile altered. SQL>
SQL> alter profile custprof limit PASSWORD_REUSE_TIME 20 PASSWORD_REUSE_MAX 1; Profile altered. SQL>
system
and create the password verify function, as follows:SQL> @?/rdbms/admin/utlpwdmg.sql Function created. Profile altered. Function created. SQL>
create or replace FUNCTION verify_function_11G (username varchar2, password varchar2, old_password varchar2) RETURN boolean IS n boolean; m integer; differ integer; isdigit boolean; ischar boolean; ispunct boolean; isspecchar boolean; ................................................. reverse_user varchar2(32); specialchars varchar2(30); BEGIN digitarray:= '0123456789'; chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; specialchars:= '~@#$%^&*()[];;.><?|:_=+-'; -- add special characters as manadatory -- Check for the minimum length of the password IF length(password) < 15 THEN raise_application_error(-20001, 'Password length less than 8'), END IF; 3. Check for the special characters isspecchar:=FALSE; FOR i IN 1..length(specialchars) LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(specialchars,i,1) THEN isspecchar:=TRUE; GOTO findspecialchar; END IF; END LOOP; END LOOP; IF isspecchar = FALSE THEN raise_application_error(-20009, 'Password must contain at least one digit, one character, and one special character such as ~@#$%^&*()[];;.><?|:_=+-'), END IF; <<findspecialchar>> END IF; -- Everything is fine; return TRUE ; RETURN(TRUE); END;
password_verify_function
resource, as follows:SQL> alter profile custprof limit password_verify_function verify_function_11g; Profile altered.
custprof
profile to the user HR
as follows:SQL> alter user hr profile custprof; User altered. SQL>
It is recommended that you have separate profiles for a group of users, such as application users, administrative users, and so on. In this way, very sensitive users may have more restrictive password policies than others.
Profiles can also be used to control user resource management by modifying the following resources:
CUSTPROF COMPOSITE_LIMIT DEFAULT CUSTPROF SESSIONS_PER_USER DEFAULT CUSTPROF CPU_PER_SESSION DEFAULT CUSTPROF CPU_PER_CALL DEFAULT CUSTPROF LOGICAL_READS_PER_SESSION DEFAULT CUSTPROF LOGICAL_READS_PER_CALL DEFAULT CUSTPROF PRIVATE_SGA DEFAULT
And we can set the maximum connection idle time and connect time by modifying the following resources:
CUSTPROF IDLE_TIME DEFAULT CUSTPROF CONNECT_TIME DEFAULT
3.22.181.47