14 Replies Latest reply: Jun 5, 2013 5:25 PM by sb92075 RSS

    create tables on another schema

    538299
      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.
          Grant "create any table" privilege to user A.
          • 2. Re: create tables on another schema
            Aman....
            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
              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
                hi,

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

                thank you,
                • 5. Re: create tables on another schema
                  Aman....
                  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
                    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....
                      Yes
                      Aman....
                      • 8. Re: create tables on another schema
                        420083
                        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
                          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
                            12cdb
                            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
                              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
                                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
                                  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
                                    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