Deep Thought

MySql User Role Privileges Reference

3 min read

🗂️ MySQL User, Role, and Privilege Management Reference

This guide introduces the syntax and purpose of MySQL statements used to create and manage databases, users, roles, and privileges.
It provides general explanations — not specific examples from any exercise.

🧱 1. Creating and Using Databases

Create a database

CREATE DATABASE database_name;

Creates a new database. The name must be unique on the server.

Select a database to work with

USE database_name;

Switches the active database context to database_name.

🧩 2. Creating Tables

Create a table

CREATE TABLE table_name (
    column_name data_type [constraints],
    ...
);

Defines a new table structure within the current database.

  • data_type specifies what kind of data is stored (e.g., INT, VARCHAR(100), DATE).
  • constraints can define rules like PRIMARY KEY, NOT NULL, or FOREIGN KEY.

🧮 3. Inserting Data

Insert rows into a table

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Adds one or more rows to a table.
Multiple rows can be inserted by separating sets of values with commas.

👥 4. Managing Users

Create a user

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Creates a new MySQL account.

  • 'host' defines where the user can connect from ('localhost' or '%' for any host).
  • 'password' sets the user’s authentication password.

View existing users

SELECT User, Host FROM mysql.user;

🧑‍🤝‍🧑 5. Managing Roles

Create a role

CREATE ROLE role_name;

Creates a named role, which can hold privileges that can later be assigned to users.

Grant a role to a user

GRANT role_name TO 'username'@'host';

Assigns one or more existing roles to a user.

Set default roles

SET DEFAULT ROLE ALL TO 'username'@'host';

Ensures that the granted roles are active automatically upon login.

🔐 6. Granting Privileges

Grant privileges to a user or role

GRANT privilege_type ON database_name.table_name TO 'username'@'host';

Allows a user or role to perform certain actions.

Common privileges include:

  • SELECT — Read data from tables
  • INSERT — Add data
  • UPDATE — Modify existing data
  • DELETE — Remove data
  • ALL PRIVILEGES — Grants all available permissions on the object

You can grant privileges on:

  • A specific table
  • An entire database
  • Or even all databases (using *.*)

🚫 7. Revoking Privileges

Revoke privileges

REVOKE privilege_type ON database_name.table_name FROM 'username'@'host';

Removes specific privileges from a user or role.

🧾 8. Viewing Granted Privileges

Show privileges of a user or role

SHOW GRANTS FOR 'username'@'host';

Displays all privileges and roles granted to a user or role.

🧹 9. Dropping Users, Roles, or Databases

Drop a user

DROP USER 'username'@'host';

Drop a role

DROP ROLE role_name;

Drop a database

DROP DATABASE database_name;

Removes the specified object permanently. Use with caution!

⚙️ 10. Additional Tips

  • Always use least privilege principle: grant only the access a user or application truly needs.
  • Use roles to manage permissions for groups of users efficiently.
  • Regularly check privileges with SHOW GRANTS to maintain secure configurations.

Graph View