DB version : 19c
OS : RHEL 7.9
I have a common user named C##CLONE_USER which has been granted CREATE PLUGGABLE DATABASE and CREATE SESSION system privileges on all PDBs plus the root container.
C##CLONE_USER is not granted any other role.
But, C##CLONE_USER cannot run SELECT on data dictionary views as shown below . It is the same behaviour in all PDBs in this CDB.
Strangely, in other CDBs, the same user which has been granted only CREATE PLUGGABLE DATABASE and CREATE SESSION privileges (with no other roles granted) can access dictionary views !!
Any idea why ?
I first thought of O7_DICTIONARY_ACCESSIBILITY. But, it seems to be deprecated in 19c
$ sqlplus C##CLONE_USER/Lioness45@enterp04-scan.domain.net:1535/pdb_brcs.domain.net
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 19 17:00:28 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Last Successful login time: Fri Aug 19 2022 16:47:49 +02:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> desc dba_objects
ERROR:
ORA-04043: object "SYS"."DBA_OBJECTS" does not exist
SQL> desc dba_tables
ERROR:
ORA-04043: object "SYS"."DBA_TABLES" does not exist
-- At the container
SQL> alter session set container = PDB_BRCS;
Session altered.
SQL> select privilege from dba_Sys_privs where grantee = 'C##CLONE_USER';
PRIVILEGE
----------------------------------------
CREATE PLUGGABLE DATABASE
CREATE SESSION
SQL> select granted_role from dba_role_privs where grantee = 'C##CLONE_USER';
no rows selected
SQL> show parameter O7_DICTIONARY_ACCESSIBILITY
SQL>
SQL> show parameter DICTIONARY_ACCESSIBILITY
SQL>