Oracle Database - User Profile

1 - About

A profile is a collection of parameters that sets limits on database resources. When you change the profile to a user, you assign a profile but you apply also a set of parameters.

If you assign the profile to a user, then that user cannot exceed these limits parameters.

You can use profiles to configure database settings such as:

  • sessions per user,
  • logging and tracing features,
  • controlling user passwords,
  • and so on.

3 - Parameters Definition

The parameters definition can be found in the SQL reference Documentation.

4 - How to

4.1 - Create a profile

CREATE profile DI_SAMPLE LIMIT 
  COMPOSITE_LIMIT	        UNLIMITED
  SESSIONS_PER_USER	        UNLIMITED
  CPU_PER_SESSION	        UNLIMITED
  CPU_PER_CALL	                3000
  LOGICAL_READS_PER_SESSION	UNLIMITED
  LOGICAL_READS_PER_CALL	1000
  IDLE_TIME	                UNLIMITED
  CONNECT_TIME	                UNLIMITED
  PRIVATE_SGA	                15k
  FAILED_LOGIN_ATTEMPTS	        UNLIMITED
  PASSWORD_LIFE_TIME            UNLIMITED
  PASSWORD_REUSE_TIME	        UNLIMITED
  PASSWORD_REUSE_MAX	        UNLIMITED
  PASSWORD_VERIFY_FUNCTION	NULL;

4.2 - Get

4.2.1 - The definition of profile

To find information about profile, you can query the DBA_PROFILES data dictionary view.

You have also a DBA_SQL_PROFILES

4.2.2 - The profile per user

SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;

4.2.3 - More

4.3 - Modify the profile of a user

ALTER USER bisample profile MyProfile;

4.4 - Disable password complexity

Disable any existing password complexity rules for the default profile.

SQL> @?/rdbms/admin/undopwd.sql
 
Profile altered.

where the script undopwd.sql undo the default password security settings from Oracle Database 11g

4.5 - Apply the Release 11g default password

Run the secconf.sql script to apply the Release 11g default password settings

5 - Documentation / Reference

db/oracle/profile.txt ยท Last modified: 2017/09/06 19:29 by gerardnico