Re: create view problem

On 10 Jan 2002 05:00:53 -0800, dm_at_belkam.com (Dmitry Melekhov) wrote:

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

Privilege through a role, so not directly and roles are disabled during compilation of stored procedures and views.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Jan 10 2002 - 23:49:12 CST