Basics of Privileges in Oracle 12c

By Chris Ruel, Michael Wessler

After you create a user, you have to decide what types of things the user can do in the Oracle 12c database. You probably want to allow the user to be able to log in, but you may need to limit whether the user can do any of the following tasks:

  • Accessing certain data

  • Starting and stopping the database

  • Creating tables, indexes, and views

  • Deleting data

  • Performing backups

You determine what a user can do via privileges. The database has two types of privileges:

  • System privileges control what a user can do in the database. For example, can they create tables, create users, and drop tablespaces? These privileges apply mainly to adding or changing structures in the database.

  • Object privileges control how a user can access the actual data in the database. For example, what data can he see, change, or delete? These privileges apply primarily to rows in a table or view.

You manage all privileges with the GRANT and REVOKE commands. It’s pretty clear from their names which one giveth and which one taketh away. However, you form the commands depending on the type of privilege. For example, when you give, you GRANT TO and when you take you REVOKE FROM.

System privileges are the first privileges any user needs. There are literally hundreds of system privileges.

Before any user can do anything with the database, they need to be able to connect, The CREATE SESSION privilege gives users access to the database. Without this privilege, no other privileges matter.

Follow these steps to grant CREATE SESSION privileges to the user DTITILAH:

  1. Log in to the database as the user SYSTEM.

  2. Type the following:

    <GRANT CREATE SESSION TO dtitilah;>

    You see the following, which means DTITILAH can connect to the database:

    Grant succeeded.

What if the password for the user DTITILAH has been compromised? A quick way to make sure that the user can no longer access the database, externally or not, is to revoke the CREATE SESSION privilege from that user.

Revoke the CREATE SESSION from DTITILAH with these steps:

  1. Log in to the database as SYSTEM.

  2. Type the following:

    <REVOKE CREATE SESSION FROM dtitilah;>

    You see this:

    Revoke succeeded.

    When that user tries to connect, he sees this:

    ERROR:
    ORA-01045: user DTITILAH lacks CREATE SESSION privilege; logon denied
  3. Address the security breach by finding out why the password was compromised.

A user might also have these system privileges that allow them to create objects in the database:

  • RESUMABLE allows jobs to be suspended and resumed when space restrictions are met.

  • FLASHBACK ARCHIVE allows users to retrieve data from the past.

  • CREATE JOB allows users to create jobs that can be run in the Oracle Scheduler.

  • CREATE SYNONYM allows users to be able to create alias for objects for easier access.

The following privileges apply commonly to developers:

  • CREATE TABLE

  • CREATE VIEW

  • CREATE SEQUENCE

  • CREATE PROCEDURE

  • CREATE TRIGGER

Database administrators (DBAs) commonly have these privileges:

  • CREATE ANY TABLE creates tables in any user’s schema.

  • DROP ANY TABLE drops tables from any user’s schema.

  • CREATE TABLESPACE creates tablespace storage areas.

  • ALTER USER changes user characteristics.

  • DROP USER . . . uh, drops a user.

  • ALTER SYSTEM changes system operation parameters.

  • GRANT ANY OBJECT allows grantee to manage any object privilege against any object in the database. Very powerful!

WITH ADMIN OPTION is another feature associated with system privileges. You can use this option when granting a system privilege to allow the user to grant the privilege to someone else. For example, say you’ve hired a new DBA with the username RPLEW.

You want the user MJAUST to connect to the database with the CREATE SESSION privilege, but you also want him to be able to grant that privilege to someone else.

To grant a system privilege WITH ADMIN OPTION, take these steps:

  1. Log in to SQL*Plus as SYSTEM.

  2. Type the following:

    <GRANT CREATE SESSION TO maust WITH ADMIN OPTION;>

    You see this:

    Grant succeeded.

    Now MAUST can administer CREATE SESSION as well.

If WITH ADMIN OPTION is revoked, all users given that privilege by that person retain the privileges. Act accordingly. It is not a cascading revoke like the WITH GRANT OPTION.