We often hear that in Oracle, when a role is granted user permissions, the user must log on again to obtain the permissions granted. How can this problem be solved? Let's take the following steps to understand the meaning of this sentence.
1. DBA performs the following operations:
Create user u1 identified by u1
Create role r1;
Grant create session to r1;
Grant r1 to u1;
Query select * from dba_sys_privs where grantee = 'r1 'and find that the permission has been paid to the role.
By querying select * from dba_role_privs where grantee = 'u1', it is found that role r1 has been paid to user u1.
2. Log On Through u1 and log on to www.bkjia.com. Check that the corresponding permissions and roles are available.
SQL> show user;
USER is "U1"
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
SQL> select * from session_roles;
ROLE
------------------------------
R1
3. u1 tries to create a table: You are not authorized to create a table.
SQL> create table t (I int );
Create table t (I int)
*
ERROR at line 1:
ORA-01031: insufficient privileges
4. DBA creates another role R2. grant R2. create R2. create R2. grant u1.
Create role r2;
Grant create table to r2;
Grant r2 to u1;
By querying select * from dba_role_privs where grantee = 'u1', it is found that role r2 has been paid to user u1.