7 Replies Latest reply: Jul 18, 2014 4:16 PM by rp0428 RSS

    user roles and privileges

    TuBong

      Hi everyone,

       

      Does someone know what privileges, other than DBA, can a user create tables, triggers, procedures for other schemas?

      For example:

      User A log in and create a table for user B:

       

      UserA> create table USERB.table_name (x number, y number);

       

      Thanks,

      TuBong

        • 1. Re: user roles and privileges
          sb92075

          TuBong wrote:

           

          Hi everyone,

           

          Does someone know what privileges, other than DBA, can a user create tables, triggers, procedures for other schemas?

          For example:

          User A log in and create a table for user B:

           

          UserA> create table USERB.table_name (x number, y number);

           

          Thanks,

          TuBong

           

           

          GRANT CREATE ANY TABLE TO USERA;

          • 2. Re: user roles and privileges
            SomeoneElse

            You would need a system privilege like CREATE ANY.

             

            They are usually considered far too powerful and are rarely (if ever) granted to anyone.

            • 3. Re: user roles and privileges
              rp0428
              Does someone know what privileges, other than DBA, can a user create tables, triggers, procedures for other schemas?

              For example:

              User A log in and create a table for user B:

               

              UserA> create table USERB.table_name (x number, y number);

               

              Those are system privileges. An ordinary grant of a system privilege (e.g. GRANT CREATE PROCEDURE) to a user only allows that user privileges in their own schema.

              Adding the keyword ANY (e.g. GRANT CREATE ANY PROCEDURE) to a user allows that user the privilege for all current, and future, schemas.

               

              You can't just grant a system privilege that applies to a specific list of schemas only.

               

              See the Security Doc

              http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#BABGGBBH

              About ANY Privileges and the PUBLIC Role

               

              System privileges that use the ANY keyword enable you to set privileges for an entire category of objects in the database. For example, the CREATE ANY PROCEDURE system privilege permits a user to create a procedure anywhere in the database. The behavior of an object created by users with the ANY privilege is not restricted to the schema in which it was created. For example, if user JSMITH has the CREATE ANY PROCEDURE privilege and creates a procedure in the schema JONES, then the procedure will run as JONES. However, JONES may not be aware that the procedure JSMITH created is running as him (JONES). If JONES has DBA privileges, letting JSMITH run a procedure as JONES could pose a security violation.

              • 4. Re: user roles and privileges
                user9339278

                Hi,

                 

                The way to allow a user to create a table in another schema which does not have DBA privileges so, give CREATE ANY TABLE system privilege to that user.

                 

                This privilege can only be given to UserA by a user with DBA privileges.

                Grant create any table to UserA;

                 

                Note: - For Security concern make sure when you grant above privileges to UserA for create tables in UserB. So UserB must be aware about this provided privileges.

                • 5. Re: user roles and privileges
                  RAJESH.PK



                  Pls Read more ....Roles and Privileges Administration and Restrictions (Doc ID 13615.1)

                  • 6. Re: user roles and privileges
                    TuBong

                    Thanks all.

                    • 7. Re: user roles and privileges
                      rp0428

                      If your question has been answered then please mark it as ANSWERED.