| 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 tkyte_at_us.oracle.com (Thomas Kyte)
(if that email address didn't require changing)
On Thu, 15 Apr 1999 10:45:23 GMT, you wrote:
>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.
>
sorry, the above paragraph should read:
In Oracle8.0 and below procedures ALWAYS run with the base privileges of the
CREATOR of the procedure -- never, never as the INVOKER 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
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |