Is it possible to query Oracle system tables (DBA_USERS,dba_role_privs,dba_tab_privs) Using Db_link?

Question

Is it possible to query Oracle system tables (DBA_USERS,dba_role_privs,dba_tab_privs) Using Db_link?

I am getting ORA-00942: table or view does not exist when tried to query the same.

enter image description here

Answer 1

Sure it is, but only if database link points to user who is capable of accessing those objects.

Connected as an ordinary user (Scott), I'll create database link to user with DBA privileges:

SQL> connect scott/tiger
Connected.
SQL> create database link dev_old_db1
 2 connect to mydba
 3 identified by mypwd
 4 using 'orcl';
Database link created.

Selecting rows from dba_users over database link:

SQL> select count(*) from dba_users@dev_old_db1;
 COUNT(*)
----------
 83

Is that really so? Let's connect as mydba in remote database:

SQL> connect mydba/mypwd@orcl
Connected.
SQL> select count(*) from dba_users;
 COUNT(*)
----------
 83
SQL>

Looks like that's it.

---

If, on the other hand, I point database link to a non-privileged user, it won't work (and I'll get the same error as you did):

SQL> show user
USER is "SCOTT"
SQL> drop database link dev_old_db1;
Database link dropped.
SQL> create database link dev_old_db1
 2 connect to mike
 3 identified by lion
 4 using 'orcl';
Database link created.
SQL> select count(*) from dba_users@dev_old_db1;
select count(*) from dba_users@dev_old_db1
 *
ERROR at line 1:
ORA-00942: table or view does not exist --> here it is!
ORA-02063: preceding line from DEV_OLD_DB1
SQL>

---

Therefore, make sure that user - which is behind that database link - has required privileges.

• Arun: Many thanks for the comment But I am loggin as admin user and my db is inside AWS RDS I have my db link syntax as CREATE DATABASE LINK "DEV_OLD_DB" CONNECT TO "<schema>" IDENTIFIED BY <password> USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=www.amazonaws.com)(PORT=1521))) (CONNECT_DATA=(SID=D1APPOB)))'; but still not able to query the system tables
• Littlefoot: I don't use AWS. However, unless "<schema>" has DBA privileges or is granted (at least) SELECT privileges on mentioned DBA_ tables/views, you won't be able to do what you wanted.