Re: Q: ORACLE Logon Account

Roger Bjaerevall (ebcrbj_at_ebcw150.ericsson.se) wrote:
: We are developing an application with OCI calling ORACLE DML statements
: and also PL/SQL routines.
:
: We would like to have one account per user, BUT this is not to good
: because each account requires the SELECT, INSERT, UPDATE and DELETE
: privileges which in turn means that they can enter SQL*PLus and destroy the
: information. (This is no problem for the PL/SQL routines which encapsulates
: the table processings).
:
: Will GRANT EXECUTE ever work for OS applications? ;-)

Well... This situation is exactly the reason for having so-called "IDENTIFIED ROLES" in Oracle7. You basically define a ROLE which has the appropriate rights needed in your application (SELECT, INSERT, UPDATE and DELETE privileges on the tables), but do *NOT* make it the default tole of the users (which can be now either 1 userid or 1 for each - personally I would prefer one for each). This ROLE you define with a (secret) password, so no one can use this role without knowing the password. The default role of the users will only need CONNECT privileges.

In your OCI or Pro*C program after connecting to the database, execute an "ALTER SESSION SET ROLE ..." statement where you specify the password. You could do some encryption here, to make sure nobody can see the password even if they have a good look at the executable.

And now the application has all the rights it needs, while if the users connect with e.g. SQL*Plus, they have much more limited rights.

Sandor Nieuwenhuijs
Oracle Netherlands

• These opinions (if any) are just mine. I do not represent Oracle ---
• Corporation in this Posting ---