Asinine security workarounds in Oracle, Part XXXXIII

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
Rich.Jesse_at_qtiworld.com              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: Rich.Jesse_at_qtiworld.com

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: ListGuru_at_fatcity.com (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).