Solved

type of object in dba_tab_privs

Posted on 2012-03-19
11
1,111 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 77

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 35

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 77

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 35

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 77

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 35

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

749 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