Updated March 13, 2023
Introduction to DB2 grant
DB2 grant is a statement available in DB2 that can assign privileges to perform certain operations on certain entities or objects present in the database, such as tables, views, or any nicknames, i.e. aliases. Further, we can also mention if the user to which the privileges are assigned will have the privilege to grant the assigned privilege to some other user or group by making the use of the grant option. In this article, we will study the syntax of the grant statement in DB2 and learn about how it can be used in the database and its implementation with the help of a number of examples.
Syntax
The syntax of the grant privilege in DB2 is as shown below –
GRANT PRIVILEGES (ALL)
ALTER / CONTROL / DELETE / INDEX / INSERT
REFERENCES
(column name(s))
SELECT / UPDATE
Column name(s)
TABLE
ON
tablename / viewname
TO
Authorization name
USER
GROUP
PUBLIC
WITH GRANT OPTION
GRANT PRIVILEGES – It is compulsory to mention these keywords for using the grant statement in DB2.
(ALL) – This is the optional clause that can be used to specify whether we have to assign the privilege of performing all or only certain specified operations using this grant statement.
If all is mentioned, all the privileges except the control privilege are assigned on the table name or view name, whatever is specified in the on clause. This will happen only when the name of the authorization mentioned in the statement has ACCESSCTRL authority or SECADM authority with him or has the control privilege on the table or view whatever is mentioned in the entity.
If the authorized user does not satisfy any of the two mentioned conditions, then all those privileges with the authorization user on the specified entity that is table or view.
If we have not used this ALL-in statement, we should specify the privileges to be granted.
ALTER / CONTROL / DELETE / INDEX / INSERT – Anyone or many of the mentioned operations can be specified in the DB2, telling about what type of operations the user or group will be allowed to perform after executing this grant statement.
Types of privileges
The different types of access and operable permissions assigned to the user or group are mentioned below.
| Permission or privilege | Details |
| SELECT | Permission to do SELECT query statements on any given table |
| INSERT | Permission to do INSERT query statements on any given table |
| UPDATE | Permission to do UPDATE query statements on any given table |
| DELETE | Permission to do DELETE query statements on any given table |
| REFERENCES | Permission for creating a constraint in SQL on the given table to refer to other entities in the database. |
| ALTER | Permission to do ALTER TABLE query statements which can modify the definition of the table. |
| ALL | This allows the user to give the permissions to perform SELECT, INSERT, UPDATE, DELETE, and REFERENCES query statements on any given table. These are not all the permissions but ANSI-92 permissions. |
SELECT / UPDATE – We can specify the entity or name of the table in the table name or also a view by using the view name after the select or update clause.
Column name(s) – We need to specify the column names on which this privilege is allowed after selecting or updating the statement.
Authorization name – This can be either a user, group, or role, which is generally used to specify to who we need to assign the specified privilege to in the grant statement.
WITH GRANT OPTION – This is again the optional clause that can be used if we have to tell that the permission to assign the same privilege assigned using this grant statement is allowed to the user, group or public if mentioned. If we are not mentioning grant privilege here, then the user or group to whom we have assigned the privilege won’t be able to assign the same privilege to any other user or group.
Examples of DB2 grant
Let us understand how we can make use of the grant statement and learn about its implementation in the DB2 database with the help of certain examples.
Example #1
Let us create one table named employee_details using the following query statement.
CREATE TABLE employee_details
( employee_id NUMBER(6)
, f_name VARCHAR2(20)
, l_name VARCHAR2(25)
, email_id VARCHAR2(40)
, mobile_number VARCHAR2(20)
, joining_date DATE
, store_id VARCHAR2(20)
, salary NUMBER(8,2)
, appointee_id NUMBER(6)
, department_id NUMBER(4)
)
Output:
We have also inserted some of the rows in the table, and after we query on the employee_details table for retrieving all the data using the select query statement shown below, we get the result set containing all rows in it.
SELECT * FROM [employee_details]
The output of the execution of the above query statement is as shown below –
We have one user who does not have access to the employee tables table. Hence, when that user tries to do the same select query as shown below, he gets the error saying he does not have the grant permissions on the table.
Consider that table named employee_details in our database on which we have to publicly assign the permissions on this table. We can do this by using the following query statement –
GRANT ALL ON employee_details TO PUBLIC;
The execution of the above query statement gives out the following output –
Now. If the same user goes for accessing the data of the employee details table, he can see all the rows because we have assigned all the privileges on the employee details table to the public, which means that any user can use this table. The user gets the following output after doing the select query on the table.
SELECT * FROM [employee_details]
Example #2
Assigning privileges to user and group along with permission to extend it.
Consider that we have one table named customers_details in our database, which is created using the following statement.
CREATE TABLE customers_details
( customer_id NUMBER(6)
, f_name VARCHAR2(20)
, l_name VARCHAR2(25)
, email_id VARCHAR2(100)
, mobile_number VARCHAR2(20)
, purchase_date DATE
, store_id VARCHAR2(20)
, bill_amount NUMBER(8,2)
, salesman_id NUMBER(6)
, department_id NUMBER(4)
)
we have to assign the insert and select permissions on this table to user Payal and the group AM04. In this case, both the group and the user will be granted select and insert privileges, and also two rows will be added in SYSCAT.TABAUTH catalog view. We can do this by using the following query statement –
GRANT INSERT, SELECT ON customers_details
TO GROUP AM04, USER payal
WITH GRANT OPTION
The execution of the above query statement gives out the following output –
Now both Payal user and AM04 group can execute the following select instruction on the customers_details table –
SELECT * FROM customers_details;
The execution of the above query gives the following output –
Conclusion
We can make use of the GRANT statement in DB2 to assign privileges of different operations on multiple entities like tables and views in the database. We can also control whether the assigned privileges to that user or group can be further extended by them and assigned to other users or groups using the grant option. Finally, we can also assign the privileges on the nickname of the existing entity of the database.
Recommended Articles
This is a guide to the DB2 grant. Here we discuss how it can be used in the database along with its implementation with the help of a number of examples. You may also have a look at the following articles to learn more –
