SQL Data Control Language

Overview of DCL
  • DCL (Data Control Language) manages database access and authorization using GRANT and REVOKE.

  • Accompanied by account-management commands: CREATE USER, ALTER USER, and DROP 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_name to 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';