| roles and permission [message #559275] |
Sun, 01 July 2012 11:28  |
 |
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 #559332 is a reply to message #559325] |
Mon, 02 July 2012 11:24  |
John Watson
Messages: 8989
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 |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
