2 Replies Latest reply: Aug 22, 2013 2:54 AM by user5754561 RSS

    Dropping domain index of another user - missing permissions




      My system user is able to drop an normal index of another user after disabling DBA role. It is unable, though, to drop a domain index created by the same user.




      USERNAME                 PRIVILEGE                          ADMIN_OPTION

      ------------------------------ ---------------------------------------- ------------

      SYSTEM                   SELECT ANY DICTIONARY              NO   
      SYSTEM                   EXECUTE ANY PROCEDURE              NO   
      SYSTEM                   DROP TABLESPACE                    NO   
      SYSTEM                   GLOBAL QUERY REWRITE               NO   
      SYSTEM                   CREATE MATERIALIZED VIEW           NO   
      SYSTEM                   ALTER ANY TABLE                    NO   
      SYSTEM                   CREATE TABLE                       NO   
      SYSTEM                   ALTER TABLESPACE                   NO   
      SYSTEM                   CREATE TABLESPACE                  NO   
      SYSTEM                   UNLIMITED TABLESPACE               YES  
      SYSTEM                   DROP ANY MATERIALIZED VIEW         NO   
      SYSTEM                   SELECT ANY TABLE                   NO   
      SYSTEM                     DROP ANY INDEX                       YES  


      DDL used for index creation (run under user star1)







      CREATE INDEX "FTXTINDEX101" ON "FACT_CHARGE" ( "SAVEC_RECONCILING_USERS" ) indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 50M SYNC (ON COMMIT)');     

      create table test (x varchar(20))

      create index test_index on test ("X" )


      Now, when I execute the following under system, I'm getting the error mentioned at the bottom:


      set roles all except DBA;

      drop index star1.test_index;

      index STAR1T.TEST_INDEX dropped.


      drop index star1.FTXTINDEX101

      Error report:

      SQL Error: ORA-01031: insufficient privileges

      01031. 00000 -  "insufficient privileges"

      *Cause:    An attempt was made to change the current username or password

                 without the appropriate privilege. This error also occurs if

                 attempting to install a database without the necessary operating

                 system privileges.

                 When Trusted Oracle is configure in DBMS MAC, this error may occur

                 if the user was granted the necessary privilege at a higher label

                 than the current login.

      *Action:   Ask the database administrator to perform the operation or grant

                 the required privileges.

                 For Trusted Oracle users getting this error although granted the

                 the appropriate privilege at a higher label, ask the database

                 administrator to regrant the privilege at the appropriate label.



      If I let system use its DBA roles the index gets dropped. I find it hard to pinpoint the permission which is induced by the DBA role and which allows system to drop other's domain indices.


      Thanks for any hints,



      PS. Oracle version info


      Oracle Database 11g Enterprise Edition Release - 64bit Production
      PL/SQL Release - Production                                     
      CORE    Production                                                   
      TNS for 64-bit Windows: Version - Production                    

      NLSRTL Version - Production