MENU
Data Control Language
Data Control Language (DCL) is the subset of SQL used to manage access to a MySQL server: creating and administering accounts, granting or revoking the privileges those accounts hold, and grouping privileges into reusable roles. Together, users, privileges, and roles form MySQL's access-control model.This chapter is organized into three topics:
- Users — creating, authenticating, renaming, locking, and dropping accounts with CREATE USER, SET PASSWORD, ALTER USER, RENAME USER, and DROP USER.
- Privileges — granting and revoking static and dynamic privileges with GRANT and REVOKE.
- Roles — bundling privileges into named roles with CREATE ROLE, assigning roles to users, and activating them per session with SET ROLE.
A typical access-control flow is to create accounts, grant the necessary static or dynamic privileges (directly or through a role), and then let each user connect under the least privilege needed for their work. See Users to start with account creation, or jump straight to Roles if privileges are already organized as roles.
-- Minimal end-to-end DCL flow: create an account, group privileges into a
-- role, grant that role to the account, and activate it in a session.
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'change_me_123!';
CREATE ROLE 'app_read';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT 'app_read' TO 'app_user'@'localhost';
ALTER USER 'app_user'@'localhost' DEFAULT ROLE 'app_read';
-- In a session authenticated as app_user, the role is active by default;
-- it can also be switched on explicitly:
SET ROLE 'app_read';