Jump to content

Database Management/Data Control Language

Add links
From Wikiversity
Database Management
  1. Introduction
  2. Query by Example
  3. SQL
  4. Advanced SQL
  5. Data Definition Language
  6. Data Control Language
  7. Normalization
  8. E-R Diagrams
  9. Database Design
  10. Database Functions
  11. Database Administration
  12. NoSQL
  13. ODBC

This lesson introduces data control language (DCL).

Objectives and Skills

[edit | edit source]

Objectives and skills for this lesson include:

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

Readings

[edit | edit source]
  1. Wikipedia: Data control language
  2. Wikibooks: Structured Query Language/Managing Rights

Multimedia

[edit | edit source]
  1. YouTube: SQL-DCL

Activities

[edit | edit source]
  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

[edit | edit source]
  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:
  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

[edit | edit source]
  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:
  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

[edit | edit source]

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

[edit | edit source]

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

[edit | edit source]

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

[edit | edit source]

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

[edit | edit source]

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

[edit | edit source]
  • 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.[1]
  • The GRANT statement enables system administrators to grant privileges and roles, which can be granted to user accounts and roles.[2]
  • The REVOKE statement enables system administrators to revoke privileges and roles, which can be revoked from user accounts and roles.[3]
  • The format of the CREATE ROLE statement is:[4]
    CREATE ROLE role
  • The format of the CREATE USER statement is:[5]
    CREATE USER user DEFAULT ROLE role
  • The format of the GRANT statement is:[6]
    GRANT action ON object TO user_or_role
  • The format of the REVOKE statement is:[7]
    REVOKE action ON object FROM user_or_role
  • The INFORMATION_SCHEMA.USER_PRIVILEGES table provides global user and role privilege information for MySQL.[8]
  • The INFORMATION_SCHEMA.TABLE_PRIVILEGES table provides user and role privilege information for Microsoft SQL Server.[9]
  • The format of the SHOW GRANTS statement is[10]
    SHOW GRANTS FOR user_or_role [USING role]

Key Terms

[edit | edit source]
Data Control Language (DCL)
A syntax used to manage access (authorization) to data stored in a database.[11]

See Also

[edit | edit source]

References

[edit | edit source]
  1. ↑ Wikipedia: Data control language
  2. ↑ Wikipedia: Data control language
  3. ↑ Wikipedia: Data control language
  4. ↑ MySQL: CREATE ROLE
  5. ↑ MySQL: CREATE USER
  6. ↑ MySQL: GRANT
  7. ↑ MySQL: REVOKE
  8. ↑ MySQL: INFORMATION_SCHEMA USER_PRIVILEGES
  9. ↑ Microsoft: TABLE_PRIVILEGES
  10. ↑ MySQL: SHOW GRANTS
  11. ↑ Wikipedia: Data control language
Retrieved from "https://en.wikiversity.org/w/index.php?title=Database_Management/Data_Control_Language&oldid=2547213"