- 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.
0 comments:
Post a Comment