Which permission need to grant to access sys.dba_systems

Question

I am working on the application which works on Oracle. For some kind of logic I need to get the list of tables from the given db user with the specified schema. In my case, I have a user which have granted access of the given schema. So when my code creates connection using the given credential and tries to fetch the tables from the following query, its return table list.

SELECT * FROM dba_objects where owner ='schema' and object_type = 'TABLE'

The above query was working with user having grant all privileges but when I did try with limited permission, it is throwing error msg.

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"

For the secondary user, from which our code is creating connection has granted permissions by following query

create user johnsmith identified by Passw0rd;;
grant connect to johnsmith ;
grant select any table to johnsmith ;
grant UPDATE any table to johnsmith ;
grant DELETE any table to johnsmith ;
grant INSERT any table to johnsmith ;

Which permission should I grant to user to have access on the following system tables...?

• dba_objects
• user_constraints
• user_cons_columns
• USER_TABLES
• all_tab_cols and also allow to access dbms_metadata.get_dependent_ddl() method

Solution

With the O7_DICTIONARY_ACCESSIBILITY initialisation parameter set to false, which is the default, then:

System privileges that provide access to objects in other schemas do not give other users access to objects in the SYS schema. For example, the SELECT ANY TABLE privilege allows users to access views and tables in other schemas, but does not enable them to select dictionary objects (base tables of dynamic performance views, regular views, packages, and synonyms). You can, however, grant these users explicit object privileges to access objects in the SYS schema.

So you can either grant select privileges on the specific views you need:

grant select on sys.dba_objects to johnsmith;

and the same for other views; or if you need them to have wider access to the SYS schema objects you can give them that with a role:

grant select_catalog_role to johnsmith;

though the principle of least privilege should always apply, so this may be overkill and potentially expose things you don't want that user to be able to see.

You don't need to grant anything for the user to be able to query user_* views. If you meant the DBA equivalents of those - e.g. dba_tables - then grant them as for dba_objects above; or they woudl be included in select_catalog_role. But again, only grant what is actually needed.

Either way, for dbms_metadata you can just grant privileges on that package too (you can't grant privileges on individual procedures in a package):

grant execute on dbms_metadata to johnsmith;

or - again probably much more than actually needed, and potentially much more dangerous that the select role:

grant execute_catalog_role to johnsmith