| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: With statement alias problem/10g
I get the same results in 9i. It appears that separate privileges are
required to select from anything used in the subquery factoring (with)
clause. Granting select on the view is sufficient for anything
elsewhere in the view, but not in the with clause. The separate
privileges can even be through a role and do not need to be granted
explicitly. I have added a little to your demo to show that below.
Since one of the common uses of views is to restrict access to certain
columns, rather than grant select on the whole underlying table, I
would tend to lean toward calling this a bug, unless someone can
explain why this is intended behavior. The workarounds are to either
grant separate prvileges or not use the with clause when creating a
view.
scott_at_ORA92> CREATE USER USER1 IDENTIFIED BY USER1 DEFAULT TABLESPACE
USERS
2 QUOTA UNLIMITED ON USERS
3 /
User created.
scott_at_ORA92> GRANT CREATE TABLE TO USER1 2 /
Grant succeeded.
scott_at_ORA92> GRANT CREATE VIEW TO USER1
2 /
Grant succeeded.
scott_at_ORA92> GRANT CREATE SESSION TO USER1 2 /
Grant succeeded.
scott_at_ORA92> GRANT CREATE ROLE TO user1
2 /
Grant succeeded.
scott_at_ORA92> CREATE USER USER2 IDENTIFIED BY USER2 DEFAULT TABLESPACE
USERS
2 QUOTA UNLIMITED ON USERS
3 /
User created.
scott_at_ORA92> GRANT CREATE SESSION TO USER2 2 /
Grant succeeded.
scott_at_ORA92> CONN USER1/USER1
Connected.
scott_at_ORA92> @ LOGIN
scott_at_ORA92> SET ECHO OFF
GLOBAL_NAME
user1_at_ORA92> CREATE TABLE test (a NUMBER) 2 /
Table created.
user1_at_ORA92> CREATE TABLE test2 (a NUMBER) 2 /
Table created.
user1_at_ORA92> CREATE ROLE test_role
2 /
Role created.
user1_at_ORA92> GRANT SELECT ON test2 TO test_role 2 /
Grant succeeded.
user1_at_ORA92> GRANT test_role TO user2
2 /
Grant succeeded.
user1_at_ORA92> CREATE OR REPLACE VIEW testView3 AS
2 WITH
3 testAlias AS
4 (SELECT COUNT(*) X
5 FROM test)
6 SELECT testAlias.X
7 FROM testAlias
8 /
View created.
user1_at_ORA92> CREATE OR REPLACE VIEW testView4 AS
2 SELECT testAlias.X
3 FROM (SELECT COUNT(*) X
4 FROM test) testAlias
5 /
View created.
user1_at_ORA92> CREATE OR REPLACE VIEW testView5 AS
2 WITH
3 testAlias AS
4 (SELECT COUNT(*) X
5 FROM test2)
6 SELECT testAlias.X
7 FROM testAlias
8 UNION ALL
9 SELECT COUNT (*) x
10 FROM test
11 /
View created.
user1_at_ORA92> GRANT SELECT ON testView3 TO USER2 2 /
Grant succeeded.
user1_at_ORA92> GRANT SELECT ON testView4 TO USER2 2 /
Grant succeeded.
user1_at_ORA92> GRANT SELECT ON testView5 TO USER2 2 /
Grant succeeded.
user1_at_ORA92> CONN USER2/USER2
Connected.
user1_at_ORA92> @ LOGIN
user1_at_ORA92> SET ECHO OFF
GLOBAL_NAME
user2_at_ORA92> SELECT * FROM USER1.testView3
2 /
SELECT * FROM USER1.testView3
*
user2_at_ORA92> SELECT * FROM USER1.testView4 2 /
X
0
user2_at_ORA92> SELECT * FROM USER1.testView5 2 /
X
0
0
user2_at_ORA92> SELECT * FROM V$VERSION
2 /
BANNER
user2_at_ORA92> CONNECT scott/tiger
Connected.
user2_at_ORA92> @ LOGIN
user2_at_ORA92> SET ECHO OFF
GLOBAL_NAME
scott_at_ORA92> DROP USER user2 CASCADE
2 /
User dropped.
scott_at_ORA92> DROP USER user1 CASCADE
2 /
User dropped.
scott_at_ORA92> DROP ROLE test_role
2 /
Role dropped.
scott_at_ORA92> Received on Fri Aug 12 2005 - 16:01:51 CDT
 |
 |