1/39
Looks like no tags are added yet.
Data Control Language (DCL)
A category of SQL commands used to manage privileges that determine what database users can do.
Helps database administrators control access to database objects (such as tables) and reduce unauthorized activities.
Why DCL is important:
Protects confidential information
Limits unauthorized changes
Separates the responsibilities of users
Supports accountability and security management
GRANT
gives a user specified privileges on database objects.
REVOKE
removes specified privileges that were previously granted.
GRANT and REVOKE
Command | Purpose | When to use it |
|---|---|---|
GRANT | Assigns privileges to a database user | When a user needs permission to perform an operation |
REVOKE | Removes specified privileges previously assigned | When access is no longer needed or must be restricted |
GRANT and REVOKE MySQL syntax
GRANT privilege_name
ON database_name.table_name
TO 'username'@'host';REVOKE privilege_name
ON database_name.table_name
FROM 'username'@'host';
ON
identifies the database object where the privilege applies.
TO
identifies the account receiving a privilege (GRANT).
FROM
identifies the account whose privilege is removed (REVOKE).
Creating a Database User and Checking Privileges
A database account must exist before privileges are assigned.
In MySQL, CREATE USER creates the account.
CREATE USER 'app_user'@'localhost'
IDENTIFIED BY 'StrongExamplePassword!';
SHOW GRANTS FOR 'app_user'@'localhost';
app_user is the username.
localhost is the permitted connection host.
IDENTIFIED BY sets the account password.
SHOW GRANTS displays the privileges assigned to the specified account.
Creating an account does not mean it should automatically get full administrative access.
Authentication vs. Authorization
Concept | Main question | Meaning and examples |
|---|---|---|
Authentication | Who are you? | Verifies a user’s identity before access is provided. Examples: username and password, fingerprint, security key, multifactor authentication |
Authorization | What are you allowed to do? | Determines which resources and actions an authenticated user may access. Examples: a student views their grades; a teacher enters grades for assigned classes |
Remember: Authentication verifies identity. Authorization determines permitted access and actions.
Database User Roles
Role | Typical access / responsibility | Example |
|---|---|---|
Administrator (DBA) | Broad administrative privileges; manages database structure, accounts, access privileges, backups, and maintenance | Manages database users and backups |
Application User | Has the required CRUD privileges on selected tables for an application | A school application views students and adds enrollment records without managing accounts |
Read-Only / Reporting User | Retrieves information, typically through SELECT on permitted tables or views, but does not modify underlying records | Generates enrollment reports |
Guest / Limited User | Restricted access to approved information | Visitors can check available courses but cannot view private student records |
Principle of Least Privilege
Each user, application, or process should receive only the permissions necessary to perform its assigned tasks.
Benefits:
Reduces accidental data modification or deletion
Limits the impact of compromised accounts
Helps protect confidential information
Makes user activity and permissions easier to manage
Reduces unnecessary administrative access
Example: A reporting account that only needs to view student records should not be allowed to update or delete them.
Password Hashing vs. Plain-Text Passwords
Plain-text password: Stored in readable form. If an attacker obtains the database, the actual credentials may be exposed immediately.
Password hashing: Transforms a password into a derived value using a one-way cryptographic process. A properly designed hash is computationally expensive to reverse or guess.
Plain-text storage | Password hashing |
|---|---|
The actual password is stored in readable form | A derived hash value is stored instead of the readable password |
Good security practices:
Do not store passwords in plain text
Protect user credentials
Assign only the privileges users need
Avoid giving every account full administrative access
Stored Procedures: Meaning and Purpose
A stored procedure is a named group of SQL statements saved inside the database and executed by calling its name.
Why use them:
Reusability: write the logic once, use it again
Consistency: follow the same process each time
Organization: keep related SQL logic together
Parameters: accept input values and can provide output values
They help avoid repeating long SQL statements and centralize common database tasks.
Creating and Calling a Stored Procedure
DELIMITER //
CREATE PROCEDURE EnrollStudent(
IN p_StudentID VARCHAR(10),
IN p_CourseID VARCHAR(10)
)
BEGIN
INSERT INTO Enrollment
(StudentID, CourseID, EnrollDate)
VALUES
(p_StudentID, p_CourseID, CURDATE());
END //
DELIMITER ;
CALL EnrollStudent('S01', 'C101');
Key parts:
DELIMITER // temporarily changes the statement ending so the procedure can contain semicolons.
CREATE PROCEDURE gives the procedure its name and defines its parameters.
IN parameters receive values supplied when the procedure is called.
BEGIN ... END marks the block of SQL statements inside the procedure.
CALL executes the saved procedure.
The example enrolls a student using the supplied student ID and course ID. CALL runs it with the values 'S01' and 'C101'.
What Is a Trigger?
A trigger is a database program that runs automatically when a specified event happens on a table. Unlike a stored procedure, it does not need to be called manually.
Table event | When it occurs |
|---|---|
INSERT | When a new row is added |
UPDATE | When an existing row is changed |
DELETE | When a row is removed |
BEFORE and AFTER Triggers
Timing | Meaning |
|---|---|
BEFORE | Executes before the table operation takes place |
AFTER | Executes after the table operation takes place |
OLD and NEW Values in Triggers
In the lesson’s UPDATE trigger example:
Reference | Meaning | Example in an UPDATE trigger |
|---|---|---|
OLD | Previous or original row value |
|
NEW | Updated or new row value |
|
Audit Logging: Student Name Update Example
An audit log keeps a record of important changes. When a student’s name is updated, an AFTER UPDATE trigger automatically inserts the student ID, old name, new name, and change date into a StudentAudit table.
Sequence:
Create the StudentAudit table to store the change details.
Create an AFTER UPDATE trigger on the Students table.
When a student’s name is changed, the trigger runs automatically.
The trigger records OLD.StudentName and NEW.StudentName, plus the student ID and current date/time.
View the audit record with SELECT * FROM StudentAudit;
Important SQL elements:
OLD.StudentID: the student ID from the row before the update
OLD.StudentName: the name before the update
NEW.StudentName: the updated name
NOW(): returns the current date and time
StudentAudit: the table that stores the audit information
When Should You Use a Trigger?
Student details updated: save old and new values in an audit log
Product stock changes: check or respond to inventory changes
Employee record deleted: keep a record of the deletion
New transaction added: automatically update related information
Use a trigger when an action must happen automatically in response to a database event.
Stored Procedures vs. Triggers
Feature | Stored Procedure | Trigger |
|---|---|---|
Execution | Executed manually using CALL | Executed automatically |
When it runs | Can run whenever needed | Runs when a table event occurs |
Parameters / values | Can accept parameters | Uses OLD and NEW row values |
Main use | Reusable database operations | Automatic actions and business rules |
Lesson example | Enroll a student | Log a student update |
Memory guide: PROCEDURE → CALL. TRIGGER → EVENT.
Benefits and Cautions
Benefits | Cautions |
|---|---|
Automation | Too many triggers can be hard to manage |
Consistency | Automatic actions may be less visible and harder to debug |
Data integrity | Inefficient logic can slow operations |
Reusability | Extra SQL may run unexpectedly |
Audit tracking |
Best practice: Use automation thoughtfully, document it, and test it.
QUICK REVIEW: Key Distinctions
GRANT vs. REVOKE: GRANT gives specified privileges; REVOKE removes them.
Authentication vs. Authorization: Authentication asks “Who are you?”; authorization asks “What are you allowed to do?”
Plain text vs. Hashing: Plain text is readable; hashing transforms a password into a derived value using a one-way cryptographic process.
Procedure vs. Trigger: A procedure is called when needed; a trigger runs automatically when a specified table event happens.
BEFORE vs. AFTER: BEFORE runs before the table operation; AFTER runs after it.
OLD vs. NEW: OLD is the previous row value; NEW is the new or updated row value.
INSERT / UPDATE / DELETE: INSERT adds a row; UPDATE changes an existing row; DELETE removes a row.
1. What is the main purpose of DCL?
To manage privileges that determine what database users can do. It lets administrators control access to database objects like tables and reduce unauthorized activities.
2. How are GRANT and REVOKE different?
GRANT gives specified privileges to a user. REVOKE removes privileges that were previously granted.
3. What does SHOW GRANTS display?
The privileges assigned to a specified account, e.g. SHOW GRANTS FOR 'app_user'@'localhost';
4. What is the difference between authentication and authorization?
Authentication verifies identity (“Who are you?”), such as a username and password, fingerprint, or MFA. Authorization determines what an authenticated user is allowed to access or do (“What are you allowed to do?”).
5. Which database role is appropriate for a user who only generates reports?
The Read-Only / Reporting User, who can retrieve data (SELECT on permitted tables or views) but cannot modify records.
6. What does the principle of least privilege require?
Each user, application, or process should receive only the permissions necessary to perform its assigned tasks, nothing more. For example, a reporting account should not be able to update or delete records.
7. Why is storing passwords in plain text risky?
The passwords are stored in readable form, so if an attacker gets the database, the actual credentials are exposed immediately.
8. What is password hashing?
Transforming a password into a derived value using a one-way cryptographic process. The hash is stored instead of the readable password, and a properly designed hash is computationally expensive to reverse or guess.
9. What is a stored procedure, and how is it executed?
A named group of SQL statements saved inside the database. It is executed manually by calling its name with CALL, e.g. CALL EnrollStudent('S01', 'C101');
10. What does DELIMITER do when defining a procedure in MySQL?
It temporarily changes the statement ending (e.g. to //) so the procedure body can contain semicolons without ending the whole definition early. It is then set back with DELIMITER ;
11. What is the purpose of an IN parameter?
It receives a value supplied by the caller when the procedure is run, such as the student ID and course ID in EnrollStudent.
12. How does a trigger differ from a stored procedure?
A stored procedure runs manually when you CALL it, can accept parameters, and is used for reusable operations. A trigger runs automatically when a table event occurs, uses OLD and NEW row values, and is used for automatic actions and business rules. (PROCEDURE → CALL, TRIGGER → EVENT.)
13. What are the INSERT, UPDATE, and DELETE events?
INSERT: a new row is added.
UPDATE: an existing row is changed.
DELETE: a row is removed.
14. How are BEFORE and AFTER triggers different?
A BEFORE trigger executes before the table operation takes place. An AFTER trigger executes after it takes place.
15. What do OLD and NEW represent in an UPDATE trigger?
OLD is the row value before the update (e.g. OLD.StudentName is the original name). NEW is the row value after the update (e.g. NEW.StudentName is the updated name).
16. How can an audit trigger help a school database?
It automatically records changes without anyone having to do it manually. For example, when a student’s name is updated, an AFTER UPDATE trigger saves the student ID, old name, new name, and date/time into a StudentAudit table. This gives the school a trackable history of changes, supporting accountability and data integrity.