Privilege and Roles in DBMS
Confidentiality, integrity, and availability are the stamps of database security. Authorization is the allowance to the user or process to access the set of objects. The type of access granted can be any like, read-only, read, and write. Privilege means different Data Manipulation Language(DML) operations which can be performed by the user on data like INSERT, UPDATE, SELECT and DELETE, etc.
There are two methods by which access control is performed is done by using the following.
Let’s discuss one by one.
The authority or permission to access a named object as advised manner, for example, permission to access a table. Privileges can allow permitting a particular user to connect to the database. In, other words privileges are the allowance to the database by the database object.
- Database privileges —
A privilege is permission to execute one particular type of SQL statement or access a second persons’ object. Database privilege controls the use of computing resources. Database privilege does not apply to the Database administrator of the database.
- System privileges —
A system privilege is the right to perform an activity on a specific type of object. for example, the privilege to delete rows of any table in a database is system privilege. There are a total of 60 different system privileges. System privileges allow users to CREATE, ALTER, or DROP the database objects.
- Object privilege —
An object privilege is a privilege to perform a specific action on a particular table, function, or package. For example, the right to delete rows from a table is an object privilege. For example, let us consider a row of table GEEKSFORGEEKS that contains the name of the employee who is no longer a part of the organization, then deleting that row is considered as an object privilege. Object privilege allows the user to INSERT, DELETE, UPDATE, or SELECT the data in the database object.
A role is a mechanism that can be used to allow authorization. A person or a group of people can be allowed a role or group of roles. By many roles, the head can manage access privileges very easily. The roles are provided by the database management system for easy and managed or controlled privilege management.
The following are the properties of the roles which allow easy privilege management inside a database:
- Reduced privilege administration —
The user can grant the privilege for a group of users who are related instead of granting the same set of privileges to the users explicitly.
- Dynamic privilege management —
If the privilege of the group changes then, only the right of role needs to be changed.
- Application-specific security —
The user can also protect the use of a role by using a password. Applications can be created to allow a role when entering the correct and best password. Users are not allowed the role if they do not know about the password.