Granting roles and privileges in Oracle DBA

Authorization is how the Oracle database discriminates between the administrators, the different users, and their levels of access. The access to the data in the databases is controlled by the database administrators by granting the users certain rights, i.e. the privileges or groups of them, i.e. the roles.

Privileges in Oracle DBA - Table of Contents

What is a privilege in Oracle DBA? Granting system privileges and roles Granting a role to a user Identified by clause With the admin option clause Granting object privileges With the grant option clause

Granting roles and privileges in Oracle DBA

There are two primary processes included in authorization:

Permitting only a few particular users to be able to access, alter, or process the data applying limitations on user access and actions.

Placing limitations on the users or removed might apply to tables, rows, and schemas. They might even apply to resources like connect, CPU, or even idle times.

Many tasks are involved in the administration of user roles, privileges, and profiles. Each Oracle database would have a list of valid database users and a user must run a database application to access the database and connect to a database instance.

If you would like to Enrich your career with a Database Administrator(DBA) and get Oracle certified professional, then visit Mindmajix - A Global online training platform:“Oracle DBA Online Course”  This course will help you to achieve excellence in this domain.

What is a privilege in Oracle DBA?

• A privilege is a right allowing the user to run some particular types of SQL commands or access the object of another user. Some of the privileges that are given to users include the rights like connecting to a database or creating a table. There could also be rights to select the rows from the users of another table or execute the stored procedure of another user.
• Privileges are granted to users in order for them to accomplish the tasks needed for different jobs. Only those privileges should be granted to the user that would allow them to perform the necessary task. Security could be compromised if excessive or unnecessary privileges are granted to a user. Users can receive privileges in two of the following ways:
• Privileges can be granted to a user explicitly. Privileges can also be granted to a certain role, i.e. a named collection of privileges. In this way, a certain role can be granted to one user or more than one.
• Roles allow better and easier management of privileges. That is why, the general way to go should be to grant privileges to the roles, but not the individual users. This would make for a more sophisticated database with better controls and management.
• Below ones are six basic classes of privileges, though some of them have subcategories as well:
• • System Privileges
  • Schema Object Privileges
  • Procedure Privileges
  • View Privileges
  • Table Privileges
  • Type Privileges
• System Privileges - The system privileges offer the rights to perform actions on schema objects or some other actions. There are lots of different system privileges that can be granted under this category.
• Schema Object Privileges - The schema object privilege offers permission to execute a certain action on a specific schema object. The different object privileges are available for the different types of schema objects. It offers the privilege to delete roles. The schema objects that do not have associated object privileges are controlled through system privileges.
• Table Privileges - Security at the Data Definition Language (DDL) or Data Manipulation Language (DML) level is enabled by the schema object privileges for tables.
• View Privileges - A view is basically a presentation of the data accessed from one or more tables. The structure of the underlying tables is seen through a view. No actual data is contained in a view. It merely shows the data derived from the tables on which it is based. The data in a view can be updated or deleted and new data can be inserted. The tables are directly altered by the operations on which the views are based. The triggers and integrity constants of the base tables are taken into consideration.
• Procedure Privileges - The only schema object privilege for procedures is EXECUTE. Standalone procedures, packages, and functions are all included in these procedures. This privilege is to be granted only to those users who have to execute a procedure or compile another procedure that needs the desired procedure. For the creation and management of secure and effective usage of the procedure privileges, you need to know about the security domains.
• Type Privileges - The privileges for methods, types, and objects are defined by system privileges for named types, method execution model, and others.

Do You Want Know More About Oracle DBA Then, Check out: "Oracle DBA Tutorial for Beginners with Examples"

Granting system privileges and roles

The system privilege offers users the right to execute certain actions. It could be the actions of schema objects. The privileges that are needed to make tablespaces and delete rows of tables in the databases are all system privileges. More than 100 different system privileges are available.

How to grant and revoke the system privileges?

System privileges can be granted and revoked from users or roles. If system privileges are granted to roles, these roles can be used to manage the system roles. Roles offer the privileges the chance to be selectively available. In general, the system privileges should be granted only to the administrative team and the application developers. The end-users would not require these associated capabilities and must not be vested with them.

To revoke or grant the system privilege to the roles or users, one among the following can be used:

• The SQL REVOKE and GRANT statements
• The Oracle Enterprise Manager 10g Database Control

Who has the power to revoke or grant the system privileges?

There are only two kinds of users who are vested with the power of granting system privileges. They can grant them to the other users and even revoke them. They are:

• The users having a system privilege along with ADMIN option (or)
• The users having the GRANT ANY PRIVILEGE

Learn end-to-end Oracle DBA concepts through the Oracle DBA Course in Hyderabad to take your career to a whole new level!

Granting a role to a user in Oracle

1. The GRANT command is used to grant the system privileges both to roles and users. The statement can even be used to grant roles to both roles and users. The privileges and roles could be global, external, or local. It can even offer object privileges for certain objects to roles, users, or the PUBLIC.
2. Database users can be authorized in some other ways as well, where there would be no need to use the database or the GRANT statement. Most of the privileges in the Oracle database are often granted through the Java packages and Oracle PL/SQL. Some of the operating systems offer facilities to let the users grant roles to the users of Oracle Database with the OS_ROLES initialization parameter.
3. If you are granting roles to these users through the operating system, you would not be able to use the GRANT command in order to grant the roles to these users. However, it is possible to use the GRANT statement if you want to grant any system privilege to a user or system privilege or roles are to be granted to some other roles.

• Prerequisites

If you want to grant the system privileges, they must be granted with the help of the ADMIN OPTION. You could also have GRANT ANY PRIVILEGE, which is a system privilege. In the same way, if you need to grant roles to a user, the role must have been offered the required ADMIN OPTION, if not the system privilege -  GRANT ANY ROLE.

The alternative is to create the role yourself. Granting any object privilege is possible only if the object is owned by you. Otherwise, the object owner must grant you the necessary object privileges and also GRANT OPTION. Another way out is for you to be granted with GRANT ANY OBJECT PRIVILEGE, which is another system privilege. 

You would be able to grant the object privilege through the GRANT ANY OBJECT PRIVILEGE provided that the owner of the object had the power to grant the same privilege. GRANTOR column in the DBA_TAB_PRIVS would display the owner of the object and not the person who had offered the GRANT command.

• System privileges

Oracle RDBMS has added a few clauses in its semantics for better distribution of the privileges among the users and for better control of the administrators. A few clauses are important to grant the system privileges to users and roles. These clauses are crucial to the GRANT statement and help the database administrator or the users with roles and privileges exercise them with proper authentication.

Related Blog: Revoking user privileges and roles

• Granting system privileges

It is needed to specify the privilege that you would like to grant. If a user is granted a privilege, the privilege is added to the privilege domain of the user by the database. The user would immediately be able to exercise that privilege. In the same way, if a role is granted a privilege, the privilege is added to the privilege domain section of that role by the database. If the users who had been granted the role have enabled it, the privilege can be exercised immediately. The role needs to be enabled, following which immediate exercise is allowed.

If a privilege is granted to PUBLIC, the privilege would be added to privilege domains each of the users possess and all the users would be immediately able to exercise the privilege. ALL PRIVILEGES is the shortcut that can be used to grant all system privileges offered by the Oracle Database. However, it does not have the privilege - SELECT ANY DICTIONARY.

• Granting system privileges to users:

GRANT CREATE SESSION
   TO hr;

• Granting system privileges to roles:

GRANT

         CREATE ANY MATERIALIZED VIEW
, ALTER ANY MATERIALIZED VIEW
   , DROP ANY MATERIALIZED VIEW
   , QUERY REWRITE
   , GLOBAL QUERY REWRITE
   TO dw_manager
   WITH ADMIN OPTION;

The dw_manager role now has a privileged domain that contains system privileges that are related to materialized views.

Related Article: Oracle DBA Interview Questions

• Role

In a similar way to the privileges, it is necessary to mention the role to be granted. The roles predefined in the Oracle Database as well as the user-defined roles can be granted. When a role is granted to some user, the database would make the role accessible for that particular user. It would make the user immediately able to enforce the privileges that a role has in its privilege domain.

• Granting a role to a user: 

If a role is granted to some other role, the privilege domain that is the property of the role would be added to that privilege domain that is used by the grantee role. Thus, the users having the privileges of grantee role would be able to exercise the roles that are in the privilege domain of the granted role, along with the pre-existing ones of the grantee role. If a role is granted to PUBLIC, the role would be accessible to all the users. All the users would be immediately able to use the role as well as enforce the privileges that are present in the privilege domain.

• Granting role to another role:

GRANT warehouse_user
TO dw_manager;

The dw_manager role now has all of the privileges that are in the privilege domain of the warehouse_user roles.

Identified by Clause

IDENTIFIED BY statement+1 finds use when a user is specifically identified by a password or a non-existing user is created. The clause would not be credible if the one who has been granted the role is a role itself or PUBLIC. The database would create a user with a given password, roles, and privileges provided in the clauses if the users provided in the grantee_clause are non-existent.

Related Article: Managing The Redo Log - Oracle DBA

With Admin Option

The grantee must be specified with the clause - ‘WITH ADMIN OPTION’ to offer a few rights. They are stated below.

• Grant this role to other roles or users, if the roles are not GLOBAL roles
• Revoke this role from other roles or users
• Alter the roles so that the authorization required for access can be modified
• Drop the roles altogether

If a user grants a role or system privilege to another user and do not specify WITH ADMIN OPTION, but however, the role or privilege is granted to that user subsequently with the ADMIN OPTION, the user would have ADMIN OPTION in that role or privilege. If you would like to revoke just the ADMIN option from users on a certain role or system privilege, you would need to revoke the role or privilege from those users altogether first. You would then need to grant that role or privilege to the users now again without using ADMIN OPTION.

Global privileges such as system privileges or roles are often granted WITH ADMIN OPTION. We also employ WITH GRANT OPTION for the table-specific privileges. Revoking a grant having WITH GRANT would cascade the privileges that had been offered by the original privileged user. However, if someone having WITH ADMIN OPTION is revoked, only the personal privileges would be revoked and all the granted users would remain intact.

When the system privileges pass on to others with a WITH ADMIN OPTION, there would be no cascade when system privileges are revoked from original users. If system privileges need to be revoked, direct action must be taken. This is quite unlike object privileges with WITH GRANT OPTION which is passed to others. Along with the privileges of the grantor, object privileges would be revoked as well. Only the object privileges would cascade when revoked, but the system privileges would not.

Sometimes, there is a need to pass on privileges to the users and also have them, in turn, be capable of granting privileges to the other users. WITH ADMIN OPTION is included in such a case in GRANT command. The user is allowed to pass on privileges to other users when this keyword is used. So this option is prohibited by many companies and the others make sure that all the user IDs are proper.

Related Article: Oracle PL SQL Interview Questions

Granting a Role with the Admin Option:

GRANT dw_manager
   TO sh
   WITH ADMIN OPTION;

Now she would be able to perform a few operations with the dw_manager:

granting and revoking the role to and from other users enabling the role and exercising any privileges that are present in the privilege domain of the role, and
dropping the role

Granting object privileges

The object privileges that are to be granted under the GRANT command must be mentioned.  Let us look at how the object privileges are to be used, the restrictions on them, how to grant privileges to specific columns. The WITH GRANT OPTION would allow the grantee user to further pass on the object privileges.

• on_object_clause

The purpose of the on_object_clause is to identify the object for which privileges are being granted. The objects of resource schema and directory schema would be identified separately as they do not reside in the same namespaces. However, if the grant is made just because the user has the GRANT ANY OBJECT PRIVILEGE and he does not own the object, then from the grant’s effect, it would reflect that the user is following the wishes of the owner. The data dictionary *_TAB_PRIVS would show that the grant had been made by the object owner.

With Grant Option

The WITH GRANT OPTION is to be specified if the user would like the grantee to be able to pass object privileges on to the other roles or users. The Grant Option would allow you to offer the user you are assigning the privilege to pass it on to others. Only if WITH GRANT OPTION has not been mentioned in the statement, the schema that owns the object can grant such privileges. An example that describes  The Grant Option is as follows:

GRANT SELECT ON
   emp
TO
   ben
WITH GRANT OPTION;

• Restrictions on using object privileges

A privilege must not be appearing among the granted privileges more than a single time. If you would need to grant each and every privilege available for an object, the keyword to be used is ALL. The user in possession of the schema that contains the object would have all the privileges on that object with GRANT OPTION. The PRIVILEGES keyword is provided just to offer semantic clarity, so it is actually optional.

• How to grant privileges to a specific column?

The view or table column is to be specified on which the privileges should be granted. If the columns are not specified, the grantee would then have the specified privileges on each of the columns in a view or table.

All these different levels of user privileges and authorization help sound management of the database. In the real world, there are different levels of employees in a company and not everyone should have the right to access or modify the content.

On top of that, there should be special privileges and powers that are to be vested only with the management or administration. The Oracle Database offers the concept of Authorization, which makes it possible to put this into effect. Authorization helps to put in place the mechanisms that are needed to keep checks on the users, using limitations individually or in groups.

Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!

Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!