Oracle Database - User Profile

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.

Parameters Definition

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

How to

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;

Get

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

The profile per user

SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;

More

Modify the profile of a user

alter user bisample profile MyProfile;

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

Apply the Release 11g default password

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

Documentation / Reference


Powered by ComboStrap