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@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.

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Other related posts: