type of object in dba_tab_privs

dba_tab_privs , table_name can be a object_type of view or a table

in Order to create a dynmic query I need to tdentify <table_name> is a view or a table

Can anyone give me underlying x$table for dba_tab_privs where I can identify the type of objects

Thanks
Greens8301Asked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
Are you trying to generate the statements?  For what purpose, to copy them or to generate the privs originally.

If to generate the original privs, you don't need DBA_TAB_PRIVS.

select 'grant select on ' || owner || '.' || object_name ||
' to role_vu_1;' from dba_objects where object_type = 'VIEW' and ...
union all
select 'grant select on ' || owner || '.' || object_name ||
' to role_tab_2;' from dba_objects where object_type = 'TABLE' and ...;

Open in new window


If you are trying to copy them from an existing user, then use something like:

select distinct 'grant select on ' || owner || '.' || table_name ||
' to role_vu_1;' from dba_tab_privs a where exists (select 1 from dba_objects b
where b.owner = a.owner and b.object_name = a.table_name and b.object_type=
'VIEW') and grantee = '<user_to_copy_from>'
union all
select distinct 'grant select on ' || owner || '.' || table_name ||
' to role_tab_2;' from dba_tab_privs a where exists (select 1 from dba_objects b
where b.owner = a.owner and b.object_name = a.table_name and b.object_type=
'TABLE') and grantee = '<user_to_copy_from>';

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>in Order to create a dynmic query I need to tdentify <table_name> is a view or a table

Why?  Oracle really doens't care about this when querying.
0
 
johnsoneSenior Oracle DBACommented:
I would join to DBA_OBJECTS to determine what type of object it is.  I don't think you need to go into the X$ tables to figure that out.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
BajwaCommented:
like rest have said!! Just join the views.  Why do you need X$ tables?  What exactly is the information you looking for?

If you have DBA role then you can query dba_tab_privs and dba_tables and dba_objects or dba_segments (and join them all on table_name) to get the relevant information.
0
 
Greens8301Author Commented:
I want to construct like
grant select on viename dba_tab_privs. table_name to role_vu_1
grant select on table_name dba_tab_privs. table_name  to role_tab_2

etc
0
 
slightwv (䄆 Netminder) Commented:
Not sure why you want two different roles but OK, it's your system.

Join to dba_objects mentioned above.

Not even sure why you need to join to dba_tab_privs but I'm sure you have a reason...
0
 
Greens8301Author Commented:
Thanks for all your comments and a useful script. I am going to apply your comments and scripts

I was actually reverse engineering  the set of privs from source system and apply to target system when creating accounts and roles

BTW

select ue.name, u.name, o.name, ur.name, tpm.name,o.type#,
       decode(mod(oa.option$,2), 1, 'YES', 'NO'),
       decode(bitand(oa.option$,2), 2, 'YES', 'NO')
from sys.objauth$ oa, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue,
     table_privilege_map tpm
where oa.obj# = o.obj#
  and oa.grantor# = ur.user#
  and oa.grantee# = ue.user#
  and oa.col# is null
  and oa.privilege# = tpm.privilege
  and u.user# = o.owner#

in thie query type# is 2 for table and 4 for views
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>I was actually reverse engineering  

You can probably get all this with DBMS_METADATA.

Check out:
http://psoug.org/reference/dbms_metadata.html

GET_DEPENDENT_DDL

I might also look into a full export followed by an import to capture the scripts.  For the datapump import it is SQLFILE:
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#autoId51
0
 
johnsoneSenior Oracle DBACommented:
You are definitely reinventing the wheel.

You can go into $ORACLE_HOME/rdbms/admin and look at the scripts that create the catalog views and get the underlying tables to do a query like this, but it is a bad idea. Oracle can change those at any time without warning.

You are better off going with the DBA views or one of the suggestions that slightwv gave.  They all should work just fine and would be reusable in case of dictionary changes.
0
 
Greens8301Author Commented:
I did the text from dba_views where view_name = 'DBA_TAB_PRIVS' same thing
0
 
Greens8301Author Commented:
Thanks Very much for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.