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 / implement any profile default profile will be there. Various companies has its own profile for different teams as their policy.
Click Here Click Here for Apps DBA Blog
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;
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
RESOURCE_NAME LIMIT
------------------------------ ----------
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> 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
RESOURCE_NAME LIMIT
------------------------------ ----------
PASSWORD_REUSE_TIME 2
PASSWORD_REUSE_MAX 3
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME DEFAULT
PASSWORD_GRACE_TIME 5
16 rows selected.
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
RESOURCE_NAME LIMIT
------------------------------ ----------
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='SHIRISHA';
PROFILE
--------------------------------------------------------------------------------
DEFAULT
SQL> alter user shirisha profile trainee;
User altered.
SQL> select profile from dba_users where username='SHIRISHA';
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.
[oravis@test ~]$ cd $ORACLE_HOME/rdbms/admin
[oravis@test admin]$ pwd
/u03/oravis/VIS/12.1.0/rdbms/admin
[oravis@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
RESOURCE_NAME LIMIT
-------------------------------- ----------
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
16 rows selected.
[oravis@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
RESOURCE_NAME LIMIT
------------------------------ ----------
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 shirisha password expire;
User altered.
user
[oravis@test ~]$ sqlplus shirisha/shirisha123;
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 shirisha
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: shirisha/shirisha123
ERROR:
ORA-28001: the password has expired
Changing password for shirisha
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
RESOURCE_NAME LIMIT
------------------------------ ----------
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
[oravis@test ~]$ sqlplus shirisha/shirisha123
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 shirisha
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 "SHIRISHA"
SQL> password
Changing password for SHIRISHA
Old password:
New password:
Retype new password:
Password changed
Click Here Click Here for Apps DBA Blog
No comments:
Post a Comment