User Privileges in Oracle Database Introduction

By Manish Sharma

User Privileges.

As we know that every user in oracle database needs some rights to perform any task. These tasks can be anything for example to execute any SQL statement or to access the objects owned by any other user or to run any PL/SQL package and many more.

So to define a user privilege in oracle database we can say.

  A user privilege is the right to run a particular type of SQL statement, or the right to access an object that belongs to another user, run a PL/SQL package, and so on.

There are two types of user privileges in Oracle Database.

  1. System Privilege and
  2. Object Privilege

System Privilege

System privileges are some powerful special rights given to a user to perform standard administrator tasks in the database. These tasks can be any action on any schema objects for example create and drop a user or tablespace, flashback or lock any table and export as well as import the database and many more.

As I mentioned before that System privileges are quite powerful set of rights therefore when in wrong hands they could result in catastrophic events. Hence these should be granted very responsibly and only when absolutely necessary. As they say-

“With great power comes great responsibility”

Object Privilege

Object privileges are the rights given to a user to access and perform some actions on the database objects or the objects owned by some other user in the database.

By objects I mean tables, views, sequences, procedures etc. and by Action I mean Alter, Delete, Execute, Select, Update, References, Insert and Index.

Object privileges vary from object to object and an owner of the object has all the privileges on it.

How to Grant and Revoke the use privileges

To assign and deny the user privilege we have two Data control language (DCL) statements in Oracle database.

  1. Grant and
  2. Revoke

Who Can Grant or Revoke System Privileges?

  1. The most powerful administrative user in the database sys can grant and revoke any privilege from any user in the database.
  2. A user with the system privilege GRANT ANY PRIVILEGE and
  3. Any user who were granted a specific system privilege with the ADMIN OPTION
All of these can grant and revoke any privilege.

In oracle database we have three options which we can use along with the Grant statement and add some flexibility to this privilege management.

These 3 options are:

  1. With Admin Option
  2. With Grant Option
  3. With Hierarchy Option

With Admin Option

With Admin Option enables the grantee to –
  • Grant the privilege or role to another user or role, unless the role is a GLOBAL role
  • Revoke the privilege or role from another user or role
  • Alter the privilege or role to change the authorization needed to access it
  • Drop the privilege or role
If you grant a system privilege or role to a user without specifying WITH ADMIN OPTION, and then subsequently grant the privilege or role to the user WITH ADMIN OPTION, then the user has the ADMIN OPTION on the privilege or role.

To revoke the ADMIN OPTION on a system privilege or role from a user, you must revoke the privilege or role from the user altogether and then grant the privilege or role to the user without the ADMIN OPTION.

With Grant Option

WITH GRANT OPTION enables the grantee the power of granting object privileges to various other users.

Tip: Also it is important to remember that WITH GRANT OPTION only assists in granting a privilege to PUBLIC or user and not to a role.

With Hierarchy Option

WITH HIRARCHY OPTION enables the grantee to grant particularly specified object privilege on every sub-objects of an object for example sub-views that are created within a view. This particular clause works only when used in combination with the SELECT object privilege.

Restrictions on Granting System Privileges and Roles

Privileges and roles are subject to the following restrictions:
  • A privilege or role cannot appear more than once in the list of privileges and roles to be granted.
  • You cannot grant a role to itself.
  • You cannot grant a role IDENTIFIED GLOBALLY to anything.
  • You cannot grant a role IDENTIFIED EXTERNALLY to a global user or global role.
  • You cannot grant roles circularly. For example, if you grant the role banker to the role teller, then you cannot subsequently grant teller to banker.

Restriction on Object Privileges

A privilege cannot appear more than once in the list of privileges to be granted.

Security guidelines for user privileges

  1. Do not provide database users or roles more privileges than are necessary. (If possible, grant privileges to roles, not users.) In other words, the principle of least privilege is that users be given only those privileges that are actually required to efficiently perform their jobs.
  2. To implement this principle, restrict the following as much as possible:
    • The number of SYSTEM and OBJECT privileges granted to database users.
    • The number of people who are allowed to make SYS-privileged connections to the database.
    • The number of users who are granted the ANY privileges, such as the DROP ANY TABLE privilege. For example, there is generally no need to grant CREATE ANY TABLE privileges to a non-DBA-privileged user.
    • number of users who are allowed to perform actions that create, modify, or drop database objects, such as the TRUNCATE TABLE, DELETE TABLE, DROP TABLE statements, and so on.
  3. Restrict the CREATE ANY JOB, BECOME USER, EXP_FULL_DATABASE, and IMP_FULL_DATABASE privileges. These are powerful security-related privileges. Only grant these privileges to users who need them.
  4. Do not allow non-administrative users access to objects owned by the SYS schema.
  5. Lock and expire default (predefined) user accounts.
  6. Revoke access to the following:
    • The SYS.USER_HISTORY$ table from all users except SYS and DBA accounts
    • The RESOURCE role from typical application accounts
    • The CONNECT role from typical application accounts
    • The DBA role from users who do not need this role
  7. Grant privileges only to roles.
  8. Granting privileges to roles and not individual users makes the management and tracking of privileges much easier.
  9. Limit the proxy account (for proxy authorization) privileges to CREATE SESSION only.
  10. Discourage users from using the NOLOGGING clause in SQL statements.
  11. In some SQL statements, the user has the option of specifying the NOLOGGING clause, which indicates that the database operation is not logged in the online redo log file. Even though the user specifies the clause, a redo record is still written to the online redo log file. However, there is no data associated with this record. Because of this, using NOLOGGING has the potential for malicious code to be entered can be accomplished without an audit trail.

I hope this article clears your concept of User Privileges. Please do share it on your social networking and help me reach out to more people. Thanks and have a great day!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

NO SQL Script used in Video and in this article
Presentation used in Video and in this article

I have used copy cloud to share these resourses. You can also join copy cloud and get 15GB free cloud storage for lifetime. If you will use this referal link You will get 5GB extra free cloud storage means total 20GB free cloud storage.

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn