0

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;
2
  • Huh? Why (how) does a user not have access to the ALL_xxxx dictionary views? Nov 27, 2020 at 16:03
  • I don't know. I just installed any Oracle product first time in 15 years. and I am not even a DBA. Dec 1, 2020 at 14:37

2 Answers 2

1

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

GRANT ALL PRIVILEGES TO system WITH GRANT OPTION
3
  • I am connected to localhost:1521/xepdb1 with sys as sysdba, and executing above command gives me the 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. Nov 27, 2020 at 14:55
  • So, did you "Specify the admin option"?
    – mustaccio
    Nov 27, 2020 at 14:57
  • I tried that too. It succeeds. However, I still get insufficient privileges error when i GRANT SELECT ON ALL_XXXXX to my user! Nov 27, 2020 at 15:02
0

The problem was solved by someone else in the team, the differences that I see in the provided setup document are:

  1. Login as sys@xepdb1

  2. Instead of creating new table spaces, we now rename the table spaces in Oracle XE, with the following two commands:

    ALTER TABLESPACE temp RENAME TO temporary_data;

    ALTER TABLESPACE users RENAME to user_data;

  3. Secondly this is how the instructions are to GRANT SELECT ON rights to the system user individually.

    GRANT SELECT ON ALL_CATALOG TO system WITH GRANT OPTION;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.