OraFAQ Forum: SQL & PL/SQL » create view based on dba_role

Home » SQL & PL/SQL » SQL & PL/SQL » create view based on dba_role_privs

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

create view based on dba_role_privs [message #148509]

Thu, 24 November 2005 14:58

m_libranda
Messages: 3
Registered: November 2005

Junior Member

Hi,
I am trying to create a view based on dba_role_privs table. I am using this code below:

create or replace view VIEW_USER_PRIVS as
select grantee,
granted_role
from dba_role_privs
WHERE upper(granted_role) like 'OMS%';

I am logged in as the schema owner not an adminstrator. When I run this code, I'm getting this error:
ORA-00942: table or view does not exist

If I just run the select statement, it is giving me some results. Does anybody know why is this happening?

Thanks.

Report message to a moderator

Re: create view based on dba_role_privs [message #148512 is a reply to message #148509]

Thu, 24 November 2005 16:09

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

I've never tried this, but I'll take a guess:

DBA_ROLE_PRIVS (as you know it) is a PUBLIC SYNONYM to a view owned by SYS. You can run a SQL over a synonym, but cannot create a view over it.

You could try FROM sys.dba_role_privs. If you get a permission error, you will need to get the DBA to grant SELECT privs on the view DIRECTLY to you user id.

_____________
Ross Leishman

Report message to a moderator

Re: create view based on dba_role_privs [message #148514 is a reply to message #148509]

Thu, 24 November 2005 16:20

m_libranda
Messages: 3
Registered: November 2005

Junior Member

Thanks Ross,
But still does not work even if I extracted the DDL for the dba_role_privs. I'm still getting table of view does not exist. I have this sql now:
CREATE OR REPLACE VIEW VIEW_USER_PRIVS (
grantee,
granted_role,
admin_option,
default_role )
AS
select /*+ ordered */ decode(sa.grantee#, 1, 'PUBLIC', u1.name), u2.name,
decode(min(option$), 1, 'YES', 'NO'),
decode(min(u1.defrole), 0, 'NO', 1, 'YES',
2, decode(min(ud.role#),null,'NO','YES'),
3, decode(min(ud.role#),null,'YES','NO'), 'NO')
from sys.sysauth$ sa,
sys.user$ u1,
sys.user$ u2,
sys.defrole$ ud
where sa.grantee#=ud.user#(+)
and sa.privilege#=ud.role#(+) and u1.user#=sa.grantee#
and u2.user#=sa.privilege#
group by decode(sa.grantee#,1,'PUBLIC',u1.name),u2.name;

I'm beginning to think that there is actually no way around it.
But let me know if you think of something else that I can try.

Report message to a moderator

Re: create view based on dba_role_privs [message #148521 is a reply to message #148514]

Thu, 24 November 2005 16:39

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Did you run that SQL? Do you have the privs to select from all those SYS tables? I doubt that you could even run the SQL, let alone create the view.

I ran your CREATE VIEW statement on my database and it worked. But I have SELECT ANY TABLE privilege on my database.

Did you make sure that the DBA granted SELECT on sys.dba_role_privs directly to YOU, not to a role?

_____________
Ross Leishman

Report message to a moderator

Re: create view based on dba_role_privs [message #148523 is a reply to message #148509]

Thu, 24 November 2005 16:51

m_libranda
Messages: 3
Registered: November 2005

Junior Member

Thanks Ross,
We had a look at it, but that is not the way we would want to implement it. We are now creating a table based the existing data and create triggers to update data on this table.

Thanks again.

Report message to a moderator

Previous Topic:	Round a Number
Next Topic:	single value with Group By in SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Aug 08 10:14:32 CDT 2025