RE: Read Only User
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <charlottejanehammond@xxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 16 Dec 2004 10:19:01 -0500
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 =3D '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
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Charlotte Hammond
Sent: Thursday, December 16, 2004 9:51 AM
To: ORACLE-L
Subject: Read Only User
Hi all,
=20
I've been asked to shoehorn a user with "read only" access into a
database which wasn't designed to accommodate that.
=20
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. =20
=20
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.
=20
Any easier ideas? (9.2 btw)
=20
Thanks
- Charlotte
=09
---------------------------------
Do you Yahoo!?
Jazz up your holiday email with celebrity designs. Learn more.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
