Re: Q: ORACLE Logon Account

In article <1994Jul8.071111.21234_at_ericsson.se>, ebcrbj_at_ebcw150.ericsson.se (Roger Bjaerevall) writes:
|> We are developing an application with OCI calling ORACLE DML statements
|> and also PL/SQL routines.
|>
|> Our problem is how to connect to the database; as one account per user
|> or one specific account (hidden in the application).
|>
|> 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).
|>
|> Question: It seams impossible to have one account per user (to ensure the sequrity)
|> if there are business specific rules & management in an application that
|> process the information WIHTOUT using PL/SQL!?
|>
|> Will GRANT EXECUTE ever work for OS applications? ;-)
|>
|> +------------------------------------------------------------------+
|> + _/_/_/ _/_/_/ _/_/_/_/ Roger Bjärevall +
|> + _/ _/ _/ _/ _/ BO/EBC/FL/RGFC +
|> + _/ _/ _/ _/ _/ Ericsson Business Networks AB +
|> + _/_/_/_/ _/_/_/_/ _/ 135 83 Tyresö +
|> + _/ _/ _/ _/ _/ _/ Sweden +
|> + _/ _/ _/ _/ _/ _/ +46 8 6824851 +
|> + _/ _/ _/_/_/ _/_/ email: ebcrbj_at_ebc.ericsson.se +
|> +------------------------------------------------------------------+
|>

Grant all privileges via roles and password protect the roles. Then grant the roles to the users, but make NONE of these roles default for the users. place code in the front of your application to "set" the role (you may want to use some mechanism for retrieving the password for the role - Stored procedure). In this way if the user connects with the application, his roles are automatically enabled, but if he connects via sqlplus he must "set" the role in order to have the privileges ( and if he doesn't have the passwords to the roles he can't "set" them). Hope this helps.

  __________ /    __      __/______  |Jack C. Carter,  Systems Architect
    / _  _  /_   / ' _  _  /  _  _   |Cap Gemini America - Consulting
 \_/ (/ (_ /|   /_, (/ /' /, (' /'   |EMAIL jack.carter_at_nt.com
 ----------------------------------- |PHONE: (214) 933-4314