ORACLE DBA - ROLES MANAGEMENT

ROLES
A set of privileges is called role. When we create a user. The default roles are.
 Connect: connect session, alter session, and drop session.
 Resource: create table, synonym, sequence, procedures, sessions,
packages.
 Imp_full_database: privilege to import the database.
 Exp_full_database: privilege to export the database.
 DBA role: all the privileges of database default role have a DBA.
 Select_catalog_role: select privilege on data dictionary table
 Delect_catalog_role: delete privilege on data dictionary table.
Syntax: Create role role identified by password /externally/globally/using packages;
To create user name called chandra
SQL> create user chandra identified by chandra;
To create role
SQL> create role role_tab;
To assign some of roles to role_tab
SQL> grant create table, create view, create session to role_tab;
To assign role to user
SQL> grant role_tab to chandra;
SQL> grant role_tab to chandra with admin option;
To establishing default roles to user
SQL> alter user chandra default role role_tab;
SQL> alter user chandra default role all;
SQL> alter user chandra default role all except role_tab;
To removing roles from the users
SQL> revoke role_tab from chandra;
To remove roles from database
SQL> drop role role_tab;
Q: Can I create role with admin option?
A: Yes , I can create.
Q: When user locked can give privileges or not?
Q: DBA given create session to chandra and again role with create session given to
chandra. If DBA revoke the create session then chandra having create session option or
not?
Q: Can I assign same privilege to multiple roles?
Q: Can I assign role to role?
Q: What are the default roles that will come with installation of oracle/database
creation?
Q: Sysdba and sysoper treated as super privileges?
Sysoper has all the features has sysdba except
 He can startup or shutdown the database
 He can’t create a database.
 He can’t drop a database.
For all practical purpose generally we connect as sysdba.
Privileges/roles are database restrictions.
Privileges are helping in restriction.
We want see the session privileges
SQL> select privilege from session_privs;
DATA DICTIONARY VIEWS OF ROLES
DBA_ROLES
SESSION_ROLES
ROLE_TAB_PRIVS
USER_TAB_PRIVS
USER_ROLE_PRIVS