Home » RDBMS Server » Server Administration » roles and permission (solaris 2.10 Oracle 10.2.4.0)
roles and permission [message #559275] Sun, 01 July 2012 11:28 Go to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
This sounds like an issue that has most likely been asked before but I can't seem to find the answer so let me apologize in advance.

I am trying to create a stored procedure under the schema "ABC" but the SP refers to tables
in "XYZ" scehema.

When I do the following this appears to work:


GRANT SELECT ON XYZ.TAB1 TO ABC;




When I put the grant into a role and assign "ABC" the role I get the followig errors.

142/37 PL/SQL: ORA-00942: table or view does not exist
217/7 PL/SQL: SQL Statement ignored
235/37 PL/SQL: ORA-00942: table or view does not exist
310/7 PL/SQL: SQL Statement ignored
328/37 PL/SQL: ORA-00942: table or view does not exist
418/10 PL/SQL: SQL Statement ignored
426/27 PL/SQL: ORA-00942: table or view does not exist
433/10 PL/SQL: SQL Statement ignored




CREATE ROLE XXX NOT IDENTIFIED;
    GRANT SELECT ON ON XYZ.TAB1 TO XXX;

grant XXX to ABC;




I would have thought the role method should have worked since it is only another step
of inderection but obviously I seem to be mistaken. Can somebody explain to me why the
role does not work or maybe point me to the correct place in the documentation so I can read
up on this.

I can supply a test case if needed as this is reproducible.

Thanks to all who answer.
Re: roles and permission [message #559276 is a reply to message #559275] Sun, 01 July 2012 11:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
privileges acquired via ROLE do NOT apply within named PL/SQL procedures.
direct GRANT is required
Re: roles and permission [message #559278 is a reply to message #559275] Sun, 01 July 2012 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do I get a "ORA-01031: insufficient privileges" or "PLS-00201: identifier 'x' must be declared" in my stored procedures?

Regards
Michel

Re: roles and permission [message #559325 is a reply to message #559278] Mon, 02 July 2012 10:50 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Thanks for the response
Re: roles and permission [message #559332 is a reply to message #559325] Mon, 02 July 2012 11:24 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
I would like a nice one sentence explanation of this. I think it is something to do with PL/SQL being compiled. At compile time, Oracle checks that the owner of the procedure has appropriate privileges. It is architecturally impossible to use privileges granted through roles, because even though a role might be enabled at time of compilation, it might be disabled at time of execution.
I think the assumption is that directly granted privileges are very rarely revoked. So if a privilage is revoked, it is reasonable to invalidate all procedures that depend upon it. But roles can be enabled and disabled frequently, making it impractical to track dependencies.
Is that right?
--
Update: no, that isn't right. It could only be right if the person executing the procedure were the procedure owner. How about this:
Roles are enabled/disabled per session. If you are executing my procedure, there is no way for your session to check what state my roles are in. Because they don't even have a state, as far as you are concerned.
Better?

[Updated on: Mon, 02 July 2012 11:28]

Report message to a moderator

Re: roles and permission [message #559341 is a reply to message #559332] Mon, 02 July 2012 12:08 Go to previous message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The best answer is that this is how it is defined in SQL standard; so
"Theirs not to make reply,
Theirs not to reason why,
Theirs but to do..."

Regards
Michel
Previous Topic: how to drop datafile
Next Topic: Statement audit option not properly working
Goto Forum:
  


Current Time: Mon Mar 18 21:09:56 CDT 2024