Re: How to create a table in another user

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 18 Jun 1999 16:48:32 GMT
Message-ID: <3778776d.17764433@newshost.us.oracle.com>

A copy of this was sent to "Benny Yazdanpanahi" <biyazdanpanahi_at_marathonoil.com> (if that email address didn't require changing) On Fri, 18 Jun 1999 11:39:29 -0500, you wrote:

>I have an sql which i am running from user1 ( e.g.)
>
>and in that SQL i am trying to create a temporary table in another user
> e.g. user2 )
>
>but user1 does not have "create any table priviledge"
>
>and DBA does not wants me to write any connect statement in my sql to
>connect to user2
>and do create table statement because of security issue !!
>
>Can anyone send me suggestion.
>
>
>Thanks
>
>
>

Can user2 create a procedure for user1 and grant user1 execute on it? If so, that procedure could look like:

SQL> create or replace procedure create_table( sql_stmt in varchar2 )   2 as

  3      exec_cursor     integer default dbms_sql.open_cursor;
  4      rows_processed  number  default 0;
  5  begin
  6      dbms_sql.parse(exec_cursor, 'create table ' || sql_stmt,
  7                                     dbms_sql.native );
  8      rows_processed := dbms_sql.execute(exec_cursor);
  9      dbms_sql.close_cursor( exec_cursor );

 10 end;
 11 /

Procedure created.

SQL> grant execute on create_table to scott; Grant succeeded.

SQL> grant create table to tkyte;
Grant succeeded.

SQL> desc a_temp_table
ERROR:
ORA-04043: object a_temp_table does not exist

SQL> connect scott/tiger
Connected.

SQL> exec tkyte.create_table( 'a_temp_table ( x int )' );

PL/SQL procedure successfully completed.

SQL> connect tkyte/tkyte
Connected.

SQL> desc a_temp_table

 Name                            Null?    Type
 ------------------------------- -------- ----
 X                                        NUMBER(38)

If user2 creates that and grants execute on it to user1, AND user2 has been granted the CREATE TABLE privelege DIRECTLY (not via a role), then user1 will be able to create tables in user2's schema (and since we glue create table onto the statement in the create table procedure -- thats the ONLY thing user1 can do in user2's schema)...

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 18 1999 - 11:48:32 CDT