In Oracle the newly created role is not working
I have created a role and grante select access on a table to the role
then granted the role to user
User not able to select on the table whose access is granted through the role.
this is explained and resolved in the example below:
SQL> create user a identified by a default tablespace users temporary tablespace temp quota unlimited on users;
User created.
SQL> grant connect, create table to a;
Grant succeeded.
SQL> conn a/a
Connected.
SQL> create table t (name varchar(15));
Table created.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> grant insert on a.t to a;
Grant succeeded.
SQL> conn a/a
Connected.
SQL> insert into t (name) values('harvarinder');
1 row created.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> create user b identified by b default tablespace users temporary tablespace temp quota unlimited on users;
User created.
SQL> grant connect to b;
Grant succeeded.
SQL> create role r;
Role created.
SQL> grant select on a.t to r;
Grant succeeded.
SQL> grant r to b;
Grant succeeded.
SQL> conn b/b
Connected.
SQL> select * from a.t;
select * from a.t
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select * from dba_role_privs where grantee = 'B';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
B R NO NO
B CONNECT NO YES
SQL> conn b/b;
Connected.
SQL> select * from a.t;
select * from a.t
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user b default role all;
User altered.
SQL> select * from dba_role_privs where grantee = 'B';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
B R NO YES
B CONNECT NO YES
SQL> conn b/b
Connected.
SQL> select * from a.t;
NAME
---------------
harvarinder
SQL>
The reason for this is that, only default roles get enabled implicitly. Non default roles are not enabled on log on they should be enabled explicitly by
SQL> set role role_name
The above command should enable the role (role_name) for that session and once the session is lost the role is disabled for that use. It must be enabled for next session if we want its benifits.
However if we want it to be enable by default then make that role as default.
