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.
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.
[Related Article: Oracle DBA Tutorial for Beginners with Examples]
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.
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:
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:
Related Blog: Network Services in Oracle Database
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.
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
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.
GRANT CREATE SESSION TO hr;
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]
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.
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.
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 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.
The grantee must be specified with the clause - ‘WITH ADMIN OPTION’ to offer a few rights. They are stated below.
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]
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
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.
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.
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;
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.
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.
Technical Content Writer