Managing Users Privileges and Roles :
Two types of privileges :
1.System privileges
2.Object privileges
1.System privileges :
sys>>grant create session to user1,finance;
sys>>grand create user,alter table to user1,finance;
sys>>revoke create table from user1;
2.Object privileges :
alter -- tables,sequences
delete -- tables,views
execute -- procedures
index -- tables
insert -- tables,views
reference -- tables
select -- tables,sequences,views
update -- tables,views
Example:
sys>>grant insert,update on emp to u1;
Managing Users Roles :
system defined roles:
connect ---- create session
resource ---- create cluster,create procedures,create sequence,create table, create triggen, create dba ---- export tables ,users schema etc
exp_full_database ---- export full database
imp_full_database ---- import full database
delete_catalog_role ----delete privileges on all dictionary packages for this role
execute_catalog_role --- execute privileges on all catalog tables and viwes for this role
select_catalog_role ---select privilege on all catalog tables and viwes for this role
Creating,Altering,Dropping,granting and revoking a Role:
create:
sys>>create role clerk identified by demo;
altering :
sys>>alter role clerk identified by <password>;
Dropping :
sys>>drop role clerk;
granting :
sys>>grant manager to user_01 with admin options;
revoking :
sys>>revoke clerk from user1;
Creating the profiles:
session_per_user --limits the number of concurrent session for the user
cpu_per_session --limits the CPU time for session. This is expressed in hundredths of seconds
cpu_per_call --Limits CPU time for a call.This is expressed in hundredths of seconds
connect_time --limits the total elapsed connect time a session
failed_login_attempts --no of failed attempts after which accounts is going to locked
password_life_time --no of days password is valid
password_reuse_max --no of time password can be changed
paswword_verify_function--Function with which it is going to verify the password
password_lock_time -- no of days password going to be locked
password_grace_time --no of days it s going to prompt for password expiry
idle_time -- defines the maximum amount of continuos inactive time span.
logical_reads_per_session--limits the number of data blocks read ina session
logical_reads_per_call -- limits the number of data blocks read for a call to process a SQL statement
private_sga
composite_limit
Creating the Profile :
sys>>create PROFILE ramu limit
SESSIONS_PER_USER 1
CPU_PER_CALL 6000
CONNECT_TIME 560
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
IDLE_TIME 15
FAILED_LOGIN_ATTEMPTS 3;
Changing the values for profile :
sys>>alter profile ramu limit
LOGICAL_READS_PER_SESSION 20000
CPU_PER_CALL default
LOGICAL_READS_PER_CALL 100;
To drop the profile
sys>>drop profile ramu cascade;
To lock the Users account:
sys>>alter user ramu account lock;
To check the status of the users :
sys>>select username,user_id,account_status,lock_date from dba_users where username='ramu';
To expire the users password :
sys>>alter user ramu password expire;
To unlock the user account :
sys>>alter user ramu account unlock;
To list all system privilege grants :
sys>>select * from dba_sys_privs;
To list all role grants :
sys>>select * from dba_role_privs;
To list object privileges granted to user :
sys>>select table_name,privilege,grantable from dba_tab_privs;
To list all the column specific privileges that have been granted :
sys>>select grantee,table_name,column_name,privilege from dba_col_privs;
sys>>select * from session_privs;
To list roles of the database :
sys>>select * from dba_roles;
sys>>select granted_role,admin_option from role_role_privs where role='system_admin';
To check the granted roles and their privileges to ramu user
sys>>select a.grantee,a.granted_role,b.privilege from dba_role_privs a,dba_sys_privs b
where a.granted_role=b.grantee and a.grantee=user1;
Listing privilege and role information
ALL_COL_PRIVS
USER_COL_PRIVS
ALL_TAB_PRIVS
USER_TAB_PRIVS
ALL_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD
DBA_ROLES
DBA_COL_PRIVS
DBA_SYS_PRIVS
DBA_ROLE_PRIVS
DBA_TAB_PRIVS
ROLE_ROLE_PRIVS
SESSION_PRIVS
SESSION_ROLES
