Oracle SQL - Introduction User Management

  1. HOME
  2. Oracle SQL
  3. Introduction
  4. User Management

Introduction

SQL offers several commands to implement data security and to restrict data access.

  • CREATE USER, to define new database users
  • ALTER USER, to change properties (privileges and passwords) of existing database users
  • DROP USER, to remove user definitions from the database

Privileges and Roles

The Oracle DBMS offers two types of privileges: system privileges and object privileges.

  • System privileges include the right to perform non-object-related actions; for example, CREATE SESSION privilege (log on to the database) and the CREATE TABLE privilege.
  • Object privileges involve the right to access a specific database object in a specific way; for example, issue SELECT, INSERT, and UPDATE commands against the EMPLOYEES table.

The following table 2-2 lists the most important Oracle object privileges.

Object Privilege Allowable Action
ALTER Change the table structure with ALTER TABLE
DELETEDelete rows
EXECUTE Execute stored functions or procedures
FLASHBACK Go back in time with FLASHBACK TABLE
INDEX Create indexes on the table
INSERTInsert new rows
REFERENCESCreate foreign key constraints to the table
SELECTQuery the table or view
UPDATEChange column values of existing rows

The Oracle DBMS can group privileges into roles.

You will need to be given the 'create role' privilege by your DBA.

The following are the SQL commands used to manage these privileges and roles:

  • GRANT, to grant certain privileges or roles to users or roles
  • REVOKE, to revoke certain privileges or roles from users or roles

A typical scenario is the following: 

--creates a new (empty) role. 
CREATE ROLE <role name> 
-- populates the role with a mix of object and system privileges.  
GRANT privileges TO <role name> 
--grants the role and all its privileges to a user in a single step. 
GRANT <role name> TO user(s)