Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Read Only User

RE: Read Only User

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Thu, 16 Dec 2004 18:27:12 +0100
Message-Id: <200412161727.iBGHRCXe009910@webmail.nexlink.net>

 

Charlotte,

    IMHO the impact will be much much less than an additional index. Would you worry so much about an index ?

Regards,

Stephane Faroult

RoughSea Ltd
http://www.roughsea.com

On Thu, 16 Dec 2004 08:45 , Charlotte Hammond <charlottejanehammond_at_yahoo.com>sent:

Hi Mark,

Thanks for the suggestion. Your suggestion sounds a lot easier to automate which would save a lot of time.

However this is a heavy-use OLTP database with a lot of users and I'm concerned about all these extra trigger calls every time somebody changes anydata. Performance is always an issue and I'm reluctant to introduce any additional processing which will be redundant for 99% of users.

Any thoughts on the impact?

Thanks again
- Charlotte

"Bobak, Mark" <Mark.Bobak_at_il.proquest.com[1]> wrote:Charlotte,

Unfortunately, I don't see any way to get around the packaged functions problem, short of writing wrappers. There is no way to grant or revoke execute on procedures/functions within a particular package.

Ok, here's a thought.....but I warn you, it's not pretty either.....

Grant execute on all required packages, even if they have functions/procedures that can update tables.

Create a trigger, one per table, that does something like:

create or replace trigger stop_updates
before insert or update or delete
on some_table
begin
if user = 'READ_ONLY_USER' then
raise_application_error(-20001,'Read-only user is not allowed to update this table!');
end if;
end;
/

Hope that helps,

-Mark

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI

-----Original Message-----

From: oracle-l-bounce_at_freelists.org[2]
[oracle-l-bounce_at_freelists.org[3]','','','')">oracle-l
-bounce_at_freelists.org[4][]On Behalf Of Charlotte Hammond
Sent: Thursday, December 16, 2004 9:51 AM To: ORACLE-L
Subject: Read Only User

Hi all,

I've been asked to shoehorn a user with "read only" access into a database which wasn't designed to accommodate that.

Creating a role with select only on tables and views was easy but I'm struggling with how to handle packaged functions (which allow indirect access to view data). I can't grant execute on the whole package, as it also contains procedures that allow data changes.

I could create wrapper packages with only the functions exposed, but that looks like a great big maintenance swamp as this isn't a very stable app and the developers keep on changing the package interfaces.

Any easier ideas? (9.2 btw)

Thanks
- Charlotte



Do you Yahoo!?
Jazz up your holiday email with celebrity designs. Learn more.

--

http://www.freelists.org/webpage/oracle-l[5]



Do you Yahoo!?
Dress up your holiday email, Hollywood style. Learn more.

--

http://www.freelists.org/webpage/oracle-l[6]

Received on Thu Dec 16 2004 - 11:30:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US