This discussion is archived
14 Replies Latest reply: Jun 5, 2013 3:25 PM by sb92075 RSS

create tables on another schema

538299 Newbie
Currently Being Moderated
hi,

what should I do so that user A can create tables on user B' schema?

thank you in advance.
  • 1. Re: create tables on another schema
    Paul M. Oracle ACE
    Currently Being Moderated
    Grant "create any table" privilege to user A.
  • 2. Re: create tables on another schema
    Aman.... Oracle ACE
    Currently Being Moderated
    user535296 wrote:
    hi,

    what should I do so that user A can create tables on user B' schema?
    What's the oracle version? Are both the users in the same database or in different ones?
    If they are in the same database than all you have to do is to ask user B to grant privs over his tables to user A.That's all!
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
    With the Partitioning, Oracle Label Security, OLAP, Data Mining,
    Oracle Database Vault and Real Application Testing options
    
    SQL> create user usera identified by a;
    
    User created.
    
    SQL> grant create session,create table to usera;
    
    Grant succeeded.
    
    SQL> create user userb identified by b'
      2  create user userb identified by b'
      3
    SQL> create user userb identified by b;
    
    User created.
    
    SQL> grant create session ,create table to userb;
    
    Grant succeeded.
    SQL> alter user usera quota unlimited on users;
    
    User altered.
    
    SQL> alter user userb quota unlimited on users;
    
    User altered.
    
    SQL> conn userb/user
    ERROR:
    ORA-01017: invalid username/password; logon denied
    
    
    Warning: You are no longer connected to ORACLE.
    SQL> conn userb/b
    Connected.
    SQL> create table userb_table(a char);
    
    Table created.
    
    SQL> grant all on userb_table to usera;
    
    Grant succeeded.
    
    SQL> conn usera/a
    Connected.
    SQL> sho user
    USER is "USERA"
    SQL> create table usera_userb as select * from userb.userb_table;
    
    Table created.
    
    SQL> desc usera_userb
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     A                                                  CHAR(1)
    
    SQL>
    If they are in different databases than in addition to the above mentioned step,you need to create a DB link between two databases as well.
    HTH
    Aman....
  • 3. Re: create tables on another schema
    Surachart Opun Oracle ACE
    Currently Being Moderated
    this case you have to grant "create any table" to userA.

    Example:

    SQL> show user;
    hr

    SQL> create table scott.test(id number);
    create table scott.test(id number)
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges


    SQL> conn / as sysdba

    SQL> grant create any table to hr;

    Grant succeeded.


    then try again:



    1* create table scott.test(id number)
    SQL> /

    Table created.

    SQL> show user
    hr
    ................
  • 4. Re: create tables on another schema
    538299 Newbie
    Currently Being Moderated
    hi,

    this will allow userA to create tables in all schemas even SYS. Correct?

    thank you,
  • 5. Re: create tables on another schema
    Aman.... Oracle ACE
    Currently Being Moderated
    user535296 wrote:
    hi,

    this will allow userA to create tables in all schemas even SYS. Correct?
    Nope,
    SQL> conn / as sysdba
    Connected.
    SQL> grant create any table to usera;
    
    Grant succeeded.
    
    SQL> conn usera/a
    Connected.
    SQL> create table sys.a(a char);
    create table sys.a(a char)
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    
    
    SQL>
    IMHO,I wouldn't grant a user ,create any table priv.
    HTH
    Aman....
  • 6. Re: create tables on another schema
    538299 Newbie
    Currently Being Moderated
    hi

    After granting the priviliges to users, I want to backup the database using rman. When I restore it, these privileges are perserved?

    thank you
  • 7. Re: create tables on another schema
    Aman.... Oracle ACE
    Currently Being Moderated
    Yes
    Aman....
  • 8. Re: create tables on another schema
    420083 Newbie
    Currently Being Moderated
    is there other way to allow one schema create a table in another schema without granting create any table? we are not allowed to grant create any table due to security violations.
  • 9. Re: create tables on another schema
    SomeoneElse Guru
    Currently Being Moderated
    we are not allowed to grant create any table due to security violations.
    So that restriction has been put into place but you want a way to work around it?

    (by the way, this thread is years old. You should start a new one.)
  • 10. Re: create tables on another schema
    user296828 Expert
    Currently Being Moderated
    is there other way to allow one schema create a table in another schema without granting create any table? we are not allowed to grant create any table due to security violations.
    Not possible without granting a create table privileges. Bear in mind it will always be a breach.
  • 11. Re: create tables on another schema
    850983 Newbie
    Currently Being Moderated
    hi,

    yes it is possible....

    create user A
    grant create session, create procedure to A
    grant execute procedure to A with ADMIN OPTION
    login as A
    create procedure DDL_in_A_proc(table_def varchar2(2000))
    BEGIN

    execute_imediate(table_def );
    END;

    Create user B
    grant create session to B


    grant execute on A.DDL_in_A_proc to B

    ...

    now by passing the table ddl u can do it...
  • 12. Re: create tables on another schema
    589325 Newbie
    Currently Being Moderated
    I like the procedure based solution. I have the very same issue with my security department and the use of ANY. I think they will like this method.

    Thanks
  • 13. Re: create tables on another schema
    990932 Newbie
    Currently Being Moderated
    dont you think there is a risk having other DDL changes like alter existing table , truncate or dropping the table in the definition. if we can restrict them do so, can you please help me.

    Appreciate your help.
  • 14. Re: create tables on another schema
    sb92075 Guru
    Currently Being Moderated
    987929 wrote:
    dont you think there is a risk having other DDL changes like alter existing table , truncate or dropping the table in the definition. if we can restrict them do so, can you please help me.

    Appreciate your help.
    do NOT hijack somebody elses thread.

    start your own thread

    How do I ask a question on the forums?
    SQL and PL/SQL FAQ

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points