Database Profiles

Profile – is a database object – a named set of resource limits to:

Restrict database usage by a system user – profiles restrict users from performing operations that exceed reasonable resource utilization. Examples of resources that need to be managed:

• Disk storage space.
• I/O bandwidth to run queries.
• CPU power.
• Connect time.

Enforce password practices – how user passwords are created, reused, and validated
Profiles are assigned to users as part of the CREATE USER or ALTER USER commands

• User accounts can have only a single profile
• A default profile can be created – a default already exists within Oracle named
• DEFAULT – it is applied to any user not assigned another profile.
• Assigning a new profile to a user account supersedes any earlier profile.
• Profiles cannot be assigned to roles or other profiles.

Profiles only take effect when resource limits are "turned on" for the database as a whole. Specify the RESOURCE_LIMIT initialization parameter.

RESOURCE_LIMIT = TRUE

Use the ALTER SYSTEM statement to turn on resource limits.

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;


Profile Specifications

Profile specifications include:

• Password aging and expiration
• Password history
• Password complexity verification
• Account locking
• CPU time
• Input/output (I/O) operations
• Idle time
• Connect time
• Memory space (private SQL area for Shared Server only)
• Concurrent sessions

If users not assigned a specific profile are automatically assigned the DEFAULT profile. The DEFAULT profile has only one significant restriction it doesn't specify a password verification function.

This query lists the resource limits for the DEFAULT profile


SQL> COLUMN profile FORMAT A10;
COLUMN resource_name FORMAT a30;
COLUMN resource FORMAT a8;
COLUMN limit FORMAT a15;
SELECT * FROM DBA_PROFILES
WHERE PROFILE = 'DEFAULT';

PROFILE    RESOURCE_NAME                  RESOURCE LIMIT
---------- ------------------------------ -------- ---------------
DEFAULT    COMPOSITE_LIMIT                KERNEL   UNLIMITED
DEFAULT    SESSIONS_PER_USER              KERNEL   UNLIMITED
DEFAULT    CPU_PER_SESSION                KERNEL   UNLIMITED
DEFAULT    CPU_PER_CALL                   KERNEL   UNLIMITED
DEFAULT    LOGICAL_READS_PER_SESSION      KERNEL   UNLIMITED
DEFAULT    LOGICAL_READS_PER_CALL         KERNEL   UNLIMITED
DEFAULT    IDLE_TIME                      KERNEL   UNLIMITED
DEFAULT    CONNECT_TIME                   KERNEL   UNLIMITED
DEFAULT    PRIVATE_SGA                    KERNEL   UNLIMITED
DEFAULT    FAILED_LOGIN_ATTEMPTS          PASSWORD 10
DEFAULT    PASSWORD_LIFE_TIME             PASSWORD 180
DEFAULT    PASSWORD_REUSE_TIME            PASSWORD UNLIMITED
DEFAULT    PASSWORD_REUSE_MAX             PASSWORD UNLIMITED
DEFAULT    PASSWORD_VERIFY_FUNCTION       PASSWORD NULL
DEFAULT    PASSWORD_LOCK_TIME             PASSWORD 1
DEFAULT    PASSWORD_GRACE_TIME            PASSWORD 7



Creating a Profile
===================

DBA creates a profile with the CREATE PROFILE command.
This command has clauses that explicitly set resource limits. A DBA must have the CREATE PROFILE system privilege in order to use this command.
Example:

CREATE PROFILE developer LIMIT
SESSIONS_PER_USER 4
CPU_PER_SESSION unlimited
CPU_PER_CALL 6000
LOGICAL_READS_PER_SESSION unlimited
LOGICAL_READS_PER_CALL 100
IDLE_TIME 1
CONNECT_TIME 480
PASSWORD_REUSE_TIME 1
PASSWORD_LOCK_TIME 2
PASSWORD_REUSE_MAX 3
FAILED_LOGIN_ATTEMPTS 2;

Resource limits that are not specified for a new profile inherit the limit set in the DEFAULT profile.


Assigning Profiles
===========================

Profiles can only be assigned to system users if the profile has first been created. Each system user is assigned only one profile at a time. When a profile is assigned to a system user who already has a profile, the new profile replaces the old one – the current session,if one is taking place, is not affected, but
subsequent sessions are affected. Also, you cannot assign a profile to a role or another profile

profiles are assigned with the CREATE USER and ALTER USER
command. An example CREATE USER command

CREATE USER userp IDENTIFIED BY userp
PROFILE developer;

SELECT username, profile FROM dba_users
WHERE username = 'USERP';


Altering Profiles
====================
Profiles can be altered with the ALTER PROFILE command.
A DBA must have the ALTER PROFILE system privilege to use this command.
When a profile limit is adjusted, the new setting overrides the previous setting for
the limit, but these changes do not affect current sessions in process.
Example:

ALTER PROFILE developer LIMIT
CPU_PER_CALL default
LOGICAL_READS_PER_SESSION 20000
SESSIONS_PER_USER 1;




Dropping a Profile
=========================

Profiles no longer required can be dropped with the DROP PROFILE command.
The DEFAULT profile cannot be dropped.
The CASCADE clause revokes the profile from any user account to which it was
assigned – the CASCADE clause MUST BE USED if the profile has been assigned to any user account.
When a profile is dropped, any user account with that profile is reassigned the DEFAULT profile.
Examples



SQL> drop profile developer;
drop profile developer
*
ERROR at line 1:
ORA-02382: profile DEVELOPER has users assigned, cannot drop without CASCADE

SQL> DROP PROFILE DEVELOPER CASCADE;

Profile dropped.

SQL> SELECT username, profile FROM dba_users
WHERE username = 'USERP';  

USERNAME                       PROFILE
------------------------------ ------------------------------
USERP                          DEFAULT

SQL>

Changes that result from dropping a profile only apply to sessions that are created after the change – current sessions are not modified.