Oracle database User Role Simple test

To test the role of database roles, use the set role command to enable roles, setting the default role for database users

Create corner R1 and R2, where R2 password authentication, I set the password is Oracle

Sql> Conn System/oracle

Connected.

sql> Create role R1;

Role created.

sql> Create role R2 identified by Oracle;

Role created.

Authorize both roles, where R2 's permissions contain R1 permissions, which means R2 have higher privileges

Sql> Grant Create session,create table,create view to R1;

Grant succeeded.

Sql> Grant Create session,create table,create view,create procedure to R2;

Grant succeeded.

Create user TJ and grant two roles to the user, by default, when the user logs on, two roles take effect.

Sql> create user TJ identified by TJ

2 quota 10m on users;

User created.

Sql> Grant R1,R2 to TJ;

Grant succeeded.

Sql> Conn TJ/TJ

Connected.

Sql> select * from Session_privs;

Privilege

------------------------------

CREATE session

CREATE TABLE

CREATE VIEW

CREATE PROCEDURE

Sql> select * from Session_roles;

Role

--------------------------------------------

R1

R2

Modifies the default role of TJ users, allowing R1 to take effect only when landing

Sql> Conn System/oracle

Connected.

sql> alter user TJ default role r1;

User altered.

Sql> Conn TJ/TJ

Connected.

Sql> select * from Session_roles;

Role

------------------------------------------------------------

R1

Sql> select * from Session_privs;

Privilege

------------------------------

CREATE session

CREATE TABLE

CREATE VIEW