CODEWITHSUNDEEP
oracle-databaseodbcdatabase-schemaenterprise-architect
Margulan Zharkenov
Margulan Zharkenov

Reputation: 458

Retrieving Oracle db schema without admin roles to Sparx

I'm trying to import db schema to sparx via odbc and I have a user without admin roles. It has access only on certain tables for monitoring database performance.

What minimal privileges my user should have to do this task? I know that I can retrieve user's schema, but I want to retrieve all schemas.

I hope there is no need for dba role or access to select all tables and dictionaries.

Upvotes: 3

Views: 336

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21085

Acquire SELECT_CATALOG_ROLE to ger read access to data dictionary views. See details in dokumentation

Than use the query to get all schemas (i.e. all db users having some objects)

 select distinct owner from dba_objects;

To list all objects in a schema

 select * from dba_objects where owner = 'XXXX';

To get a column list of a table 

 select * from dba_tab_columns where owner = 'XXX' and table_name = 'TTTTT' order by column_id;

Check here the dictionary view.

Note that with the privilege SELECT_CATALOG_ROLE you see only the metadata, you have no access to data (tables or views).

Upvotes: 4

sandman
sandman

Reputation: 2118

Edited from discussion below (thanks for response):

CREATE SESSION

DATAPUMP_EXP_FULL_DATABASE

SELECT_CATALOG_ROLE

RESOURCE

And selected privileges from DATAPUMP_IMP_FULL_DATABASE since you don't want permission to select any table.

You can get this list from

select privilege 
from dba_sys_privs 
where grantee = 'DATAPUMP_IMP_FULL_DATABASE';

Upvotes: 0

Related Questions