Profile is a set of limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits.
Profiles are created by DBA and assigned to the users by DBA only.
Only one profile can be assigned to one user account.
Profile is nothing but security properties.
If didn't assign any profile to user default profile will be there.
To create a profile, you must have the CREATE PROFILE system privilege.
To specify resource limits for a user, you must:
Enable resource limits dynamically with the ALTER SYSTEM statement or with the initialization parameter RESOURCE_LIMIT. This parameter does not apply to password resources. Password resources are always enabled.
Create a profile that defines the limits using the CREATE PROFILE statement
Assign the profile to the user using the CREATE USER or ALTER USER statement
Profile has two types of parameters
A. Resource Parameters:
B. Password Parameters
For function, specify the name of the password complexity verification routine.
Specify NULL to indicate that no password verification is performed.
Profile Creation Syntax:
CREATE PROFILE <PROFILE-NAME> LIMIT <RESOURCE PARAMETER OR PASSWORD PARAMETER> VALUE<INTEGER OR DEFAULT OR UNLIMITED>;
EX:
SQL>CREATE PROFILE NEW_PROFILE LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 45
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K
COMPOSITE_LIMIT 5000000;
IDLE_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 5
PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 10;
Run below sql statement to Check DEFAULT PROFILE LIMITS:
SQL>select * from dba_profile where profile='DEFAULT';
RESOURCE_NAME LIMIT
---------------------------- -----------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
16 rows selected.
Run below sql statement to Check How many Profiles Exists in DB
SQL> select profile from dba_profiles;
PROFILE
------------------------
EBS_APPS
TRAINEE
AD_PATCH_MONITOR_PROFILE
EM_OAM_MONITOR_PROFILE
DEFAULT
Run below sql statement to Check How many Profiles assigned to Users in DB
SQL> select distinct profile from dba_users;
PROFILE
------------------------
EBS_APPS
TRAINEE
AD_PATCH_MONITOR_PROFILE
EM_OAM_MONITOR_PROFILE
DEFAULT
SQL> desc dba_profiles;
Name Null? Type
----------------------------------------- -------- --------------
PROFILE NOT NULL VARCHAR2(128)
RESOURCE_NAME NOT NULL VARCHAR2(32)
RESOURCE_TYPE VARCHAR2(8)
LIMIT VARCHAR2(128)
COMMON VARCHAR2(3)
SQL> column limit format a10;
SQL> column resorce_name format a30;
Run below sql to Check newly create PROFILE LIMITS:
SQL> select resource_name,limit from dba_profiles where profile='TRAINEE';
RESOURCE_NAME LIMIT
------------------------------ ----------
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER 2
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME 5
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 30
PASSWORD_REUSE_TIME 2
PASSWORD_REUSE_MAX 3
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME DEFAULT
PASSWORD_GRACE_TIME 5
16 rows selected.
Changing Limit for a Resource:
SQL> alter profile trainee limit password_life_time 40;
Profile altered.
SQL> select resource_name,limit from dba_profiles where profile='TRAINEE';
RESOURCE_NAME LIMIT
------------------------------ ----------
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER 2
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME 5
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 40
PASSWORD_REUSE_TIME 2
PASSWORD_REUSE_MAX 3
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME DEFAULT
PASSWORD_GRACE_TIME 5
16 rows selected.
Assigning Profile to User:
SQL> select profile from dba_users where username='SREENU';
PROFILE
--------------------------------------------------------------------------------
DEFAULT
SQL> alter user SREENU profile trainee;
User altered.
SQL> select profile from dba_users where username='SREENU';
PROFILE
--------------------------------------------------------------------------------
TRAINEE
Profiles and Password Verify Function:
Remember the password verification function in Oracle Database? Many of you may not be even aware of its existence, let alone use it. The function is a quick and easy way to enforce quality of database passwords—for example, they should contain a certain number of characters, should not be identical to the username, and so on. Perhaps its best feature is that it is built-in; all you have to do is turn it on. More likely than not, you didn't.
In Oracle Database 11g/12c the password management function has new and improved verification logic. If you examine the password verification file utlpwdmg.sql in $ORACLE_HOME/rdbms/admin, you will notice that the script creates a new password function called verify_fnction_11g / ora12c_verify_function. At the end, the script has the following lines:
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;
The script attaches the function to the profile DEFAULT, which is the default profile for all users, unless something else is explicitly assigned. This makes the authentication compliant with many regulations. All you have to do is run this script to create the 11g/12c version of the password checking function, and the script will enable the password verification feature by attaching itself to the default profile.
[oradb1@test ~]$ cd $ORACLE_HOME/rdbms/admin
[oradb1@test admin]$ pwd
/u03/oradb1/VIS/12.1.0/rdbms/admin
[oradb1@test admin]$ ls utlpwd*
utlpwdmg.sql (Password management Utility)
SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';
RESOURCE_NAME LIMIT
---------------------------- -----------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
16 rows selected.
[oradb1@test admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 1 12:42:33 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @?/rdbms/admin/utlpwdmg.sql (To enable pw_verify_function)
Function created. (Here ?=$ORACLE_HOME)
Grant succeeded.
Function created.
Grant succeeded.
Function created.
Grant succeeded.
Profile altered.
SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';
RESOURCE_NAME LIMIT
---------------------------- -----------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
16 rows selected.
Example for Password Verify Function:
sys SQL> alter user SREENU password expire;
User altered.
user
[oradb1@test ~]$ sqlplus SREENU/sreenu123;
SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 1 13:22:35 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-28001: the password has expired
Changing password for sreenu
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8
Password unchanged
Enter user-name: sreenu/sreenu123
ERROR:
ORA-28001: the password has expired
Changing password for sreenu
New password:
Retype new password:
ERROR:
ORA-28007: the password cannot be reused
Password unchanged
Errors:
ORA-20025: Password must contain at least 1 digit(s)
ORA-20022: Password must contain at least 1 letter(s)
ORA-20002: Password contains the username
To Disable Password_verify_function:
SQL> alter profile default limit password_verify_function null;
Profile altered.
SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';
RESOURCE_NAME LIMIT
------------------------------ ----------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
16 rows selected.
Now we can check
[oradb1@test ~]$ sqlplus sreenu/sreenu123
SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 1 13:31:27 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-28001: the password has expired
Changing password for sreenu
New password:
Retype new password:
Password changed
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show user
USER is "SREENU"
SQL> password
Changing password for SREENU
Old password:
New password:
Retype new password:
Password changed
Profiles are created by DBA and assigned to the users by DBA only.
Only one profile can be assigned to one user account.
Profile is nothing but security properties.
If didn't assign any profile to user default profile will be there.
To create a profile, you must have the CREATE PROFILE system privilege.
To specify resource limits for a user, you must:
Enable resource limits dynamically with the ALTER SYSTEM statement or with the initialization parameter RESOURCE_LIMIT. This parameter does not apply to password resources. Password resources are always enabled.
Create a profile that defines the limits using the CREATE PROFILE statement
Assign the profile to the user using the CREATE USER or ALTER USER statement
Profile has two types of parameters
A. Resource Parameters:
- SESSIONS_PER_USER:Specify the number of concurrent sessions to which you want to limit the user.
- CPU_PER_SESSION:Specify the CPU time limit for a session ,expressed in hundredth of seconds.
- CPU_PER_CALL:Specify the CPU time limit for a call(a parse,execute or fetch),expressed in hundredth of seconds.
- CONNECT_TIME:Specify the total elapsed time limit for a session ,expressed in minutes.
- LOGICAL_READS_PER_SESSION:Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.
- LOGICAL_READS_PER_CALL:Specify the permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
- PRIVATE_SGA:Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA).
- COMPOSITE_LIMIT:Specify the total resource cost for a session, expressed in service units. Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.
- IDLE_TIME:Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
B. Password Parameters
- FAILED_LOGIN_ATTEMPTS:Specify the number of failed attempts to log in to the user account before the account is locked.
- PASSWORD_LIFE_TIME:Specify the number of days the same password can be used for authentication. If you also set a value for PASSWORD_GRACE_TIME, the password expires if it is not changed within the grace period, and further connections are rejected. If you do not set a value for PASSWORD_GRACE_TIME, its default of UNLIMITED will cause the database to issue a warning but let the user continue to connect indefinitely.
- PASSWORD_REUSE_TIME & PASSWORD_REUSE_MAX:These two parameters must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. For these parameter to have any effect, you must specify an integer for both of them.
If you specify an integer for both of these parameters, then the user cannot reuse a password until the password has been changed the password the number of times specified for PASSWORD_REUSE_MAX during the number of days specified for PASSWORD_REUSE_TIME.
For example, if you specify PASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 10, then the user can reuse the password after 30 days if the password has already been changed 10 times.
If you specify an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password.
If you specify DEFAULT for either parameter, then Oracle Database uses the value defined in the DEFAULT profile. By default, all parameters are set to UNLIMITED in the DEFAULT profile. If you have not changed the default setting of UNLIMITED in the DEFAULT profile, then the database treats the value for that parameter as UNLIMITED.
If you set both of these parameters to UNLIMITED, then the database ignores both of them.
For example, if you specify PASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 10, then the user can reuse the password after 30 days if the password has already been changed 10 times.
If you specify an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password.
If you specify DEFAULT for either parameter, then Oracle Database uses the value defined in the DEFAULT profile. By default, all parameters are set to UNLIMITED in the DEFAULT profile. If you have not changed the default setting of UNLIMITED in the DEFAULT profile, then the database treats the value for that parameter as UNLIMITED.
If you set both of these parameters to UNLIMITED, then the database ignores both of them.
- PASSWORD_VERIFY_FUNCTION:The PASSWORD_VERIFY_FUNCTION clause lets a PL/SQL password complexity verification script be passed as an argument to the CREATE PROFILE statement. Oracle Database provides a default script, but you can create your own routine or use third-party software instead.
For function, specify the name of the password complexity verification routine.
Specify NULL to indicate that no password verification is performed.
- PASSWORD_LOCK_TIME:Specify the number of days an account will be locked after the specified number of consecutive failed login attempts.
- PASSWORD_GRACE_TIME:Specify the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.
Profile Creation Syntax:
CREATE PROFILE <PROFILE-NAME> LIMIT <RESOURCE PARAMETER OR PASSWORD PARAMETER> VALUE<INTEGER OR DEFAULT OR UNLIMITED>;
EX:
SQL>CREATE PROFILE NEW_PROFILE LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 45
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K
COMPOSITE_LIMIT 5000000;
IDLE_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 5
PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 10;
Run below sql statement to Check DEFAULT PROFILE LIMITS:
SQL>select * from dba_profile where profile='DEFAULT';
RESOURCE_NAME LIMIT
---------------------------- -----------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
16 rows selected.
Run below sql statement to Check How many Profiles Exists in DB
SQL> select profile from dba_profiles;
PROFILE
------------------------
EBS_APPS
TRAINEE
AD_PATCH_MONITOR_PROFILE
EM_OAM_MONITOR_PROFILE
DEFAULT
Run below sql statement to Check How many Profiles assigned to Users in DB
SQL> select distinct profile from dba_users;
PROFILE
------------------------
EBS_APPS
TRAINEE
AD_PATCH_MONITOR_PROFILE
EM_OAM_MONITOR_PROFILE
DEFAULT
SQL> desc dba_profiles;
Name Null? Type
----------------------------------------- -------- --------------
PROFILE NOT NULL VARCHAR2(128)
RESOURCE_NAME NOT NULL VARCHAR2(32)
RESOURCE_TYPE VARCHAR2(8)
LIMIT VARCHAR2(128)
COMMON VARCHAR2(3)
SQL> column limit format a10;
SQL> column resorce_name format a30;
Run below sql to Check newly create PROFILE LIMITS:
SQL> select resource_name,limit from dba_profiles where profile='TRAINEE';
RESOURCE_NAME LIMIT
------------------------------ ----------
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER 2
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME 5
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 30
PASSWORD_REUSE_TIME 2
PASSWORD_REUSE_MAX 3
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME DEFAULT
PASSWORD_GRACE_TIME 5
16 rows selected.
Changing Limit for a Resource:
SQL> alter profile trainee limit password_life_time 40;
Profile altered.
SQL> select resource_name,limit from dba_profiles where profile='TRAINEE';
RESOURCE_NAME LIMIT
------------------------------ ----------
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER 2
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME 5
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 40
PASSWORD_REUSE_TIME 2
PASSWORD_REUSE_MAX 3
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME DEFAULT
PASSWORD_GRACE_TIME 5
16 rows selected.
Assigning Profile to User:
SQL> select profile from dba_users where username='SREENU';
PROFILE
--------------------------------------------------------------------------------
DEFAULT
SQL> alter user SREENU profile trainee;
User altered.
SQL> select profile from dba_users where username='SREENU';
PROFILE
--------------------------------------------------------------------------------
TRAINEE
Profiles and Password Verify Function:
Remember the password verification function in Oracle Database? Many of you may not be even aware of its existence, let alone use it. The function is a quick and easy way to enforce quality of database passwords—for example, they should contain a certain number of characters, should not be identical to the username, and so on. Perhaps its best feature is that it is built-in; all you have to do is turn it on. More likely than not, you didn't.
In Oracle Database 11g/12c the password management function has new and improved verification logic. If you examine the password verification file utlpwdmg.sql in $ORACLE_HOME/rdbms/admin, you will notice that the script creates a new password function called verify_fnction_11g / ora12c_verify_function. At the end, the script has the following lines:
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;
The script attaches the function to the profile DEFAULT, which is the default profile for all users, unless something else is explicitly assigned. This makes the authentication compliant with many regulations. All you have to do is run this script to create the 11g/12c version of the password checking function, and the script will enable the password verification feature by attaching itself to the default profile.
[oradb1@test ~]$ cd $ORACLE_HOME/rdbms/admin
[oradb1@test admin]$ pwd
/u03/oradb1/VIS/12.1.0/rdbms/admin
[oradb1@test admin]$ ls utlpwd*
utlpwdmg.sql (Password management Utility)
SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';
RESOURCE_NAME LIMIT
---------------------------- -----------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
16 rows selected.
[oradb1@test admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 1 12:42:33 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @?/rdbms/admin/utlpwdmg.sql (To enable pw_verify_function)
Function created. (Here ?=$ORACLE_HOME)
Grant succeeded.
Function created.
Grant succeeded.
Function created.
Grant succeeded.
Profile altered.
SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';
RESOURCE_NAME LIMIT
---------------------------- -----------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
16 rows selected.
Example for Password Verify Function:
sys SQL> alter user SREENU password expire;
User altered.
user
[oradb1@test ~]$ sqlplus SREENU/sreenu123;
SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 1 13:22:35 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-28001: the password has expired
Changing password for sreenu
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8
Password unchanged
Enter user-name: sreenu/sreenu123
ERROR:
ORA-28001: the password has expired
Changing password for sreenu
New password:
Retype new password:
ERROR:
ORA-28007: the password cannot be reused
Password unchanged
Errors:
ORA-20025: Password must contain at least 1 digit(s)
ORA-20022: Password must contain at least 1 letter(s)
ORA-20002: Password contains the username
To Disable Password_verify_function:
SQL> alter profile default limit password_verify_function null;
Profile altered.
SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';
RESOURCE_NAME LIMIT
------------------------------ ----------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
16 rows selected.
Now we can check
[oradb1@test ~]$ sqlplus sreenu/sreenu123
SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 1 13:31:27 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-28001: the password has expired
Changing password for sreenu
New password:
Retype new password:
Password changed
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show user
USER is "SREENU"
SQL> password
Changing password for SREENU
Old password:
New password:
Retype new password:
Password changed
No comments:
Post a Comment