Oracle Users, Grants and Roles


Introduction

User Types

Types of users include database administrators and security people, applications developers and administrators, network people and finally the general user population.

Users

A user account is created to acces the database. The SYS and SYSTEM accounts are created by Oracle on database installation. The first action required is user creation. Users are assigned rights allowing actions directly or through roles. Rights are called system and object privileges. System privileges allow performance of database actions and object privileges allow access to objects, ie. tables, table columns, indexes, synonymns, procedures, etc.

Creating Users

CREATE USER user IDENTIFIED {BY password | EXTERNALLY}
{
	[DEFAULT TABLESPACE tablespace]
	[TEMPORARY TABLESPACE tablespace]
	[ { QUOTA {n [K|M] | UNLIMITED} ON tablespace } [, ... ] ]
	[PROFILE profile]
} [ ... ]
;

Altering Users

ALTER USER user IDENTIFIED {BY password | EXTERNALLY}
{
	[DEFAULT TABLESPACE tablespace]
	[TEMPORARY TABLESPACE tablespace]
	[ { QUOTA {n [K|M] | UNLIMITED} ON tablespace } [, ... ] ]
	[PROFILE profile]
	[ DEFAULT ROLE { { ALL [EXCEPT role [... ]] } | NONE | { role [ ... ] } ]
} [ ... ]
;

The ALTER USER command is as the CREATE USER command except for the DEFAULT ROLE clause. Also note that if a user is set to use a different tablespace that all subsequent object creations only will be placed into the newly designated tablespace.

Dropping Users

DROP USER user [CASCADE];

With the CASCADE option any objects the user owns will also be dropped when the user is dropped.Note that DROP USER is a DDL command and cannot be rolled back. One method of retaining objects created and owned by the user is to not drop the user but to revoke the CREATE SESSION privelage on the user to prevent the user from logging into the database.

Grants

System Privileges

System privileges allow for system-wide (global) actions on a specified object.

GRANT {system privilege [, ... ] } TO { { user | role | PUBLIC } [, ... ] } [WITH ADMIN OPTION];

All users are required to have the CREATE SESSION privelage in order to access the database. Each user must be granted the CREATE SESSION privelage either directly or through a role. System privileges can be granted by one user to other users when the user granting the privelage has the WITH ADMIN OPTION.

Object Privileges

Object privileges allow a user to perform a specified action on a specific object. Other users can access user-owned objects by preceeding the object name with the user name (<username>.<object>). Object privileges extend down to table columns.

GRANT {object privilege [, ... ] | ALL [PRIVILEGES] } ON [schema.] object TO { { user | role | PUBLIC } [, ... ] } [WITH GRANT OPTION];

Column Privileges

GRANT {object privilege [, ... ] | ALL [PRIVILEGES] } [(column [, ... ])] ON [schema.] object TO { { user | role | PUBLIC } [, ... ] } [WITH GRANT OPTION];

Only INSERT, UPDATE and REFERENCES privileges can be granted at the column level. Include all not null columns in the INSERT grant at the column level.

Revoking Grants

Note that system privileges will not cascade and object privileges will cascade.

Revoking System Privileges

REVOKE {system privilege [, ... ] } FROM { { user | PUBLIC } [, ... ] };

Revoking Object Privileges

REVOKE {object privilege [, ... ] | ALL [PRIVILEGES] } ON [schema.] object FROM { { user | role | PUBLIC } [, ... ] } [CASCADE CONSTRAINTS];

Use of Roles

Roles are used to place sets of privileges into groups. These grouped privelage sets can then be granted to different types of users such that those users can fulfill specific functions. Those functions could be Database Administrators, Developers or general Users, amongst many other types. Roles can not include object creation privileges but do include most object and system privileges.

Roles have a number of benefits.

Creating Roles

CREATE ROLE role [NOT IDENTIFIED | IDENTIFIED {BY password | EXTERNALLY } ];

Role creation requires the CREATE ROLE privelage.

Adding Grants to Roles

Granting of roles requires either that the grantor created the role or that the user has the GRANT ANY ROLE privelage.

GRANT role TO { user | role };
GRANT {system privilege [, ...]} TO { user | role };
GRANT { object privilege | ALL [PRIVILEGES] } [(column [,...])] ON [schema.] object TO { rtole | user };

Setting Roles

The default for active roles is set to ALL for a user when that user is created. The DBA can change that default.

Database Administrator Roles

OSOPER and OSDBA

These roles are created on Oracle database installation. These roles are required to access the database when the database is not mounted and are used for database administration. These roles are used when connecting as user INTERNAL (DBA) in the server manager program.

To CONNECT INTERNAL with the OSOPER or OSDBA roles issue one of the following commands shown below.

CONNECT SYS AS SYSDBA or CONNECT SYS AS SYSOPER

Note that when a database administrator is connected as SYSDBA or SYSOPER that by default, any objects created by the logged in user will be created into the SYS user schema.

OSOPER

The OSOPER role can perform the database management commands and has the RESTRICTED SESSION privelage.

OSDBA

The OSDBA role has the OSOPER role granted to it. OSDBA also has WITH ADMIN OPTION enabled and has the RESTRICTED SESSION privelage aswell. The WITH ADMIN OPTION allows the OSDBA role to grant system privileges to other users.

CONNECT, RESOURCE and DBA Roles

These roles are created on the creation of a database.

The CONNECT Role

CONNECT role system privileges.

The RESOURCE Role

RESOURCE role system privileges.

The DBA Role

The DBA role includes all system privileges with the ability to grant those system privileges to all other users. The EXP_FULL_DATABASE and IMP_FULL_DATABASE roles are also granted automatically to the DBA role if they are created.

Export/Import Roles

Execution of the catalog.sql script or the catexp.sql script creates these two roles.

EXP_FULL_DATABASE Role

EXP_FULL_DATABASE system privileges.

IMP_FULL_DATABASE Role

The IMP_FULL_DATABASE has the BECOME_USER system privelage.