RE: Read Only User

But does the read only user really have to have access to the stored code
routines?  Eliminate that requirement and then you do not need to worry
about either the overhead of an extra trigger on every table or having to
maintain masking routines for every package that has update procedures on
your system.

If you have to provide access to some of the stored code routines then maybe
you can do it on a case by case basis as needed allowing you to do nothing
for most of the code.

HTH -- Mark D Powell --


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Charlotte Hammond
Sent: Thursday, December 16, 2004 11:45 AM
To: ORACLE-L
Subject: RE: Read Only User


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
any data.  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> 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
[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,

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.

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


                
---------------------------------
Do you Yahoo!?
 Dress up your holiday email, Hollywood style. Learn more.

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

Other related posts: