Home » RDBMS Server » Server Administration » ROLE not working as expected (Oracle 12c, Windows 7 64 bit)
ROLE not working as expected [message #660104] Thu, 09 February 2017 01:56 Go to next message
icm63
Messages: 22
Registered: December 2007
Junior Member

Oracle12c

Database non plugable, contains all the example schemas.

USERS ARE (the Sample Schemas for Oracle12)
HR
OE
SCOTT
SH
SAMPLE (ADMIN user)

Testing the creating of a CUSTOM ROLE called My_READONLY role

Added to this role 2x System privileges: SELECT ANY DICTIONARY, SELECT ANY TABLE

Using user SYS (SYSDBA) I GRANTED this My_READONLY role to the SAMPLE user successfully.

I know that when I GRANT these system privileges 'SELECT ANY DICTIONARY, SELECT ANY TABLE'
individually (not part of a role) I can see all the example tables for users HR, OE, SCOTT and SH while I logon in
as SAMPLE user

BUT..

when I user the ROLE 'My_READONLY' that contain these 'SELECT ANY DICTIONARY, SELECT ANY TABLE',
I dont see any tables for the users HR, OE or SCOTT while connected as SAMPLE.

I have run the SELECT * FORM DBA_TAB_PRIV code and using the My_READONLY approach I dont see any privileges there at all.

I use SQL Developer to excute the changes rather than run the PL SQL scripts all the time.

WHY is the 'My_READONLY' role not working.??

Any ideas






Re: ROLE not working as expected [message #660106 is a reply to message #660104] Thu, 09 February 2017 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
WHY is the 'My_READONLY' role not working.??
Surely because you did something wrong but as you didn't SHOW us what you did we can't tell you.

Quote:
I have run the SELECT * FORM DBA_TAB_PRIV code and using the My_READONLY approach I dont see any privileges there at all.
This is expected, you give no object privileges to your role, only system privileges you'll see in DBA_SYS_PRIVS.

See:
SQL> create role My_READONLY;

Role created.

SQL> grant SELECT ANY DICTIONARY, SELECT ANY TABLE to My_READONLY;

Grant succeeded.

SQL> create user test identified by test;

User created.

SQL> grant create session, My_READONLY to test;

Grant succeeded.

SQL> conn test/test
Connected.
TEST> select count(*) from hr.employees;
  COUNT(*)
----------
       107

1 row selected.

TEST> select * from dba_tab_privs where grantee='MY_READONLY';

no rows selected

TEST> select * from dba_sys_privs where grantee='MY_READONLY';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
MY_READONLY                    SELECT ANY DICTIONARY                    NO
MY_READONLY                    SELECT ANY TABLE                         NO

2 rows selected.

[Edit: typo]

[Updated on: Thu, 09 February 2017 02:39]

Report message to a moderator

Re: ROLE not working as expected [message #660107 is a reply to message #660104] Thu, 09 February 2017 02:15 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You need to show what you have done. For example:
orclz>
orclz> conn / as sysdba
Connected.
orclz> drop user jw cascade;

User dropped.

orclz> drop role r1;
drop role r1
          *
ERROR at line 1:
ORA-01919: role 'R1' does not exist


orclz>
orclz> create role r1;

Role created.

orclz> grant select any table to r1;

Grant succeeded.

orclz> grant create session,r1 to jw identified by jw;

Grant succeeded.

orclz> conn jw/jw
Connected.
orclz> select count(*) from scott.dept;

  COUNT(*)
----------
         4

orclz>

--update: Ah! You have MC's example already.

[Updated on: Thu, 09 February 2017 02:16]

Report message to a moderator

Previous Topic: How to check what Procedure is calling in database jobs?
Next Topic: FRA Space Claim for Flashback Logs
Goto Forum:
  


Current Time: Thu Mar 28 07:04:58 CDT 2024