Query DBA_TABLES and USER_TABLES in PL SQL cursor

Question

I have a cursor in PLSQL procedure to select from DBA_TABLES and USER_TABLES.

CURSOR c1 IS
  SELECT DISTINCT(DB.TABLE_NAME) FROM DBA_TABLES DB, USER_TABLES UT WHERE DB.TABLE_NAME = UT.TABLE_NAME AND DB.IOT_TYPE IS NOT NULL;

BEGIN
  OPEN c1;
  FETCH c1  INTO IOT_TABLE;
  CLOSE c1;

However the procedure complain

"ORA-00942 Table or View does not exist"

during execute.

Answer 1

The user has to have SELECT privilege on that view.
To check whether the user has the SELECT permission against this view, login as this user and run the below query:

select * from user_tab_privs WHERE table_name = 'DBA_TABLES';

If this query returns none, then the user has no rights to that view and cannot see it.

---

In order to grant this privilege to that user, login as SYS or SYSTEM, and issue:

GRANT select ON DBA_TABLES TO username;

After this the user should see a result like below, and will have access to that view:

select table_name, privilege from user_tab_privs WHERE table_name = 'DBA_TABLES';

TABLE_NAME PRIVILEGE                               
---------- ---------
DBA_TABLES SELECT