Solved

type of object in dba_tab_privs

Posted on 2012-03-19
11
1,087 Views
Last Modified: 2012-03-21
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
0
Comment
Question by:Greens8301
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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
 
LVL 5

Expert Comment

by:Bajwa
Comment Utility
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
 

Author Comment

by:Greens8301
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
Comment Utility
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
 

Author Comment

by:Greens8301
Comment Utility
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
>>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
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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
 

Author Comment

by:Greens8301
Comment Utility
I did the text from dba_views where view_name = 'DBA_TAB_PRIVS' same thing
0
 

Author Closing Comment

by:Greens8301
Comment Utility
Thanks Very much for the help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle 12c 10 99
Cross Outer Join 4 50
querying by the sum of a column in decimal 7 43
query in Oracle forms Builder 2 25
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now