RE: Question On authid current_user

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Mon, 21 Jan 2013 10:30:39 -0500
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F0183D18231_at_AAPQMAILBX02V.proque.st>



Hi Scott,

This is the PL/SQL direct grants problem, in a slightly different guise.

In a definers rights procedure, the object owner must be directly granted rights on all objects referenced in the procedure, at *compile* time. Then, at run time, any user who has been granted execute on the procedure, will gain those rights for the duration of the procedure execution.

In a current_user procedure, the executor of the procedure must have directly granted rights on all objects referenced in the procedure, at *run* time.

Now, if you call a current_user procedure from a definer rights procedure, what happens? If the owner of the definer rights procedure only has grants via a role on the objects referenced by the current_user procedure, it's not going to work, because the definer rights procedure executes in an environment equivalent to 'set role none'.

However, if the owner of the definer rights procedure has direct grants on the object(s) referenced by the current_user procedure, it *should* work...

So, if you grant direct grants to the owner of the definer rights procedure, on the object(s) that the current_user procedure is trying to access, I think it will work.

(Not tested.)

Hope that helps,

-Mark

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Scott Canaan Sent: Monday, January 21, 2013 9:57 AM
To: oracle-l_at_freelists.org
Subject: Question On authid current_user

All,

   I created a stored procedure that analyzes the tables in a small schema. The procedure is owned by one user and was created with authid current_user, so it can be run by another user. It runs fine as the other user, as long as it is called directly by that user. When it is put inside a package, an ORA-01031: insufficient privileges error is raised. The customer is asking me to grant "execute any" privilege to the second user so that it will run. I'm not convinced that will solve the problem and I don't want to do this grant. What other options are there for getting this procedure to run inside the package? Scott Canaan '88 (srcdco_at_rit.edu<mailto:Scott.Canaan_at_rit.edu>)
(585) 475-7886 - work (585) 339-8659 - cell
"Life is like a sewer, what you get out of it depends on what you put into it." - Tom Lehrer.

--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 21 2013 - 16:30:39 CET

Original text of this message