ReportWriter and SQL*PLUS Security

I'm new to Oracle and have been knocking this problem around for a while now:

I have a database with about 100 users. We use OPS$ type logins. The users are split
into several classes, each of which can access some subset of all of the tables contained in the database. The Oracle login for each of these users are assigned roles which grant them the appropriate privileges for their user class.

Also, all normal users, i.e. non-dba/developer type users, can only access the database via a Uniface GUI. ( Uniface is kind of like Oracle FORMs, it lets you build GUI interface screens to access the database ) The Uniface GUI also adds some restrictions to database access. For example, some of our tables have a column where the user can add his/her comments to an entry. Several users can potentially access the same entry and add their own comments, but they can only update comments that they have made. ( When a user adds a comment the Uniface GUI automatically adds their UNIX user id to an id column in the same table for that entry ) This is only enforced by the Uniface GUI. There is currently nothing at the Oracle level to enforce this constraint.

What I want to be able to do is give the normal users the ability to write and execute ad hoc queries so I don't have to spend the rest of my life writing them :-). To accomplish this I would like give them access to ReportWriter ( Reports V2 eventually ) and/or SQL*PLUS. BUT, I need to preserve the restrictions enforced by the Uniface GUI. The problems I encounter is:

1. ReportWriter allows the user to execute SQL commands with no restrictions. It does not reference the PRODUCT_USER_PROFILE table. If I allow normal users access to ReportWriter, then they can update comment column entries even if they didn't add the comment, which is a no-no.

    Does anyone know if there is a way to disable the SQL access except     for selects? I talked to Oracle support and they say no and that Reports     will work the same. Anybody out there figure out how to beat this?     If the only thing users can do from ReportWriter (RW) is selects, then I     think my problem is solved. I can let everybody use RW and let each     user's roles control what tables they can see. Does RW run a file,     ala SQL*PLUS, at startup that I can put SQL code into to disable     and/or reconfigure the users privileges?

 2. Creating an Oracle reports login means that everyone must share the

    same login and control over who can see which tables is lost and     everyone can access everyone else's reports. Not a good solution.

 3. Since the ad hoc report requirements are pretty simple at this

    point, I pondered giving users access to SQL*PLUS for the short term     and implementing security via the PRODUCT_USER_PROFILE (PUP), i.e. taking     away everything but select. However, I still want the dba's and     developers to be able to run whatever they want from SQL*PLUS. Alas,     there is no pattern we can use to separate the normal users out in     a regular expression for the PUP table entry so it looks like we     would have to have entries for EACH user. This is a bit nasty from     an admin. perspective.

    I thought I could use the gloabal startup file to check who the     user is and if they are a normal user query their roles, decompose     it into their table privileges and revoke all privileges other than     select, also not a pleasant undertaking. Anyone have any thoughts     here?

 4. I also thought about trying to enforce the constraints that Uniface

    enforces via Oracle triggers, but yet again this seems like a lot of     work.

It seems to me that I am overlooking something. I didn't expect that trying to let users perform ad hoc queries would be so complicated.

I am running Oracle7.0.12 but am upgrading to 7.0.15.4 shortly.

Any help would be greatly appreciated.

-- 



---------------------------------------------------

| Joe Miklewicz  908-949-3061  jmjm_at_hogpa.att.com |


---------------------------------------------------