| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Read Only User
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
--
http://www.freelists.org/webpage/oracle-l[5]
--
http://www.freelists.org/webpage/oracle-l[6]
 |
 |