Home » SQL & PL/SQL » SQL & PL/SQL » Package Security....
Package Security.... [message #265100] Wed, 05 September 2007 07:30 Go to next message
b4ukoushik
Messages: 3
Registered: September 2007
Location: a
Junior Member
pls see my qn ?
there is 2 user. user1 and user2.
User1 had created a Package pack1 and a procedure proc1. And gave execute permission to user2. The package have a proc which deletes a table tab1 owned by user1. and Tab1 have no access to user2. Package pack1 and Procedure proc1 are deleting from the table tab1.
User2 having execute permission on pack1 and proc1. If user2 executes or call the package what will happen ?

pack1 -- can user2 delete table tab1 ?
proc1 -- can user2 delete the table tab1 ?

If user2 can delete the table tab1 for package pack1. then packeges owned by SYS( DBMS packeges) and we have execute permission why we can not use those DBMS packages for all object owned by sys ?
Normally user2 can delete tab1. Mane user2 can delete from tab1 although user2 do not have ant rights on tab1. I have checked also. My confusion came when we can use DBMS packeged function and we can not delete/access those tables for which we havent access.
See, SYS is the owner of all table and DBMS packeged function. I ( user2) have execute permission on these DBMS packeged fns, but i can not delete all the tables which I do not have access. May be the DBMS packeges are written in such that it should execute with the invokers permission, not with the owners( SYS).
if yes how a DBMS package restricts access of a function to the invokers rights ?
Re: Package Security.... [message #265106 is a reply to message #265100] Wed, 05 September 2007 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
pls see my qn ?

Please read and follow OraFAQ Forum Guide

Quote:
pack1 -- can user2 delete table tab1 ?
proc1 -- can user2 delete the table tab1 ?

yes, yes.

Quote:
If user2 can delete the table tab1 for package pack1. then packeges owned by SYS( DBMS packeges) and we have execute permission why we can not use those DBMS packages for all object owned by sys ?

You can if the these objects are defined with "authid definer" which is the default. Not if it is defined with "authid current_user".

Regards
Michel


Re: Package Security.... [message #265133 is a reply to message #265106] Wed, 05 September 2007 08:51 Go to previous messageGo to next message
b4ukoushik
Messages: 3
Registered: September 2007
Location: a
Junior Member
Does that means all the DBMS packeges comes with authid CURRENT_USER option inside the package body ? So that we cant use them under SYS privileges.

Coz I want to create a package and give permission to other user. Other user should execute it under his privieleges.
How to write this package ?
is it like this ?
create or replace package pack1
authid CURRENT_USER is
...
...
Regards,
Koushik
Re: Package Security.... [message #265136 is a reply to message #265133] Wed, 05 September 2007 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Does that means all the DBMS packeges comes with authid CURRENT_USER option inside the package body ? So that we cant use them under SYS privileges

Some of them that requires you execute it with your privileges.

Quote:
is it like this ?

It seems OK, check with the doc, it is perfectly clear in it.

Regards
Michel
Re: Package Security.... [message #265137 is a reply to message #265136] Wed, 05 September 2007 09:02 Go to previous message
b4ukoushik
Messages: 3
Registered: September 2007
Location: a
Junior Member
Thanks for all your nice help..

Koushik
Previous Topic: procedure to find out how many times a perticular character is present in a ginen string
Next Topic: ORACLE PL/SQL QUERIES
Goto Forum:
  


Current Time: Thu Dec 08 12:52:47 CST 2016

Total time taken to generate the page: 0.07711 seconds