So, a developer asks me "Why can't I grant user CHARLIE access to my view in
schema ALPHA?"
I look in our 8.1.7.2 DB and see this setup:
Schema ALPHA:
-- Simple table.
CREATE TABLE my_table
(col1 VARCHAR2(10),
col2 VARCHAR2(10);
-- View to join ALPHA table to BRAVO table.
CREATE OR REPLACE VIEW my_view AS
SELECT a.col1, b.col2
FROM my_table a,
BRAVO.their_table b
WHERE a.col1 = b.col1;
Schema BRAVO:
-- Simple table.
CREATE TABLE their_table
(col1 VARCHAR2(10),
col2 VARCHAR2(10);
-- Let everyone see it.
GRANT SELECT ON their_table TO ALPHA;
GRANT SELECT ON their_table TO CHARLIE;
When I connect as ALPHA and try to "GRANT SELECT ON my_view TO CHARLIE", I
get:
ORA-1720, GRANT OPTION does not exist for "BRAVO.THEIR_TABLE"
Huh? Who cares? Everyone already has SELECT access to it! Who cares if
ALPHA does not have the WITH GRANT OPTION? So, as a workaround, I do this
while connected as ALPHA:
-- Remove "foreign" tables from view.
CREATE OR REPLACE VIEW my_view AS
SELECT a.col1
FROM my_table a;
-- Grant access.
GRANT SELECT ON my_view TO CHARLIE;
-- Replace view with original source.
CREATE OR REPLACE VIEW my_view AS
SELECT a.col1, b.col2
FROM my_table a,
BRAVO.their_table b
WHERE a.col1 = b.col1;
And it all works now! Does anyone have a better way to do this, other than
the GRANT OPTION, as Oracle Support states?
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
Disclaimer: I'm cranky, it's beer day, and it's over 3 hours till beer.
http://www.westbend.net/~legoman
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
