Re: Oracle admin

The problem.
"Power" end-users prefer the flexibility that MS Access gives them?
Your front-end applications with their allowable values and client side constraints are too confining for them?
So they use their application user names and passwords to sneak in via ODBC or SQL*Plus and nibble away at your data security and integrity constraints, eh?

Here's what I do...

First, create task or application specific roles that are password protected.
SQL> create role mds_resume_keyer identified by tiger;

Then grants are made only to the roles not to the individual end-user accounts.
SQL> grant select, insert on resumes to mds_resume_keyer;

Grant these roles to the end-user accounts, but don't have them enabled by default.
SQL> grant mds_resume_keyer to scott;
SQL> alter user scott default role all except mds_resume_keyer;

When selects and inserts first need to be performed on the resumes table within a VB application (or whatever) the role appropriate for that module is identified as being granted to the end-user. An SQL call> select granted_role from user_role_privs where granted_role = 'MDS_RESUME_KEYER';
If this returns true then the role name is passed to a protected stored procedure (containing the roles and their passwords) that the end-user has been granted execute on, which returns the password for the role. The application then enables that role for the end-user with the password from the procedure.

An SQL call> set role mds_resume_keyer identified by tiger;

The role and it's corresponding privileges is enabled until explicitly disabled or for the duration of the connected session.

One word of caution, roles and procedures are like oil & vinegar, they don't mix well.

I'm sure that this is not the only solution to providing end-users with a
"guided experience into their data".

Regards,

Craig M. Wall Sr. Oracle DBA / Manager of Oracle Technology

<EOF>

Audun wrote in message <3472d883.100003817_at_193.212.86.34>... Can anyone give me some tips regarding user admin. The question is:

How do you prevent a end-user using "direct-access"-tools (SQL-Plus...) from damaging tables to which they have privileges via user applications (build in PowerBuilder, VB ....)?

If anyone could tell me about their solution to this area, I would be very grateful.

TIA Audun Jensen Received on Wed Nov 19 1997 - 00:00:00 CST