Let us we can discuss about roles in user management first we need know
what is roles ? how it ll be different from profiles?
Roles:
- 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
1) USER_ROLE_PRIVS
2)ALL_ROLE_PRIVS
3)ROLE_ROLE_PRIVS
4)DBA_ROLE_PRIVS
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;
ROLE
--------
CONNECT
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);
created
check our roles
SQL>select * from Session_roles;
ROLE
--------
CONNECT
MANAGER
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;
ROLE
--------
CONNECT
MANAGER
ACCOUNTS
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;
ROLE
--------
CONNECT
ACCOUNTS
Manager role has been gone which means once the role is dropped its impact simultaneously to assigned users
No comments:
Post a Comment