Basics of Object Privileges and Management in Oracle 12c - dummies

Basics of Object Privileges and Management in Oracle 12c

By Chris Ruel, Michael Wessler

Deciding who privileges for databases is something every database administrator (DBA) has to determine. One type of privilege in Oracle 12c is object privileges which controls data access and modification.

Basics of object privileges in Oracle 12c

You can grant only eight object privileges:

  • SELECT lets the recipient select rows from tables

  • INSERT lets the recipient insert rows into tables.

  • UPDATE lets the recipient change existing rows in tables.

  • DELETE lets the recipient remove existing rows from tables.

  • REFERENCES lets a user create a view on, or a foreign key to, another user’s table.

  • INDEX lets one user create an index on another user’s table.

  • ALTER lets one user change or add to the structure of another user’s table.

  • EXECUTE lets the recipient run procedures owned by another user.

Keep these privilege tidbits in mind:

  • When you own an object, you automatically have all the privileges on that object. In other words, you don’t have to be granted SELECT on your own table.

  • Object privileges cannot be revoked from the owner of an object.

  • Whatever schema owns the object ultimately controls that object’s privileges.

  • Without express permission, no one else can manage the object privileges of said object — well, no one except a user who might have the system privilege GRANT ANY OBJECT (usually reserved for DBAs).

  • Object privilege cannot be revoked by anyone but the person who granted it except for someone with the GRANT ANY OBJECT privilege. Not even the owner can revoke a privilege on her own object unless she was the grantor.

How to manage object privileges in Oracle 12c

In the following steps, the users MAGGIE, JASON, and MATT work in a database that contains recipes. This example uses object privileges to allow them to view and add more recipes.

  1. Maggie logs in.

  2. Maggie types the following:

    <GRANT SELECT ON vegetarian_recipes TO jason;>

    This lets user MAGGIE allow JASON to select from her VEGETARIAN_RECIPES table. She sees this:

Grant succeeded.

Similar to WITH ADMIN OPTION of system privileges, object privileges have something called WITH GRANT OPTION.

  • MAGGIE can allow JASON to be able to INSERT into her table and allow JASON to pass on that privilege:

    <GRANT SELECT ON vegetarian_recipes TO jason WITH GRANT OPTION;>
  • JASON can pass on that INSERT privilege to MATT:

    <GRANT SELECT ON maggie.vegetarian_recipes TO matt;>
  • MAGGIE cannot revoke the INSERT privilege from MATT. She has to ask JASON to do so.

  • If JASON refuses to revoke INSERT privileges for MATT, MAGGIE can revoke the privilege from JASON and, in turn, revoke it from MATT. It’s called a cascading revoke. Note that this is different from system privileges.

  • MAGGIE can revoke the INSERT privilege from JASON and in the meantime automatically revoke them from MATT:

    <REVOKE INSERT ON vegetarian_recipes FROM jason;>

    She sees this:

    Revoke succeeded.

If a user wants to see what object privilege they have given out, she can query the view USER_TAB_PRIVS.

For example, MAGGIE can see what privileges JASON has left on her objects:


She sees something like this:

---------- ---------- ------------------ ---------- ----------