Greens8301
asked on
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
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
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.
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.
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.
ASKER
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
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
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...
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
I did the text from dba_views where view_name = 'DBA_TAB_PRIVS' same thing
ASKER
Thanks Very much for the help
Why? Oracle really doens't care about this when querying.