Oracle DBA, How To, Error, Cause and Action

Manage User Profile

In the context of system resources, a profile is a named set of specified resource limits that can be assigned to a valid user name in Oracle Database. Profiles provide for easy management of resource limits. Profiles are also the way in which you administer password policy.
Different profiles can be created and assigned individually to each user of the database. A default profile is present for all users not explicitly assigned a profile. The resource limit feature prevents excessive consumption of global database system resources.

When you create a user without specifying the Profile, Oracle will automatically assign default profile, the name of the profile is "DEFAULT".

To view the setting of the default profile is as follow

SELECT * FROM dba_profiles WHERE profile = 'DEFAULT' ORDER BY resource_type, resource_name;



There are 2 types of Resource Type, Kernel and Password.

The explanation of the setting

Resource Parameters (Kernel)

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 hundredths of seconds.
CONNECT_TIME Specify the total elapsed time limit for a session, expressed in minutes.
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.
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). Refer to size_clause for information on that clause.

Note:
This limit applies only if you are using shared server architecture. The private space for a session in the SGA includes private SQL and PL/SQL areas, but not shared SQL and PL/SQL areas.
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.

Password Parameters (password)

Use the following clauses to set password parameters. Parameters that set lengths of time are interpreted in number of days. For testing purposes you can specify minutes (n/1440) or even seconds (n/86400).

FAILED_LOGIN_ATTEMPTS Specify the number of failed attempts to log in to the user account before the account is locked. If you omit this clause, then the default is 10 days.
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, then the password expires if it is not changed within the grace period, and further connections are rejected. If you omit this clause, then the default is 180 days.
PASSWORD_REUSE_TIME and 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. This is the default if you omit both parameters.
PASSWORD_LOCK_TIME Specify the number of days an account will be locked after the specified number of consecutive failed login attempts. If you omit this clause, then the default is 1 day.
PASSWORD_GRACE_TIME Specify the number of days after the grace period begins during which a warning is issued and login is allowed. If you omit this clause, then the default is 7 days.
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.

If you specify expr for any of the password parameters, then the expression can be of any form except scalar subquery expression.


CREATE PROFILE

Following example setting is is to create e_dba_user profile

CREATE PROFILE e_dba_user
LIMIT
-- Kernel Setting
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
-- Password Setting
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 5
-- PASSWORD_VERIFY_FUNCTION e_dba_user_password_verification
PASSWORD_LOCK_TIME 1/24
PASSWORD_GRACE_TIME 10
;


To view the result you can use following SQL command

SELECT * FROM dba_profiles WHERE profile = 'E_DBA_USER' ORDER BY resource_type, resource_name;

If you not set the parameter it will show "DEFAULT" as it will follow the DEFAULT profile setting. To see real limit value of those settings you can query with profile = 'DEFAULT'


ALTER PROFILE

To change the setting of the Profile you can use ALTER PROFILE command.

example I am changing FAILED_LOGIN_ATTEMPTS to 3 and PASSWORD_GRACE_TIME to 5

ALTER PROFILE e_dba_user
LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_GRACE_TIME 5
;


And now I check the changes

SELECT * FROM dba_profiles WHERE profile = 'E_DBA_USER' ORDER BY resource_type, resource_name;


DROP PROFILE


To Delete the profile you can use DROP PROFILE command, however you will not able to drop a profile that currently assigned to users without CASCADE clause.

In my example EDBA_ADMIN1 is assigned E_DBA_USER profile. Use following SQL command to check user account with particular profile

SELECT username FROM dba_users WHERE profile = 'E_DBA_USER';

I try to DROP the profile.

DROP PROFILE e_dba_user;



Notice that I have error:

ORA-02382: profile E_DBA_USER has users assigned, cannot drop without CASCADE

Now I am using CASCADE clause.

DROP PROFILE e_dba_user CASCADE;
Notice that the if you drop a profile that currently being assigned to users with cascade clause then the affected user profile will be automatically set to DEFAULT.

I am discussing Password Verification Function in greater detail at another post.

No comments:

Post a Comment

Thanks for your comment.