Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> PL/SQL Package can't see tables granted via role
smento
post Mar 12 2010, 11:01 AM
Post #1


Newbie
*

Group: Members
Posts: 7
Joined: 20-February 09
Member No.: 39,825



All,

A Package cannot query a table that the package owner has permission to query via a role
specifically:

There are schema_1 and schema_2

schema_1 owns package_1

schema_1 has select, update, insert on schema_2 via role_1

schema_1 CAN query schema_2

package_1 cannot access schema_2

(288/22 PL/SQL: ORA-00942: table or view does not exist)


How can this be resolved?

Thanks in advance!!!
Go to the top of the page
 
+Quote Post
Steve
post Mar 12 2010, 01:19 PM
Post #2


Advanced Member
***

Group: Members
Posts: 618
Joined: 22-January 06
From: Virginia Beach, VA
Member No.: 3,560



You must grant select on the table to the package owner explicitly.

http://www.dba-oracle.com/concepts/roles_security.htm


--------------------
Steve Karam
Sr. Consultant - Burleson Consulting
Oracle 10g Certified Master / Oracle ACE
http://www.OracleAlchemist.com
steve@orcldba.com
Go to the top of the page
 
+Quote Post
burleson
post Mar 12 2010, 05:56 PM
Post #3


Advanced Member
***

Group: Members
Posts: 11,338
Joined: 26-January 04
Member No.: 13



Hi,

There is a good reason why privileges cannot be granted via a role:

http://www.dba-oracle.com/security/roles_granted.htm

Like Steve says, grant them directly!

Alsom be aware that Oracle has many ither security options like "grant execute" and VPD security:"

http://www.dba-oracle.com/bp/bp_book11_audit.htm



--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
smento
post Mar 15 2010, 12:29 PM
Post #4


Newbie
*

Group: Members
Posts: 7
Joined: 20-February 09
Member No.: 39,825



I found the answer.

If you create a package with "AUTHID CURRENT_USER" specification, the permissions (even those granted via a role) of the user calling the package are used.

As long as the user has permission to execute the package, the specific user's permissions are used by the package (not the permissions of the schema that owns the package.)

thanks for the help!

-S
Go to the top of the page
 
+Quote Post
dave
post Mar 15 2010, 12:51 PM
Post #5


Advanced Member
***

Group: Members
Posts: 5,206
Joined: 8-October 04
Member No.: 785



QUOTE (smento @ Mar 15 2010, 05:30 PM) *
I found the answer.

If you create a package with "AUTHID CURRENT_USER" specification, the permissions (even those granted via a role) of the user calling the package are used.

As long as the user has permission to execute the package, the specific user's permissions are used by the package (not the permissions of the schema that owns the package.)

thanks for the help!

-S


that is not an answer, that is dangerous
Go to the top of the page
 
+Quote Post
pcoates
post Apr 13 2012, 02:52 PM
Post #6


Newbie
*

Group: Members
Posts: 1
Joined: 13-April 12
Member No.: 47,079



QUOTE (dave @ Mar 15 2010, 10:51 AM) *
that is not an answer, that is dangerous


I do not see why this is dangerous. If the permissions are correctly set on the underlying tables surely this simply means that SQL in the procedures in the packages are executed as if they were being executed by the current user. Admittedly this is a different paradym from how views work, but it might be exactly what is wanted. What am I missing that makes this "dangerous", and "not an answer"?
Go to the top of the page
 
+Quote Post
HAL9000
post Apr 13 2012, 04:14 PM
Post #7


Advanced Member
***

Group: Members
Posts: 879
Joined: 25-September 07
Member No.: 12,336



"What am I missing that makes this "dangerous", and "not an answer"?"

I did not write that comment, but perhaps it is because of indirect vs. direct granting of privileges?

See here, I agree with you that invoker rights with authid current_user makes sense:

http://www.dba-oracle.com/t_authid_current_user.htm
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 2nd August 2014 - 01:30 AM