Solved

type of object in dba_tab_privs

Posted on 2012-03-19
11
1,106 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)
ID: 37739200
>>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
ID: 37739206
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
ID: 37739420
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
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.

 

Author Comment

by:Greens8301
ID: 37739536
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)
ID: 37739554
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
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
ID: 37739605
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
ID: 37747010
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
ID: 37747041
>>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
ID: 37747157
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
ID: 37747905
I did the text from dba_views where view_name = 'DBA_TAB_PRIVS' same thing
0
 

Author Closing Comment

by:Greens8301
ID: 37747924
Thanks Very much for the help
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Queries 15 46
Oracle Pivot Question 8 71
Oracle SQL - Query help 7 66
oracle RMAN - trying to duplicate a database 5 39
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

839 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