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
Security threats 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:
There is a standard
CONNECT-role which is assigned to new schemas automatically. This role includes a
CREATE SESSION- but also includes other
CREATE TABLE- and
CREATE VIEW-privileges which are unneeded for application users.
Each function is given a role, so that these roles can be
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.