RE: Read Only Login with Source Visibility

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Tue, 05 Nov 2002 10:59:19 -0800
Message-ID: <F001.004FBFCE.20021105105919@fatcity.com>

better yet - create a local copy of it, and place a private synonym in their account to point to it.

really sneaky.

-----Original Message-----
Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L

Rewrite the all_source view, and I believe the all_objects view.

It's a pain in the rear, but it can be done.

It also tends to break when you upgrade.

I did this once on Oracle 7, there may be some other workaround now on 8i.

Jared

Bill Buchan <wbuchan_at_uk.intasys.com>
Sent by: root_at_fatcity.com
 11/05/2002 04:48 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Read Only Login with Source Visibility

Hi all,

I am trying to create a read-only login, RO_USER which can do the following:

1. See all tables, views, constraints etc. in one *specific* other schema, APP.
2. See all the source for PL/SQL objects in APP.

The first bit is easy: GRANT SELECT on tables, views to RO_USER.

Not sure about the second bit. I have tried granting CREATE ANY PROCEDURE

and CREATE ANY TRIGGER to RO_USER and using a DDL trigger to prevent these

privileges actually being used to create procedures/triggers. This works but does not restrict the source visibility to APP. I have other schemas where I do not want RO_USER to see the source.

My other concern is granting SELECT on sequences as this means that they can select nextval from them and hence increment the numbers (not quite read-only!)

Any suggestions for fixing these problems would be much appreciated!

Thanks
- Bill.

PS. This is on 8i.

--

Intasys Billing Technologies Ltd. www.intasysbilling.com 74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX tel (0)131 625 8200 fax (0)131 625 8201 email wbuchan_at_uk.intasys.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Bill Buchan
  INET: wbuchan_at_uk.intasys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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). Received on Tue Nov 05 2002 - 12:59:19 CST