RE: Read Only User
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>, Charlotte Hammond <charlottejanehammond@xxxxxxxxx>
- Date: Thu, 16 Dec 2004 18:27:12 +0100
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@xxxxxxxxx>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@xxxxxxxxxxxxxxx[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@xxxxxxxxxxxxx[2]
[oracle-l-bounce@xxxxxxxxxxxxx[3]','','','')">oracle-l
-bounce@xxxxxxxxxxxxx[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.
--
//www.freelists.org/webpage/oracle-l[5]
---------------------------------
Do you Yahoo!?
Dress up your holiday email, Hollywood style. Learn more.
--
//www.freelists.org/webpage/oracle-l[6]
--- Links ---
1 javascript:parent.opencompose('Mark.Bobak@xxxxxxxxxxxxxxx','','','')
2 javascript:parent.opencompose('oracle-l-bounce@xxxxxxxxxxxxx','','','')
3 javascript:parent.opencompose('<a href=
4 javascript:parent.opencompose('oracle-l-bounce@xxxxxxxxxxxxx','','','')
5
modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l
6
modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
