† Wolfgang H. Franke




This document describes best-practices for application-development on oracle-dbms. The rationale is to GRANT minumum rights for security and simplicity. The sample project used within this document is called ELVE.

Security threats addressed:

Countermeasures addressed:



This is the project-schema. It is used to store all db-objects like tables and views that belong to the project.


This is the test-user-schema of the project. It is used to test the security settings for project users. It is assigned one single role: ELVE_USER.

There is a standard CONNECT-role which is assigned to new schemas automatically. This role includes a CREATE SESSION- but also includes other CREATE- like CREATE TABLE- and CREATE VIEW-privileges which are unneeded for application users.


Each function is given a role, so that these roles can be granted and revoked from users without interference of other functions. Each following role is a superset of the preceding one, so that only one single role is needed to be granted to access all relevant functions. Currently there are 4 known levels of access:


    This is the lowest level role for anonymous users of the project like web-/wap-servers and other machines. It does allow CREATE SESSION and SELECT on public data only and normally no modification to the database.


    This is the role for users of the project. It has minimum rights on the objects used by the project. Especially theres no SELECT-privilege on tables that are encapsulated by views and no UPDATE-privilege on tables or views that are used for display only.


    This is the role for administrators of the project. It does allow setting some global parameters of the project that affect all users and the system behavior.


    This is the role for developers of the project. It is assigned additional rights and privileges needed to create the projects database-objects. Especially different CREATE-privileges are needed. Also it must have the right to grant to the other roles.