SQL TUTORIALS

Granting User Access

What is DCL?

The GRANT Statement

Summary:

• Data Control Language (DCL) is used to give access to or take access away from specific users. Like all digital systems, databases need to be secured. DCL enables Database Administrators (DBAs) to control who has access to which database objects, and what they can do with those objects (whether they can both query and insert data into the object, or just query the object).
• The GRANT statement is used to give specific users access to database objects. This is used with the following syntax:
  GRANT specific_privilege
ON database_name.object_name
TO user_name;
• Privileges tend to differ between RDBMSs, though here is a general list:
  • all is used to give the user every privilege available.
  • create gives the user the ability to create tables.
  • select gives the user the ability to execute SELECT statements on the object specified.
  • insert gives the user the ability to insert new rows into the object specified.
  • update gives the user the ability to update roes in the object specified.
  • delete gives the user the ability to delete rows from the object specified.
  • alter gives the user the ability to use the ALTER TABLE command to change the specified table's schema.
  • drop gives the user the ability to drop the specified object from the database.
• NOTE: It's always a good idea to use the Principle of Least Privilege, which states that users should be given only the amount of privileges that they need to perform their jobs.

Revoking User Access

Summary:

• The REVOKE statement is used to remove database privileges from a specific user. This is used with the following syntax:
  REVOKE privilege_name
ON database_name.object_name
FROM user_name;
• If you're a DBA, you can revoke all privileges given to a specific user and that user's ability to grant privileges for other users using the following syntax:
  REVOKE all, grant option
FROM user_name;
• If you're NOT a DBA and you need to remove all privileges given to a specific user and that user's ability to grant privileges fot other users, use the following syntax:
  REVOKE all
ON database_name.*
FROM user_name;

REVOKE grant option
ON database_name.*
FROM user_name;

Exercise

Congratulations! You just completed the DML Tutorial! To help test your knowledge, let's complete the exercise outlined below. This exercise is based on the exercise of the previous tutorial.
**It's highly recommended that you complete the exercise outlined in the previous tutorial before beginning this exercise.**

Instructions:

1. Open your RDBMS Client.
2. Open the "Sales" Database created in the previous tutorial.
3. Create a New Table named "Orders" with the following schema:
   • id (INT) [PRIMARY KEY]
   • order_date (DATE) [NOT NULL]
   • num_items (INT) [NOT NULL]
   • subtotal_amount (FLOAT) [NOT NULL]
   • tax_amount (FLOAT) [NOT NULL]
   • customer_id (INT) [FOREIGN KEY REFERENCES customers(id)
   • employee_id (INT) [FOREIGN KEY REFERENCES employees(id)
4. Download the following files:
   • Customers Table Mock Data CSV File
   • Employees Table Mock Data CSV File
   • Orders Table Mock Data CSV File
5. Insert the data from the "customers.csv" file into the "Customer" table.
6. Insert the data from the "employees.csv" file into the "Employees" table.
7. Insert the data from the "orders.csv" file into the "Orders" table.
8. Insert the following rows into the "Categories" table:
   • 1, "Furniture", "This includes all items that furnish a home or backyard. Examples inlude couches, coffe tables, and ottomans."
   • 2, "Small Appliances", "This includes all items that are used to assist customers in their daily activities around the home. Examples include small fridges, soda can dispensers, and coffee makers."
   • 3, "Bedding", "This includes all items that are related to sleeping. Examples include mattresses, fitted sheets, loose sheets, and pillow cases."
9. Update the "wage" column in the "Employees" table by multiplying the current value by 1.5 for all employees hired before 3/1/2020.
10. Update the "tax_amount" column in the "Orders" table by dividing the current value by 2 for all orders that had more than 10 items in it.
11. Delete all rows from the "Customers" table where the "city" column is not null and the "state" column is null.
12. Exercise Completed! Click here to view the answers.

Have any issues with the above exercise? Post your question on Discord!