| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Stored procedures and privileges
A copy of this was sent to "Peter Laursen" < ptl_at_edbgruppen.dk>
(if that email address didn't require changing)
On 15 Apr 1999 10:26:10 GMT, you wrote:
>Is there a way to have a stored procedure run with other(more powerfull)
>privileges, than the user that executes the procedure?
>
In Oracle8.0 and below procedures ALWAYS run with the base privileges of the CREATOR of the procedure -- never, never as the OWNER of the procedure.
In Oracle8.1 a procedure may optionally run with the base privileges of the INVOKER of the procedure but by default execute with the privs of the CREATOR.
>What i want is a procedure that creates and drop users and grants and
>revoke access to tables. This procedure should be executed by a user that
>does not own nor have been granted accces with grant option, also the user
>executing the procedure has not been granted create user.
>
Fine, create a user and then:
grant create user to <that user>;
grant drop user to <that user>;
remember that roles are never enabled during the execution of a procedure.
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.
thats why we have to "grant <priv> to <that user>;"
now, create your procedure that adds/drops users in that schema, grant execute on it to whomever and you have the procedure to create/drop users.
For the grant on tables -- if the grants are not contained in a role (eg: if you have not setup roles with the table grants but rather want to have the grants issued directly against the tables), then you must create the 'grant' procedure in each schema that owns tables you want to grant on OR you must :
grant select on <the table> to <that user> with ADMIN OPTION;
then, you can create the procedure to do the grants in <that user>s schema and grant execute on that to others.
If the grants are in a role, then you need to
grant <that role> to <that user> with admin option;
so <that user> can grant that role to others.....
>Thanks
>Peter
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |