Default role

SQL> grant connect,resource to datauser identified by 123123;

Grant succeeded.

SQL> grant connect,resource to selectuser identified by 123123;

Grant succeeded.

SQL> create table datauser.table1(id number,data varchar2(10));

Table created.

SQL> create table datauser.table2(id number,data varchar2(10));

Table created.

SQL>  create table datauser.table3(id number,data varchar2(10));

Table created.

SQL> insert into datauser.table1 values (1,'table1');

1 row created.

SQL> insert into datauser.table2 values(2,'table2');

1 row created.

SQL> insert into datauser.table3 values(3,'table3');

1 row created.

SQL> commit;

Commit complete.

SQL> create role role_r_table1;

Role created.

SQL> create role role_r_table2;

Role created.

SQL> create role role_r_table3;

Role created.

SQL> grant select on datauser.table1 to role_r_table1;

Grant succeeded.

SQL> grant select on datauser.table2 to role_r_table2;

Grant succeeded.

SQL> grant select on datauser.table3 to role_r_table3;

Grant succeeded.

SQL> grant role_r_table1,role_r_table2 to selectdata;
grant role_r_table1,role_r_table2 to selectdata
                                     *
ERROR at line 1:
ORA-01917: user or role 'SELECTDATA' does not exist


SQL> grant role_r_table1,role_r_table2 to selectuser;

Grant succeeded.

SQL> alter user selectuser default role role_r_table1;

User altered.

SQL>
SQL>
SQL> conn selectuser/123123;
ERROR:
ORA-01045: user SELECTUSER lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant create session to selectuser;

Grant succeeded.

SQL> conn selectuser/123123;
Connected.
SQL> select * from datauser.table1;

        ID DATA
---------- ----------
         1 table1

SQL> select * from datauser.table2;
select * from datauser.table2
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> set role role_r_table2;

Role set.

SQL> select * from datauser.table2;

        ID DATA
---------- ----------
         2 table2

SQL> set role role_r_table3;
set role role_r_table3
*
ERROR at line 1:
ORA-01924: role 'ROLE_R_TABLE3' not granted or does not exist


SQL> conn / as sysdba
Connected.
SQL> alter user selectuser default role all;

User altered.

SQL> conn selectuser/123123;
Connected.
SQL> select * from datauser.table1;

        ID DATA
---------- ----------
         1 table1

SQL> select * from datauser.table2;

        ID DATA
---------- ----------
         2 table2

SQL> select * from datauser.table3;
select * from datauser.table3
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> show user
USER is "SELECTUSER"
SQL> conn / as sysdba
Connected.
SQL> grant role_r_table3 to selectuser;

Grant succeeded.

SQL> conn selectuser/123123
Connected.
SQL> select * from datauser.table1;

        ID DATA
---------- ----------
         1 table1

SQL> select * from datauser.table2;

        ID DATA
---------- ----------
         2 table2

SQL> conn / as sysdba
Connected.
SQL> alter user selectuser default role role_r_table1;

User altered.

SQL> conn selectuser/123123
Connected.
SQL> select * from datauser.table1;

        ID DATA
---------- ----------
         1 table1

SQL> select * from datauser.table2;
select * from datauser.table2
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from datauser.table3;
select * from datauser.table3
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> set role role_r_table3;

Role set.

SQL> select * from datauser.table1;
select * from datauser.table1
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from datauser.table2;
select * from datauser.table2
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from datauser.table3;

        ID DATA
---------- ----------
         3 table3

SQL> set role all
  2  ;

Role set.

SQL> select * from datauser.table1;

        ID DATA
---------- ----------
         1 table1

SQL> select * from datauser.table2;

        ID DATA
---------- ----------
         2 table2

SQL> select * from datauser.table3;

        ID DATA
---------- ----------
         3 table3