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

From: Øyvind <oor_at_laycan.com>
Date: 28 Sep 2001 02:19:52 -0700
Message-ID: <774e12ad.0109280119.1e7096f@posting.google.com>

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 Sep 28 2001 - 04:19:52 CDT