Thursday, December 20, 2012

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;

image

Check user’s Profile

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

image

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';

image

Change user’s profile

alter user PO PROFILE testProfile;
select username,profile from dba_users where username ='PO';

image

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

alter profile testProfile limit 
CPU_PER_SESSION 5
CONNECT_TIME 60;

image

Resource Limit

show parameter resource_limit
alter system set resource_limit = TRUE;

image

 

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';

image

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;

image

Drop user profile

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

image

drop profile testProfile CASCADE;
select username,profile from dba_users where username ='PO';

image

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.

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates