GRANT SELECT ON ALL_CATALOG TO appdbuser *
ERROR at line 1: ORA-01031: insufficient privileges
I want my Oracle XE
systemuser (<-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
SQL Developer 220.127.116.11 AND also with
SQL Plus 18.104.22.168
Operating System: Windows 10 Pro Build 18363
User(s) & Privileges:
The Windows user that I am logged in with is a domain user account which is part of Windows
Administratorsgroup on my machine.
I have installed this DB on my own machine with this same user, that I am currently logged in with.
I have also verified that this user is included in
ORA_DBAWindows user group. The image below shows a list of other groups on my Windows machine that start with ORA.
I am connected as
systemuser (tried both via sqlplus and sql developer) to Oracle XE DB (pluggable DB not CDB).
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.
If it matters, I have checked that NTS is included as Authentication service in my sqlnet.ora file like this:
(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.
After 7, I tried
GRANT ALL PRIVILEGES TO system WITH ADMIN OPTION;
with success, as in Grant succeeded, but my problem is still there.
- This command is part of a
SQLscript generated by a
batchscript, 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 22.214.171.124.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.
- When I executed this script by logging in with system/password as sysdba, these
GRANTcommands 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.
- 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
systemuser 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;