Re: Using (public) synonyms on views using the Oracle type/object concept

Do the grants directly, instead of thru roles. I think that should solve your problem.
(a pain I know, but there are SOME limits to what ORACLE can do)

Øyvind wrote:
>
> I have problems accessing views containing the Oracle type/object
> concept when logged on as something else than the view creator.
> Concretely, run the following script and see. Everything created will
> be dropped at the end, your database will not be polluted. Substitute
> the yourUid and yourPw and yourService with a user having the right to
> create users and roles:
>
> CONNECT yourUid/yourPw_at_yourService
>
> CREATE USER test1 IDENTIFIED BY test1;
> GRANT CONNECT TO test1;
> GRANT RESOURCE TO test1;
> GRANT DBA TO test1;
>
> CREATE ROLE test_role;
> CREATE USER test2 IDENTIFIED BY test2;
> GRANT CONNECT TO test2;
> GRANT test_role TO test2;
>
> COMMIT;
>
> CONNECT test1/test1_at_layc;
>
> CREATE TABLE t1
> (
> pk11 INT NOT NULL,
> PRIMARY KEY(pk11)
> );
>
> GRANT SELECT ON t1 TO test_role;
>
> INSERT INTO t1 VALUES(1);
>
> CREATE TABLE t2
> (
> pk21 VARCHAR2(1) NOT NULL,
> PRIMARY KEY(pk21)
> );
>
> GRANT SELECT ON t2 TO test_role;
>
> INSERT INTO t2 VALUES('A');
> INSERT INTO t2 VALUES('B');
> INSERT INTO t2 VALUES('C');
> INSERT INTO t2 VALUES('D');
>
> CREATE OR REPLACE TYPE my_ty AS OBJECT
> (
> pk21 VARCHAR2(1)
> );
> .
> /
> CREATE OR REPLACE TYPE my_ty_list AS TABLE OF my_ty;
> /
> CREATE OR REPLACE TYPE v1_ty AS OBJECT
> (
> pk11 NUMBER
> , twoers my_ty_list
> );
> /
>
> CREATE OR REPLACE VIEW v1 OF v1_ty
> WITH OBJECT oid(pk11)
> AS SELECT pk11
> , CAST(
> MULTISET(
> SELECT pk21
> FROM t2
> ) AS my_ty_list
> ) twoers
> FROM t1;
>
> GRANT SELECT ON v1 TO test_role;
>
> SELECT *
> FROM t1;
>
> SELECT *
> FROM t2;
>
> SELECT pk11
> FROM v1;
> /*
> Works fine, because logged on as creator
> */
>
> SELECT pk11
> , CAST(
> MULTISET(
> SELECT pk21
> FROM t2
> ) AS my_ty_list
> ) twoers
> FROM t1;
> /*
> Works fine, because logged on as creator
> */
>
> COMMIT;
>
> CREATE PUBLIC SYNONYM t1 FOR t1;
> CREATE PUBLIC SYNONYM t2 FOR t2;
> CREATE PUBLIC SYNONYM v1 FOR v1;
> CREATE PUBLIC SYNONYM v1_ty FOR v1_ty;
> CREATE PUBLIC SYNONYM my_ty FOR my_ty;
> CREATE PUBLIC SYNONYM my_ty_list FOR my_ty_list;
>
> COMMIT;
>
> CONNECT test2/test2_at_layc;
>
> SELECT *
> FROM t1;
>
> SELECT *
> FROM t2;
>
> SELECT pk11
> FROM v1;
>
> /*
> Gives the error:
> ----------------
> FROM v1
> *
> ERROR at line 2:
> ORA-00904: invalid column name
>
> */
>
> SELECT pk11
> , CAST(
> MULTISET(
> SELECT pk21
> FROM t2
> ) AS my_ty_list
> ) twoers
> FROM t1;
>
> /*
> Gives the error:
> ----------------
>
> ) AS my_ty_list
> *
> ERROR at line 6:
> ORA-00904: invalid column name
> */
>
> CONNECT yourUid/yourPw_at_yourService
>
> DROP USER test1 CASCADE;
> DROP USER test2 CASCADE;
> DROP ROLE test_role;
> DROP PUBLIC SYNONYM t1;
> DROP PUBLIC SYNONYM t2;
> DROP PUBLIC SYNONYM v1;
> DROP PUBLIC SYNONYM v1_ty;
> DROP PUBLIC SYNONYM my_ty;
> DROP PUBLIC SYNONYM my_ty_list;
>
> COMMIT;
Received on Fri Oct 05 2001 - 22:06:37 CDT