7 Replies Latest reply: Aug 5, 2014 3:51 AM by MariaKarpa(MK) RSS

    Create table for users

      Hi all,

       

      11.2.0.3.10

      aix6

       

       

      I have created a user SCOTT in our database.

      I grant connect,resource to scott; because It easier to do this way.

       

      As dba, I want that I am the one creating all tables for scott, and he can not create for himself.

       

      What is the effective way to do this? Is this command  enough? > REVOKE CREATE TABLE FROM SCOTT;

       

      Thanks,

      mk

        • 1. Re: Create table for users
          rp0428

          You can NOT prevent  SCOTT from creating tables if he has resource privileges and can connect to the DB.

           

          Just don't allow SCOTT to connect as SCOTT at all. Don't give anyone the password to the SCOTT schema.

          • 2. Re: Create table for users
            sol.beach

            with Oracle EVERYTHING is forbidden; except that which is explicitly GRANTED.

             

            >> REVOKE CREATE TABLE FROM SCOTT;

             

            above will not do as you desire since no GRANT CREATE TABLE was issued to SCOTT.

            • 3. Re: Create table for users

              Thank rp, sol

               

              This is a development database and SCOTT is a developer. But I just want to control the table creation so he will not mess up the database.

              Can I create a trigger to fire error when creating a table? or

              I will alter user scott default tablespace dummy; then quota 0 on dummy?

              • 4. Re: Create table for users
                Girish Sharma

                Rp has given you the correct answer that if you wish to restrict the users to create table, don't give them resource privilege.  See this demo:

                 

                Connected to:

                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

                With the Partitioning, OLAP, Data Mining and Real Application Testing options

                 

                SQL> create user test identified by test;

                 

                User created.

                 

                SQL> grant connect to test;

                 

                Grant succeeded.

                 

                SQL> connect test/test;

                Connected.

                SQL> create table tab1(a number);

                create table tab1(a number)

                *

                ERROR at line 1:

                ORA-01031: insufficient privileges

                 

                 

                SQL> connect sys/pw as sysdba

                Connected.

                SQL> grant create table to test;

                 

                Grant succeeded.

                 

                SQL> connect test/test;

                Connected.

                SQL> create table tab1(a number);

                 

                Table created.

                 

                SQL> connect sys/pw as sysdba

                Connected.

                SQL> revoke create table from test;

                 

                Revoke succeeded.

                 

                SQL> connect test/test;

                Connected.

                SQL> create table tab2(a number);

                create table tab2(a number)

                *

                ERROR at line 1:

                ORA-01031: insufficient privileges

                 

                SQL>

                 

                Regards

                Girish Sharma

                • 5. Re: Create table for users

                  Thanks all, I did not think is it just as simple as it is

                  My mind really is used to complex&challenging ideas and procedures

                  • 6. Re: Create table for users
                    Harmandeep Singh

                    A small correction, I feel CONNECT is role and not "resource privilege".

                     

                    Run the below sql, it will clear idea, why CONNECT role is making tables

                     

                    select * from dba_sys_privs where grantee='RESOURCE'

                     

                    RESOURCECREATE TRIGGERNO
                    RESOURCECREATE SEQUENCENO
                    RESOURCECREATE TYPENO
                    RESOURCECREATE PROCEDURENO
                    RESOURCECREATE CLUSTERNO
                    RESOURCECREATE OPERATORNO
                    RESOURCECREATE INDEXTYPENO
                    RESOURCECREATE TABLENO

                     

                     

                    Regards,

                    Harman

                    • 7. Re: Create table for users

                      Thanks harman sir,

                       

                      Very attentive observation. I will validate that.