Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Create table, access-rights ?
grz01_at_spray.se wrote:
> Hello,
>
> Short question:
>
> In Oracle, is it possible to grant a user the right
> to create a table in another users schema,
> without granting the right to create in any schema?
>
> I e user A should be allowed to do
>
> CREATE TABLE C.TAB ...
>
> but not
>
> CREATE TABLE C.TAB ...
>
> TIA,
> --------------------------------------grz01
>
There is no "CREATE TABLE IN schema_name" privilege. It is either CREATE TABLE or CREATE ANY TABLE. However, people have worked around this issue by creating a stored procedure in the SCHEMA_A that accepts the information needed to be able to create the table. This procedure dynamically generates the CREATE TABLE DDL statement and runs this statement with EXECUTE IMMEDIATE. Next, grant EXECUTE privileges on this procedure to SCHEMA_B. The SCHEMA_B user will now be able to create the table in SCHEMA_A.
HTH,
Brian
-- =================================================================== Brian Peasland dba_at_nospam.peasland.net http://www.peasland.net Remove the "nospam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three" - Unknown -- Posted via a free Usenet account from http://www.teranews.comReceived on Wed Mar 07 2007 - 08:32:56 CST