0 User Profile & Resource Limit

• Each Oracle user must have user profile. If no profile is specify or the profile has been delete. The default profile will be assign to the user.
• Default profile can not be drop.
• Profile can be used to restrict the password complexly or the resource limitation.
• Profile change would only apply to the new session. It does not affect the existing sessions.
• If the profile has been assign to the user. We have to assign the user to other profile first or “DROP PROFILE CASCADE”.

 

List all the user profiles

col PROFILE format a10

col resource_name format a25

col limit format a10 

select PROFILE,resource_name, limit from dba_profiles;

Check user’s Profile

select username,profile from dba_users where username ='PO2';

Create a  user profile

create profile testProfile LIMIT

password_reuse_max 5;

col resource_name format a25

col limit format a10 

select resource_name, limit from dba_profiles where profile='TESTPROFILE';

Change user’s profile

alter user PO PROFILE testProfile;

select username,profile from dba_users where username ='PO';

Change profile’s Setting - Add resource limit to the profile

alter profile testProfile limit 

CPU_PER_SESSION 5

CONNECT_TIME 60;

Resource Limit

show parameter resource_limit

alter system set resource_limit = TRUE;

 

Resource Limit demo

Check the resource limit setting

show parameter resource_limit

col resource_name format a25

col limit format a10 

select resource_name, limit from dba_profiles 

where profile='TESTPROFILE' and resource_type='KERNEL';

select username,profile from dba_users where username ='PO';

Connect as PO and run the below query. We would get the ORA-02392 error. It is because the SQL has exceed the profile setting “CPU_PER_SESSION” 5.

show user

create table t2 as select * from dba_objects;

Drop user profile

drop profile testProfile;

select username,profile from dba_users where username ='PO';

drop profile testProfile CASCADE;

select username,profile from dba_users where username ='PO';

In the previous demo, User PO has the resource limit, therefore create table would fail. After re login and we also drop the user profile with cascade then the User PO fail back to the default user. User still fail to create table. This is because the new profile only be effective at the new session. After re login, the default profile will be effective then user can create the table.