ORA 01031 Insufficient privileges on GRANT SELECT on ALL_CATALOG to a user as system user

oracleoracle-xepermissionssysdba

Command:

GRANT SELECT ON ALL_CATALOG TO appdbuser
                *

Error:

ERROR at line 1:
ORA-01031: insufficient privileges

PROBLEM:

I want my Oracle XE system user (<-important) to be able to GRANT SELECT ON
ALL_CATALOG, ALL_CONSTRAINTS, ALL_INDEXES, ALL_OBJECTS, ALL_TABLES,
ALL_TAB_COLUMNS and ALL_VIEWS.

DMBS: Oracle XE 18c (Express Edition), Version 18.4

Client(s): SQL Developer 20.2.0.175 AND also with SQL Plus 12.2.0.0

Operating System: Windows 10 Pro Build 18363

User(s) & Privileges:

  1. The Windows user that I am logged in with is a domain user account which is part of Windows Administrators group on my machine.

  2. I have installed this DB on my own machine with this same user, that I am currently logged in with.

  3. I have also verified that this user is included in ORA_DBA Windows user group. The image below shows a list of other groups on my Windows machine that start with ORA.

    Windows ORA User Groups

  4. I am connected as system user (tried both via sqlplus and sql developer) to Oracle XE DB (pluggable DB not CDB).

  5. I connected to the DB with sys/password as sysdba and executed the following command with success:

    GRANT ALL PRIVILEGES TO system;

    but the GRANT SELECT ON commands still gives the Insufficient privileges error.

  6. If it matters, I have checked that NTS is included as Authentication service in my sqlnet.ora file like this:

    SQLNET.AUTHENTICATION_SERVICES= (NTS)

  7. (EDIT1) As per this answer I have now also tried

    GRANT ALL PRIVILEGES TO system WITH GRANT OPTION;

    which gives me following error report

    Error report –
    ORA-01939: only the ADMIN OPTION can be specified
    01939. 00000 – "only the ADMIN OPTION can be specified"
    *Cause: System privileges and roles can only be granted with the admin
    option. The grant option cannot be used with system privileges and
    roles. The delegate option cannot be used with system privileges.
    *Action: Specify the admin option.

  8. After 7, I tried

    GRANT ALL PRIVILEGES TO system WITH ADMIN OPTION;

with success, as in Grant succeeded, but my problem is still there.

More Context

  1. This command is part of a SQL script generated by a batch script, neither of which I want to or can change, as that is used by a larger team and based on given parameters these scripts already work for MS SQL Server and Oracle 12.2.0.1.0 on a Linux machine (however the IT has set it up), which is maintained by our IT and we have limited (2) licenses for it only. Hence, I am setting up Oracle XE on my machine and want something to be repeatable so other devs can just do it from my experience.
  2. When I executed this script by logging in with system/password as sysdba, these GRANT commands inside the SQL script worked, but even this logging in is coded in script, which I don't want and most likely shouldn't change. Its ok if I provide the devs with an additional script for them to execute before they use the standard scripts.
  3. This script in question is also creating the users, to whom the script is now GRANTing rights. There were errors in those CREATE USER commands too, because the CREATE USER command specifies a DEFAULT TABLESPACE user_data and a TEMPORARY TABLESPACE temporary_data. The DB complained about these not existing in database (xepdb1), so I created these tablespaces with system user using following commands:
   CREATE TABLESPACE user_data 
       DATAFILE '<ORA_DATA>\XE\XEPDB1\user_data.dbf' SIZE 1M 
       AUTOEXTEND ON NEXT 1M;
    
   CREATE TEMPORARY TABLESPACE temporary_data
       TEMPFILE '<ORA_DATA>\XE\XEPDB1\temporary_data.dbf' SIZE 2M 
       AUTOEXTEND ON NEXT 1M;

Best Answer

To allow the user system to grant privileges on objects it doesn't own, use

GRANT ALL PRIVILEGES TO system WITH GRANT OPTION