1 Reply Latest reply: Jul 7, 2014 1:36 PM by JustinCave RSS

    Grant permission to a role.

    Matthew Rhea

      Oracle 11g R2 Windows.


      I have an oracle user, named user_1.  User_1 needs to be able to grant select on a table owned by another user, User_2, to a role. I keep getting the error ORA-01031: insufficient privileges.

      User_1 has the following system privileges:

      ALTER ANY INDEX

      ALTER ANY PROCEDURE

      ALTER ANY ROLE

      ALTER ANY SEQUENCE

      ALTER ANY TABLE

      ALTER ANY TRIGGER

      ALTER USER

      CREATE ANY INDEX

      CREATE ANY PROCEDURE

      CREATE ANY SEQUENCE

      CREATE ANY TABLE

      CREATE ANY TRIGGER

      CREATE ANY TYPE

      CREATE ANY VIEW

      CREATE DATABASE LINK

      CREATE SEQUENCE

      CREATE SYNONYM

      CREATE TABLE

      CREATE USER

      CREATE VIEW

      DELETE ANY TABLE

      DROP ANY INDEX

      DROP ANY PROCEDURE

      DROP ANY SEQUENCE

      DROP ANY TABLE

      DROP ANY TRIGGER

      DROP ANY VIEW

      EXECUTE ANY PROCEDURE

      EXECUTE ANY TYPE

      GRANT ANY PRIVILEGE

      GRANT ANY ROLE

      INSERT ANY TABLE

      SELECT ANY DICTIONARY

      SELECT ANY SEQUENCE

      SELECT ANY TABLE

      UNLIMITED TABLESPACE

      UPDATE ANY TABLE

       

      It has the following system roles:

      CONNECT

      DBA

      RESOURCE

      SELECT_CATALOG_ROLE

       

      What permissions must User_1 have to allow it to grant select on a table in another schema to a role? Both SYS and SYSDBA can do it, but I don’t want to give such elevated permissions to User_1.

      I have even granted SYSDBA to User_1 and I still get the ORA-01031 error!

       

      Thanks,

      Matthew

        • 1. Re: Grant permission to a role.
          JustinCave

          I believe you're looking for the GRANT ANY OBJECT PRIVILEGE privilege.

           

          The ANY privileges are all very powerful privileges and they often create opportunities for crafty humans to do things that you might not have anticipated.  They are generally frowned upon heavily in any sort of audit situation.  I'd be very, very cautious about giving non-DBAs these privileges.

           

          It would make much more sense to me, particularly when you appear to want to restrict things to a single schema, to have a DBA user create a stored procedure that does the actual grant (implementing whatever validations, logging, etc. are appropriate) and grant the user the privilege to execute this stored procedure.

           

          Justin