dcsimg
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Creating table in another schema.

  1. #1
    Join Date
    Feb 2004
    Posts
    77

    Creating table in another schema.

    I am trying to create a table in another schema while logged in as a different user. when I run it from sql prompt I lets me do it. But when I try doing it thru pl/sql routine...it is giving me insufficient privilege.

    The user I am logging in has dba privileges.

    Here is what I am tring to do.

    SQL> show user
    USER1

    -- USER1 has dba privs.

    SQL> create table user2.test as select * from user1.test;

    Table Created


    ------
    create procedure proc1 as
    l_stmt varchar2(100);
    begin
    l_stmt := 'create table user2.test as select * from user1';
    execute_immeditate l_stmt;
    end proc1;
    /

    Procedure created

    SQL> @proc1

    I get insufficient privileges.

    What do I need to get this working?

    Thanks in advance.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You need the system privilege "create any table" granted directly to the user, not indirectly through a role. It's a PL/SQL thing.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2004
    Posts
    77
    Yes. I granted USER1 "create any table" and "drop any table" privilege. And it works.

    Interestingly USER1 could grant itself all these privs since it has been assigned DBA role. Guess Oracle needs certain data in dictionary to validate pl/sql privs.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width