Managing Users Priviliges and Roles

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