OraFAQ Forum: SQL & PL/SQL » Oracle Roles -- Users -- Grant Privileges

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Roles -- Users -- Grant Privileges

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Roles -- Users -- Grant Privileges [message #613156]

Wed, 30 April 2014 09:32

praveenramaswamy
Messages: 34
Registered: December 2013

Member

Hi

I am running into a issue and need your guidance on how the approach should and i have explained a scenario below.

I have a Schema called "CCH" under which all the objects are created.

I have created a new user "USER1" and created a new role "OMSROLE".

Assigned OMSROLE to USER1

Granted SELECT ONLY privilege on all tables under "CCH" schema to the role "OMSROLE"

So if i login to the Database as "USER1", i am able to query the "CCH" schema tables.

So far no problem. However if i create a new table under "CCH" schema, then if i login as "USER1" then obviously i cant query the CCH Schema table(newly created table).

Can you please suggest how to overcome the above issue and guide what approach should i take

Report message to a moderator

Re: Oracle Roles -- Users -- Grant Privileges [message #613157 is a reply to message #613156]

Wed, 30 April 2014 09:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

after CREATE TABLE then issue GRANT

Remember privileges acquired via ROLE do NOT apply within named PL/SQL procedures.

Report message to a moderator

Re: Oracle Roles -- Users -- Grant Privileges [message #613160 is a reply to message #613157]

Wed, 30 April 2014 09:51

praveenramaswamy
Messages: 34
Registered: December 2013

Member

ThanKs Blackswan...

I agree your suggestion would work, but is there a way to do it without granting privileges after creating the table

Report message to a moderator

Re: Oracle Roles -- Users -- Grant Privileges [message #613162 is a reply to message #613160]

Wed, 30 April 2014 09:54

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

No. Oracle, unlike other databases (e.g. SQL Server) doesn't support schema level privileges (e.g. SELECT on any table in a schema - current or future).

SY.

Report message to a moderator

Re: Oracle Roles -- Users -- Grant Privileges [message #613163 is a reply to message #613160]

Wed, 30 April 2014 09:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I agree your suggestion would work, but is there a way to do it without granting privileges after creating the table

with Oracle everything is forbidden; except that which is explicitly GRANTED!

How is Oracle to know or decide that USER1 should have access any newly created table in a different schema?
Is it OK for USER1 to have access to (new?) table in SYS or SYSTEM schemas?

Report message to a moderator

Re: Oracle Roles -- Users -- Grant Privileges [message #613164 is a reply to message #613156]

Wed, 30 April 2014 09:55

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Object level privileges (like SELECT on a specific table) have to be granted on the specific object. There is no intermediate level between "GRANT SELECT ON demo.emp TO ..." and "GRANT SELECT ANY TABLE TO ..."

People often expect there to be something like SELECT ON <schema>, but think about the implications. Suppose oracle allowed you to GRANT SELECT ON HR (implying SELECT on all tables, now or future, in the HR schema). Possibly fine for today, but next week you create a new table, HR.VERY_PRIVATE_SENSITIVE_DATA. Do you really want everyone who has select on HR to just blindly inherit access to VERY_PRIVATE_SENSITIVE_DATA?

Report message to a moderator

Re: Oracle Roles -- Users -- Grant Privileges [message #613178 is a reply to message #613164]

Wed, 30 April 2014 10:49

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

EdStevens wrote on Wed, 30 April 2014 10:55

Possibly fine for today, but next week you create a new table, HR.VERY_PRIVATE_SENSITIVE_DATA. Do you really want everyone who has select on HR to just blindly inherit access to VERY_PRIVATE_SENSITIVE_DATA?

That's why database engines that allow schema/database ... level grants have both GRANT and DENY. This allows granting select on HR schema along with "granting" DENY select on VERY_PRIVATE_SENSITIVE_DATA.

SY.

Report message to a moderator

Re: Oracle Roles -- Users -- Grant Privileges [message #613187 is a reply to message #613178]

Wed, 30 April 2014 11:49

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

regarding GRANT DENY

Call me a curmudgeon (and proud of it!) but that seems like it would still be a security hole. Given oracle's approach, if I forget to grant SELECT on a new table, the worst that happens is someone screams that they can't do their job. I make the grant and they go on. Just a speed bump. But given the other approach, if I forget to grant DENY ... seems like I just left it open ...

Report message to a moderator