Grant, Revoke

When working with databases, you constantly need to add, edit, and delete something, i.e. work with data. Obviously, each user should have a different level of access to different information. For example, a database administrator has to have access to all user accounts, but the users themselves should not have such access (common users should only have access to their own accounts). In order to differentiate the rights of different users or user groups to different objects in the SQL language, there are such commands as GRANT and REVOKE.

USERS and ROLES

The subset of the SQL language, which is responsible for user management, namely, the grant or withdrawal of the rights, is the DCL language or Data Control Language.

It is important to understand that rights can be granted either to a specific user or to a group of users performing a specific role in the system. In other words, a role is a certain group of users, which is characterized by the same functionality, for example, manager role.

GRANT

Let's move on to granting the rights to users. In the SQL language, the command GRANT is used, and it looks like this:

GRANT privilege_list ON table TO username;
GRANT privilege_list ON table TO role_name;

Let's look at the examples of granting privileges to a user.

GRANT ALTER ON my_table TO Ben;

In this case, the user Ben will be granted the right to alter the my_table table. Consider another example:

GRANT INSERT, SELECT ON my_table TO Ben;

Here we have given the user Ben view and insert rights to the my_table table. In this example, you can see two new possibilities at once:

• granting several rights in one command (we list them separated by commas);
• granting rights to a specific table (using the ON keyword).

Now, let's take a look at working with roles. In the previous examples, you could've noticed one detail: for one specific user, a certain set of rights was granted. Consequently, if the system has a large number of users who need to be granted a large number of specific rights, then a large number of lines of code will also need to be written. To simplify this task, you can use the mechanism of roles.

Suppose we have a table of clients, and we need to grant access to managers for reading, writing, updating, and deleting data in this table. Managers can be removed from the system and new ones can be added. In order not to write the same code all the time, you can create a manager role. The following example demonstrates how to do it.

GRANT SELECT, UPDATE, DELETE, INSERT ON Clients TO Manager;
GRANT Manager TO Ben;
GRANT Manager TO Bob;

Here, the "Manager" role has been granted select, insert, delete, and edit rights to the data in the "Clients" table. The manager role was then given to users Ben and Bob.

Now, in order to grant rights to the customer table, it is enough to simply assign the manager role to a new user. The amount of code has been reduced and there is no need to write the same code several times.

In addition, it is possible to grant all rights to the user. To do this, use the ALL keyword. For example:

GRANT ALL ON my_table TO Ben;

REVOKE

Since the language has the ability to grant rights to users, it is also possible to take away these rights from a user. For this purpose, the SQL language uses the REVOKE operator.

The REVOKE operator has the following syntax:

REVOKE privilege_list ON table FROM username;
REVOKE privilege_list ON table FROM role_name;

Consider examples similar to those demonstrated with the GRANT operator.

REVOKE INSERT, SELECT ON my_table FROM Ben;

Here we've taken away the rights to insert and view my_table from user Ben.

REVOKE Manager FROM Ben;
REVOKE Manager FROM Bob;

In this example, users Ben and Bob lost the manager role.

Conclusion

Thus, we can come to the conclusion that the GRANT and REVOKE operations are a fairly simple and at the same time functional tool for protecting data and delegating user roles. It is important to understand what rights you are offering or taking away and from which user. If you grant incorrect rights to objects, the user might even delete the entire database and data from it.