SQL Data Control Language
Overview of DCL
DCL (Data Control Language) manages database access and authorization using
GRANTandREVOKE.Accompanied by account-management commands:
CREATE USER,ALTER USER, andDROP USER.Permissions can be defined at global, database, or table scopes.
1. Account Creation
Accounts are identified by
'username'@'host'.'localhost': Connects only from the database server itself.'%': Wildcard allowing connections from any remote host (use only when necessary).
CREATE USER 'student1'@'localhost' IDENTIFIED BY 'StrongPass123!';
CREATE USER 'student2'@'%' IDENTIFIED BY 'StrongPass456!';
2. Viewing Accounts
Account records are stored in
mysql.user.Query specific columns to avoid displaying authentication details unnecessarily.
SELECT User, Host FROM mysql.user ORDER BY User, Host;
3. Database-Level Privileges
Uses
database_name.*to apply permissions across all tables in a specific database.Follow the principle of least privilege (only assign needed actions:
SELECT,INSERT,UPDATE,DELETE).
GRANT SELECT, INSERT, UPDATE, DELETE ON school_db.* TO 'student1'@'localhost';
GRANT ALL PRIVILEGES ON school_db.* TO 'teacher1'@'localhost';
4. Table-Level Privileges
Uses
database_name.table_nameto restrict permissions to specific tables within a database.
GRANT SELECT, INSERT ON school_db.students TO 'encoder'@'localhost';
GRANT SELECT ON school_db.grades TO 'encoder'@'localhost';