Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Create table, access-rights ?

Re: Create table, access-rights ?

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Wed, 07 Mar 2007 08:32:56 -0600
Message-ID: <45eec093$0$16305$88260bb3@free.teranews.com>


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.com
Received on Wed Mar 07 2007 - 08:32:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US