The customer wants to query the dba/v$views to list all the schema/object details from each pluggable database to migrate their application data.
As part of this requirement, we are trying to create a non-sys user to provide the read-only(SELECT) access to all dba/v$views instead of providing 'SYSDBA' role/privilege(which is vulnerable to due to wide access)
We create a non-sys user with 'CONNECT,RESOURCE' role and tried the following privileges to query the dba/v$views, however, we continue to get ORA-00942: table or view does not exist
Privileges i tried so far:
1. SELECT ANY TABLE
2. select_catalog_role
3. Read any table
Is there anything I am missing. Please help us to provide right privilege to get read access on all dba/v$views.
I tried the 'READ ANY TABLE', but no luck
SQL> create user c##_readonly identified by OraTest656_ container=all;
SQL> grant connect,resource to c##_readonly
Grant succeeded.
SQL> grant read any table to c##_readonly;
Grant succeeded.
sqlplus c##_readonly/OraTest656_@pdb1s001
SQL> select LISTAGG(username, ',') WITHIN GROUP (ORDER BY username) from dba_users where username LIKE 'T\_%APP\_ADM' ESCAPE '\' order by username;
select LISTAGG(username, ',') WITHIN GROUP (ORDER BY username) from dba_users where username LIKE 'T\_%APP\_ADM' ESCAPE '\' order by username
*
ERROR at line 1:
ORA-00942: table or view does not exist