Database Management/Data Control Language

This lesson introduces data control language (DCL).

Objectives and Skills

Objectives and skills for this lesson include:

• Understand advanced SQL concepts
• Create data control language SQL queries using a database engine

Readings

1. Wikipedia: Data control language
2. Wikibooks: Structured Query Language/Managing Rights

Multimedia

1. YouTube: SQL-DCL

Activities

1. Select a database management system to use for this lesson. Options include Microsoft SQL Server, MySQL, Oracle Database, and PostgreSQL.
2. Select a graphic user interface to connect with the database management system. Tools for specific database systems include Microsoft SQL Server Management Studio, MySQL Workbench, Oracle SQL Developer, and Postgre GUI tools. Generic database administration tools that work with multiple database systems include DBeaver.
3. Use a Northwind script to create the Northwind database in your selected DBMS.
4. Complete the Roles, Users, Grant, Revoke, and Test activities below. Note: It may be easier to revoke all permissions first and then grant permissions you want roles to have.
5. Dump your completed database schema to an SQL script and review the results.

Microsoft SQL Server

1. For Linux and Windows. Download a free edition of Microsoft: SQL Server DBMS.
2. Download and install Microsoft: SQL Server Management Studio (Windows) or Microsoft: Azure Data Studio (Linux).
3. Complete one or more of the following tutorials using SQL Server and SQL Server Management Studio:
   • SQL Server Tutorial
   • Microsoft: Database Engine Tutorials
   • Guru99: MS SQL Server Tutorial
4. Review Microsoft: CREATE LOGIN and practice creating logins for your DBMS.
5. Review Microsoft: CREATE USER and practice adding users to a database.
6. Review Microsoft: CREATE ROLE and practice adding roles to a database.
7. Review Microsoft: Join a Role and practice adding users to roles.
8. Review Microsoft: GRANT and practice granting permissions to roles.
9. Review Microsoft: TABLE_PRIVILEGES and practice displaying table privileges.
10. Review Microsoft: sys.database_permissions and practice displaying database permissions.

MySQL

1. For Linux, MacOS, and Windows. Download and install the free and open MySQL Community Server DBMS.
2. Download and install the free and open MySQL Workbench.
3. Complete one or more of the following tutorials using MySQL and MySQL Workbench:
   • MySQL: Tutorial
   • MySQLTutorial.org
   • Guru99: Introduction to MySQL Workbench
4. Review MySQL Tutorial: MySQL SHOW GRANTS and practice creating users, roles, and privileges.
5. Review MySQL: INFORMATION_SCHEMA USER_PRIVILEGES and practice displaying MySQL roles and users.

Roles

Northwind

1. Review the Database Examples/Northwind/Employees database Employees table.
2. Create roles for 'Sales' and 'Manager'.

Pubs

1. Review the Database Examples/Pubs database Employees table and.Jobs table.
2. Create roles for 'Publisher' and 'Manager'.

Users

Northwind

1. Review the Database Examples/Northwind/Employees database Employees table.
2. Create users for each user and assign their default role. Andrew and Steven are managers. The other employees are sales representatives.

Pubs

1. Review the Database Examples/Pubs database Employees table and.Jobs table.
2. Create users for each user and assign their default role. Philip and Ann are managers. The other employees are publishers.

Grant

Northwind

1. Review the Database Examples/Northwind database.
2. Sales representatives must be able to select data from all tables.
3. Sales representatives must be able to insert data into Customers, Orders, and Order Details.
4. Sales representatives must be able to update Customers.
5. Managers must be able to select data from all tables.
6. Managers must be able to insert data into Customers, Orders, and Order Details.
7. Managers must be able to update Customers, Orders, and Order Details.

Pubs

1. Review the Database Examples/Pubs database.
2. Publishers must be able to select data from all tables.
3. Publishers must be able to insert data into Titles, Authors, and TitleAuthors.
4. Publishers must be able to update Titles.
5. Managers must be able to select data from all tables.
6. Managers must be able to insert data into Titles, Authors, and TitleAuthors.
7. Managers must be able to update Titles, Authors, and TitleAuthors.

Revoke

Northwind

1. Sales representatives must not be able to insert into any table except Customers, Orders, and Order Details.
2. Sales representatives must not be able to update any table except Customers.
3. Sales representatives must not be able to delete data from any table.
4. Managers must not be able to insert into any table except Customers, Orders, and Order Details.
5. Managers must not be able to update any table except Customers, Orders, and Order Details.
6. Managers must not be able to delete data from any table.

Pubs

1. Publishers must not be able to insert into any table except Titles, Authors, and TitleAuthors.
2. Publishers must not be able to update any table except Titles.
3. Publishers must not be able to delete data from any table.
4. Managers must not be able to insert into any table except Titles, Authors, and TitleAuthors.
5. Managers must not be able to update any table except Titles, Authors, and TitleAuthors.
6. Managers must not be able to delete data from any table.

Test

Northwind

1. Use the appropriate INFORMATION_SCHEMA table for your selected DBMS to display users and roles and SHOW GRANTS to display their permissions.
   • Show users and roles for your DBMS. Verify that you have created user accounts for each Northwind employee and roles for 'Sales' and 'Manager'.
   • Show permissions for the Northwind database. Verify that you have granted appropriate permissions to each role.
2. Log in to the DBMS as a sales representative.
   • Verify that you are able to insert customers, orders, and order details.
   • Verify that you are able to update customers.
   • Verify that you are unable to modify any other table.
   • Verify that you are unable to delete data from any table.
3. Log in to the DBMS as a manager.
   • Verify that you are able to insert customers, orders, and order details.
   • Verify that you are able to update customers, orders, and order details.
   • Verify that you are unable to modify any other table.
   • Verify that you are unable to delete data from any table.

Pubs

1. Use the appropriate INFORMATION_SCHEMA table for your selected DBMS to display users and roles and SHOW GRANTS to display their permissions.
   • Show users and roles for your DBMS. Verify that you have created user accounts for each Pubs employee and roles for 'Publishers' and 'Manager'.
   • Show permissions for the Pubs database. Verify that you have granted appropriate permissions to each role.
2. Log in to the DBMS as a publisher.
   • Verify that you are able to insert titles, authors, and titleauthors.
   • Verify that you are able to update titles.
   • Verify that you are unable to modify any other table.
   • Verify that you are unable to delete data from any table.
3. Log in to the DBMS as a manager.
   • Verify that you are able to insert titles, authors, and titleauthors.
   • Verify that you are able to update titles, authors, and titleauthors.
   • Verify that you are unable to modify any other table.
   • Verify that you are unable to delete data from any table.

Lesson Summary

• A data control language (DCL) is a syntax similar to a computer programming language used to control access (authorization) to data stored in a database. DCL statements include GRANT and REVOKE.
• The GRANT statement enables system administrators to grant privileges and roles, which can be granted to user accounts and roles.
• The REVOKE statement enables system administrators to revoke privileges and roles, which can be revoked from user accounts and roles.
• The format of the CREATE ROLE statement is:
  CREATE ROLE role
• The format of the CREATE USER statement is:
  CREATE USER user DEFAULT ROLE role
• The format of the GRANT statement is:
  GRANT action ON object TO user_or_role
• The format of the REVOKE statement is:
  REVOKE action ON object FROM user_or_role
• The INFORMATION_SCHEMA.USER_PRIVILEGES table provides global user and role privilege information for MySQL.
• The INFORMATION_SCHEMA.TABLE_PRIVILEGES table provides user and role privilege information for Microsoft SQL Server.
• The format of the SHOW GRANTS statement is
  SHOW GRANTS FOR user_or_role [USING role]

Key Terms

Data Control Language (DCL)

A syntax used to manage access (authorization) to data stored in a database.

See Also

• Learning SQL Server/SSMS
• Create a Database- SQL Server
• Wikibooks: MySQL
• W3Schools: SQL Tutorial
• MySQL: Roles
• SQL Server: Grant/Revoke Privileges

References

← Data Definition Language

Database Management

Normalization →