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? – Michael Kutz Nov 27 '20 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. – Ozair Kafray Dec 1 '20 at 14:37
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. – Ozair Kafray Nov 27 '20 at 14:55
  • So, did you "Specify the admin option"? – mustaccio Nov 27 '20 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! – Ozair Kafray Nov 27 '20 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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