How-to: Security, Roles and Privileges


A schema is a set of objects owned by a user, A user automatically has all object privileges for schema objects contained in his or her schema. A user can grant any object privilege on any schema object he or she owns to any other user or role.

A privilege can be granted explicitly. For example, the privilege to insert records into the EMP table can be explicitly granted to the user SCOTT.
Alternatively a privilege can be granted to a role (a named group of privileges), and then the role can be granted to one or more users.
For example, the privilege to insert records into the EMP table can be granted to the role named CLERK, which in turn can be granted to the users SCOTT and BRIAN.

The SQL statements GRANT and REVOKE, respectively allow /disallow access.

Three Schema Security Model

Typically all database tables are stored in a single schema - security options are then set to grant other schemas/users access to the data.

The 'Three Schema Security Model' for database security:

Level 1 Schema - Owns all application data tables. e.g. APP_DATA
Level 2 Schema - Owns Views and Packages needed to access the application data e.g. APP_CODE
Level 3 Schema - Used for application users to connect to the database. e.g. APP_USER

This arrangement makes it easy to do imports and exports
A USER level EXPORT of APP_DATA will export all the data but not Views, Packages or Procedures.
A USER level IMPORT of APP_CODE will update the application code without affecting any data.

To make this work will require some permissions to be set:

Level 1 Schema - Grant the required SELECT, UPDATE, DELETE privileges to the Level 2 Schema.

Level 2 Schema - Objects in this schema use privileges granted by the first schema to access the data.

Level 3 Schema - Uses privileges granted and objects created by the second schema to access the data.

None of the 3 schemas should be given full DBA permissions.
Application users should never be allowed to connect to the database via a 'single' schema that owns all data and objects.

Rather than the 'level 3 schema' being a single schema/logon/password, you can choose to implement multiple schemas, one for each user - in this case privileges should be granted to each level 3 schema using a ROLE.


Any role can be granted to any database user.

GRANT role TO [user,] [role,]

Each role granted to a user is, at a given time, either enabled or disabled. This can be used to provide selective availability of privileges.

A role can be granted to other roles (an indirect grant.)
However, a role cannot be granted to itself and cannot be granted circularly. For example, role A cannot be granted to role B if role B has previously been granted to role A.

An indirectly granted role can be explicitly enabled or disabled for a user. However, by enabling a role that contains other roles, you implicitly enable all indirectly granted roles of the directly granted role.

The following default roles are provided only for backward compatibility with earlier versions of Oracle:
It is recommended that you create new roles to suit the specific needs of your application.

A role can be granted system or schema object privileges.

A schema object privilege is a privilege or right to perform a particular action on a specific schema object, different object privileges are available for different types of schema objects.

A system privilege is the right to perform a particular action, or to perform an action on any schema objects of a particular type. There are over 60 distinct system privileges.

Selective privileges are used to restrict a user's INSERT and UPDATE privileges to sensitive data.
With selective UPDATE, a user can update only specific columns of a row.
With selective INSERT, a user can insert a row with values for the selected columns. All other columns receive NULL or the column's default value.

To use a view, you require appropriate privileges only for the view itself. You do not require privileges on base objects underlying the view.

Views add two more levels of security for tables, column-level security and value-based security:
A view can provide access to selected columns of base tables and also displays only selected rows (if a WHERE clause is used in the definition of the view)

Privileges to run code

Invoker Rights - An invoker-rights procedure executes with all of the invoker's roles and privileges. This is also the default for anonymous PL/SQL blocks.

When using Invoker Rights the invoker needs privileges at runtime to access program references embedded in DML statements or dynamic SQL statements.

Definer Rights - A user of a definer-rights procedure requires only the privilege to execute the procedure; not on the underlying objects that the procedure accesses, because a definer-rights procedure operates under the security domain of the user who owns the procedure, regardless of who is executing it.

When using Definer Rights it's essential that the procedure's owner has all the necessary object privileges for all referenced objects.

Fewer privileges have to be granted to users of a definer-rights procedure, resulting in tighter control of database access. See the PL/SQL User's Guide and Reference for more.

Secure Application Roles

Secure application roles can only be enabled by authorized PL/SQL packages. This mechanism restricts the enabling of roles to the invoking application. Application developers no longer need to secure a role by embedding passwords inside applications. Instead, they create a secure application role and
specify which PL/SQL package is authorized to enable the role.


Because of the restriction that users can not change security domain inside Definer's Right procedures, secure application roles can only be enabled inside Invoker's Right procedures.

User Settings and Quotas

Oracle provides a way to direct and limit the use of disk space allocated to the database for each user, including default and temporary tablespace and tablespace quota.
Each user is assigned a profile that can limit the system resources available to the user.
e.g. Number of concurrent sessions, CPU processing time, amount of logical I/O.

Oracle can lock a user's account if a specified number of password attempts fail. Depending on how the account is configured, it can be unlocked automatically after a specified time interval.
Password lifetime and expiration, password history/complexity are also controlled via the CREATE PROFILE statement

The database uses password files to keep track of database usernames who have been granted the SYSDBA and SYSOPER privileges. These privileges allow DBAdmin and DBOperator rights.

The User Group PUBLIC

Each database contains a user group called PUBLIC. The PUBLIC user group provides public access to specific schema objects, such as tables and views, and provides all users with specific system privileges. Every user automatically belongs to the PUBLIC user group.
As members of PUBLIC, users can see (select from) all data dictionary tables prefixed with USER and ALL. Additionally, a user can grant a privilege or a role to PUBLIC. All users can use the privileges granted to PUBLIC.

You can grant or revoke any system privilege, object privilege, or role to PUBLIC.
However, to maintain tight security over access rights, grant only privileges and roles that are of interest to all users to PUBLIC.

Granting and revoking some system and object privileges to and from PUBLIC can cause every view, procedure, function, package, and trigger in the database to be recompiled.

PUBLIC has the following restrictions:
You cannot assign tablespace quotas to PUBLIC, although you can assign the UNLIMITED TABLESPACE system privilege to PUBLIC.
You can create database links and synonyms as PUBLIC (using CREATE PUBLIC DATABASE LINK/SYNONYM), but no other schema object can be owned by PUBLIC.

"They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety" ~ Benjamin Franklin.



Copyright © 1999-2024
Some rights reserved