Let us we can discuss about roles in user management first we need know

what is  roles ? how it ll be different from profiles?


- Role is a set of privileges  that can be granted to users or to other roles

- We can add privileges to roles and then  grant the role to a user.

Difference between roles and privileges

- Profiles are set of authorization objects , but roles are set of profiles

-Without giving profile we can't create a role,but profile can exist/function in user master record without being role.  which means that, when you assign a Role, without a profile generated for that Role, to a user, the user will not be able to perform any activity. but if however you assign a Profile to a user, he will be able to perform the activities/functions, attributed to that Profile.

-role can't be effectively impact on session (connected) user views of roles





Creating role

S-1: logged  on  sysdba user  and created a new  user called 'Mike'

SQL> create user Mike identified by mike default tablespace users quota  5m on users;

 S-2: Now i 'm open the new session try to log on Mike users

oracle@oracle11g~]$export ORACLE_SID=orcl
oracle@oracle11g~]$sqlplus Mike/mike

ORA-01045  user mike lacks CREATE SESSION privilege logon denied 

here we  have to grant the user creation session system privilege or in order to grant  role

S-3 let us  go to  sysdba login and grant  connect role to  mike

SQL>grant connect to mike;

S-4; logon mike user

SQL>sqlplus Mike/mike

connected instance

SQL>select * from Session_roles;


let us mike user try to create table

SQL> create table  r (sal number);

sql error insufficient privileges

which means mike user doesn't have create table system privilege s-5 so let us creating role query without  password

SQL>create role manager;

grant system privileges to manager

SQL>grant create table, create view to  manager;

assign the role manager to mike

SQL>grant manager to Mike;

we can create role with password also

SQL> create role accounts identified by acco123;

grant object privilge

SQL> grant select on scott.emp to accounts;

S-6 after  assign Manager  role let us try create table  from mike side

SQL> create table  r (sal number);

sql error ORA-01031 insufficient privileges

the same error has throughout because role will get impact the user  not immediately  while user session is active so disconnect  the session again we can logged in as Mike user

SQL> create table  r (sal number);

check our roles

SQL>select * from Session_roles;


Assign one role into  another

S-1 logon sys user grant the role accounts into  manager

SQL>grant  accounts to manager;

in above we assigned role accounts into manager  

S-2 let us check mike user disconnect mike user  session and again logon  

SQL>select * from scott.emp;

it's working fine so check roles which has been assigned to mike

SQL>select * from Session_roles;


 Drop roles

S-1; logon on sys user  and drop the roles

SQL> drop role manager;

S-2 check the mike session after  drop manager  role

SQL>select * from Session_roles;


Manager  role has been gone which means  once the role is dropped its impact simultaneously to assigned users