Difference between Role and Privilege in Oracle Database
Privilege can be compared with authentication. To access any website we should have a require privilege to log in. In a similar way, to access database the first line of security that is to be passed is connecting to database. For example, user can only be allowed to connect to a database if CREATE SESSION privilege is assigned.
Role can be compared with authorization in a website.User of a website can only view the contents for which he is authorized to.The person who is the owner of the website gets the ADMIN role to publish the content. This user can in return assign required roles to the other user. The same idea applies to database.For example, in case of database the user with ADMIN role can assign roles to the other user.
Privilege can be split into system privilege and object privilege and tables,indexes,views,procedures,triggers in a database are few of the examples of an object.
System privileges are mostly assigned to user to manage objects or sessions in database.For example, to create table in database user should have CREATE TABLE privilege.Similarly to create index in database user should have CREATE INDEX privilege. These privileges allow user to manage the objects of the resources.User can also be allowed to alter or drop objects in database, which they do not own, by assigning required system privileges.
Object privilege are assigned to change,execute or access the objects in the database. For example, user with select privilege to a table can be allowed to access the content of the table even if he is not the owner of the table. Thus, various users of the database can be allowed to access the contents of the objects.Similarly, a user can be assigned to execute procedure that belongs to a different user.
Some DBA’s have read only and execute access to few of the objects of the database while some some have access to schedule backup for the database.These users are assigned required roles and privileges to perform their daily activities.
Role can have below combination of roles and privileges.
2)Role+privilege(can be system or object or both)
Thus, the level of complexity in a role can always be increased by assigning more privileges and roles to it.
Roles and privileges can be assigned to a user at database level and at session level.GRANT statement is used to assign roles and privileges to a user.
The other options that are available while assigning roles and privileges are GRANT option or ADMIN option.But discussion on this is topic is saved for another day as it is equally complex.
Companies with high level of compliance generally expect the user(in this case the technical team members) to access the database using the roles and privileges assigned to them.Privileges and roles can be audited and their audit records can be maintained in audit trail,within database, or in a separate software called Oracle Audit Vault to ensure zero compliance issues. The queries that are executed by the user to access the records are also maintained in these trails.This helps in conducting the root cause analysis of the compliance issues that are generally observed in companies.
The article would not be concluded if I do not mention about SYSDBA privilege. SYSDBA privilege is a super privilege that can allow user to access all resources in the database. This privilege also allows user to shut and start database. Generally the information of privileges and roles are stored in data dictionary but information about users with this privilege is stored in password file.