Posted By:
Steve_Long
Posted On:
Monday, May 20, 2002 01:59 AM
there are several ways one can approach the matter. i will share with you my preferences.
first, each application database resides in its own application schema to which no program or user has the ability to connect.
second, application or user accounts are created and granted appropriate roles or privileges on objects in the application schema(s).
third, ALL database accesses should ALWAYS be prefixed with the schema name. i usually define servlet initialization parameters for all connection parameters and schema names used in the application, and then use variables for the connection and schema prefix when building the SQL statements. this allows changing the connection and schema by changing only init parameters.
another approach is to define synonyms to the primary schema from the connection account, but i advise against this due to the amount of overhead involed in maintaining synonyms on a per user basis (and public synonyms are usually undesirable as things quickly become "cluttered").
