Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

type of object in dba_tab_privs

Posted on 2012-03-19
11
Medium Priority
?
1,173 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 1000 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 1000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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
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…

597 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