EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials DB2 grant
Secondary Sidebar
DB2 Tutorial
  • DB2 Tutorial
    • What is DB2?
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE

DB2 grant

DB2 grant

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The syntax of the grant privilege in DB2 is as shown below –

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (85,992 ratings)

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:

DB2 grant output 1

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 –

DB2 grant output 2.5

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 –

DB2 grant output 2

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 –

  1. DB2 TIMESTAMPDIFF
  2. DB2 Translate
  3. DB2 Data Types
  4. DB2 Create Table
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more