Does DBA_ROLES and ALL_USERS have public synonyms?
DBA_ROLES is owned by sys so maybe that is the case.
Just a guess ... Guy (Senior DBA)
Dmitry Melekhov <dm_at_belkam.com> wrote in message
news:48f1f058.0201100500.6a777142_at_posting.google.com...
> I want to create view oracle 8.1.7.0 from user system
>
> Error:
>
> SQL> CREATE VIEW SEL_USERS_ROLES
> (USER_NAME,NAME_ROLE,GRANTED_ROLE,ADM_OPT,DEF_ROLE) AS
> 2 SELECT aaa.*,
> 3 decode(NVL(grt.GRANTED_ROLE,'*'),'*',0,1) GRANTED_ROLE,
> 4 decode(NVL(grt.ADMIN_OPTION,'*'),'*',0,'YES',1,'NO',0,1) ADM_OPT,
> 5 decode(NVL(grt.DEFAULT_ROLE,'*'),'*',0,'YES',1,'NO',0,1) DEF_ROLE
> 6 FROM
> 7 (
> 8 SELECT usr.USERNAME USER_NAME, rol.ROLE NAME_ROLE
> 9 FROM DBA_ROLES rol, ALL_USERS usr
> 10 WHERE rol.ROLE LIKE 'TEL_%'
> 11 ) aaa,
> 12 DBA_ROLE_PRIVS grt
> 13 WHERE
> 14 grt.GRANTEE(+) = aaa.user_name
> 15 AND grt.GRANTED_ROLE(+) = aaa.name_role
> 16 ;
> FROM DBA_ROLES rol, ALL_USERS usr
> *
> ERROR at line 9:
> ORA-00942: table or view does not exist
>
> But:
>
> SQL> SELECT aaa.*,
> 2 decode(NVL(grt.GRANTED_ROLE,'*'),'*',0,1) GRANTED_ROLE,
> 3 decode(NVL(grt.ADMIN_OPTION,'*'),'*',0,'YES',1,'NO',0,1) ADM_OPT,
> 4 decode(NVL(grt.DEFAULT_ROLE,'*'),'*',0,'YES',1,'NO',0,1) DEF_ROLE
> 5 FROM
> 6 (
> 7 SELECT usr.USERNAME USER_NAME, rol.ROLE NAME_ROLE
> 8 FROM DBA_ROLES rol, ALL_USERS usr
> 9 WHERE rol.ROLE LIKE 'TEL_%'
> 10 ) aaa,
> 11 DBA_ROLE_PRIVS grt
> 12 WHERE
> 13 grt.GRANTEE(+) = aaa.user_name
> 14 AND grt.GRANTED_ROLE(+) = aaa.name_role
> 15 ;
>
> no rows selected
>
> Why?
Received on Wed Jan 16 2002 - 17:52:46 CST
