| 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,
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 16 2004 - 09:22:32 CST
 |
 |