SYS/SYSTEM Users and ORA-01031 (Prior to Oracle 9.2)

SYS and SYSTEM users have DBA roles so they manipulate any object on database, but if you are using an Oracle DB prior to 9.2, you’ll see that DBA role is not enough to manipulate the privileges of other users’ objects.

Because before Oracle 9.2, there was not a system privilege called “GRANT ANY OBJECT PRIVILEGE”. For example, you can not run the following query on Oracle 8i (even with SYS):

Here’s a trick to overcome this problem: We create a temporary procedure under the target schema:

After we grant the required privileges, we can drop the procedure:

Please share
  •  
  •  
  •  
  •  
  •  
  •  

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

4 Comments

  1. Sergio Perez

    Thanks!!! very good this procedure.

    A little think… the <b> and </b> are not necessary and after the “end;” line, I must put a slash “/”.

    The rest… all OK.!!!

    • Gokhan Atil

      Hi Sergio, those html tags are mistakenly remained from my previous syntax-highlighter. Thanks for heads up, I’ll fix it immediately.

  2. Shraddha Dasari

    This method of granting privileges has really helped me alot … Thank you for sharing.

Leave Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.