Access Control

Introduction

What is it? A privilege is a permission. It controls what a database user is allowed to do: read rows, insert rows, update rows, delete rows, create tables, drop tables, and so on. The admin decides which user gets which permission on which database or table.

Why do we need it? A company database holds things like revenue numbers, customer emails, and login history. Not everyone in the company should see or change all of it. A support person may only need to read one table. An analyst may only need to read. A backend developer may need to read and write in one database. If everybody had full access, one mistake (or one stolen password) could delete or leak everything.

Real-world analogy Think of an office building.

• The building is the MySQL server.
• Each floor is a database.
• Each room on a floor is a table.
• Your ID card is your user account.
• The doors your card can open are your privileges.
• A job-title badge like "Engineering" that opens a fixed set of doors is a role.

You would never give every employee a master key to every room. You give each person only the doors they need, and the security desk (the admin) keeps a record of who has what.

Business use case A company hires a support agent. The agent needs to look up student records, but must never change them or see payment tables. The admin creates a user for the agent and grants only SELECT on the students table. If the agent leaves, the admin locks the account the same day.

Dataset Used

The database and table

CREATE TABLE students (
  student_id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(120) NOT NULL UNIQUE,
  full_name VARCHAR(80) NOT NULL,
  city VARCHAR(60) NULL,
  status ENUM('ACTIVE','BANNED') NOT NULL DEFAULT 'ACTIVE',
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

The table lives in a database called tuf_sql, so its full name is tuf_sql.students.

The accounts used in the examples

Syntax

What the scope can look like

Mental Model

Think of access control as three separate questions, asked in order:

1. Who are you? The account name and host, plus the password.
2. Which groups do you belong to? The roles attached to your account.
3. What exactly may you do? The privileges you hold, directly or through an active role.

 USERS (who)           ROLES (groups)          PRIVILEGES (what)
 +-----------+         +--------------+        +-----------------------+
 | raj       |-------->| engineering  |------->| SELECT, INSERT, UPDATE|
 | priya     |-------->|              |        | on tuf_sql.students   |
 +-----------+         +--------------+        +-----------------------+
 | amit      |-------->| support      |------->| SELECT                |
 +-----------+         +--------------+        | on tuf_sql.students   |
                                               +-----------------------+

Without roles you must repeat the same GRANT for every person. With roles you grant once to the role, and then simply attach the role to people.

 Without roles (slow, easy to forget one)    With roles (scalable)

 raj   --> SELECT, INSERT, UPDATE            raj   --+
 priya --> SELECT, INSERT, UPDATE            priya --+--> role: engineering
 karan --> SELECT, INSERT, UPDATE            karan --+          |
                                                                v
 3 separate GRANT statements                    SELECT, INSERT, UPDATE
                                                1 GRANT statement

How MySQL Checks Permissions

Every time a client connects and runs a statement, MySQL goes through these checks:

 Client logs in as  raj@%  with a password
              |
              v
   +---------------------------+
   | Is the account locked?    |---- yes ----> Login refused
   +---------------------------+
              | no
              v
   +---------------------------+
   | Is the password correct?  |---- no -----> Login refused
   +---------------------------+
              | yes
              v
        Session starts
              |
              v
 Client runs: INSERT INTO tuf_sql.students ...
              |
              v
   +-----------------------------+
   | Does this account hold      |---- no ----> Error: command denied
   | INSERT on tuf_sql.students, |
   | directly or through an      |
   | ACTIVE role?                |
   +-----------------------------+
              | yes
              v
        Statement runs

Three facts to remember:

• Privileges only add up. MySQL has no "deny" rule. If a privilege is not granted, the action is refused. Your real power is the sum of everything you have been granted.
• Login and permission are separate. A user can log in and still be unable to do anything.
• Only active roles count. A role that is granted but not active gives no privileges in that session.

Non-production examples: The short passwords and broad-host accounts below are intentionally simple for learning. Use strong secret management, restricted hosts, TLS, and least privilege in production.

1. Users and Hosts

MySQL stores user accounts (and roles) in a system table called mysql.user.

SELECT * FROM mysql.user;

You will see columns such as user and host. An account is always a pair: 'name'@'host'. The same name with a different host is a different account.

The host part

 Machines that try to connect            Who gets in?

 Laptop  (10.0.0.5)            ---+
 Office PC (192.168.65.1)      ---+----> MySQL server
 The server itself (localhost) ---+

 'raj'@'%'                     -> Laptop, Office PC, and the server itself
 'raj_ip'@'192.168.65.1'       -> Office PC only
 'raj_localhost'@'localhost'   -> the server itself only

Example 1: Create users for three different hosts

SQL

-- Any machine
CREATE USER 'raj'@'%' IDENTIFIED BY '123456';

-- Only the server machine (careful with Docker)
CREATE USER 'raj_localhost'@'localhost' IDENTIFIED BY '123456';

-- Only one specific machine
CREATE USER 'raj_ip'@'192.168.65.1' IDENTIFIED BY '123456';

Explanation Each statement creates one login account. IDENTIFIED BY sets the password. The host decides where the login is allowed from.

A Docker warning If MySQL runs inside Docker, then localhost means the Docker container itself, not your own computer. So 'raj_localhost'@'localhost' can only log in from inside that container.

Why it works MySQL checks both the name and the host when someone logs in. raj logging in from the office PC matches 'raj'@'%', but it does not match 'raj_ip'@'192.168.65.1' because the name is different, and it does not match 'raj_localhost'@'localhost' because the host is different.

A new user can log in but cannot do anything

Important note: creating a user does not give access to any database or table. A brand-new user can only connect. In SHOW GRANTS output, this shows up as USAGE, which means "can log in, has no privileges".

 CREATE USER 'raj'@'%' ...
           |
           v
 raj can log in   --->  raj can NOT read or change any table yet

2. Roles

A role is a reusable bundle of privileges. Instead of granting permissions to each person one by one, you grant them to a role, and then attach the role to the people who need it. This scales well and reduces mistakes.

Example 2: Create roles

SQL

CREATE ROLE 'engineering';
CREATE ROLE 'support';

Explanation These statements create two empty roles. They hold no privileges yet. You add privileges later with GRANT.

How to tell users and roles apart

In MySQL, users and roles are stored in the same system table, mysql.user. There is no separate roles table. So the difference is not the table name, it is the account properties.

For example, root, striver, and raj have passwords, so they are users. engineering and support have no password, so they are roles.

Example 3: List all roles

SQL

SELECT user AS role_name, host
FROM mysql.user
WHERE account_locked = 'Y'
  AND (authentication_string IS NULL OR authentication_string = '')
ORDER BY user;

Explanation

• account_locked = 'Y' keeps only locked accounts. Roles cannot log in, so MySQL stores them as locked accounts.
• authentication_string IS NULL OR authentication_string = '' keeps only accounts with no password.
• Together, these two filters match roles.

Output

Why it works A role is an account that nobody can log in to, and that has no password. Those two properties are exactly what the query filters on.

A note on accuracy: this is a practical filter, not a perfect one. A normal user that was locked and has no password would also appear. In MySQL 8 you can also look at mysql.role_edges, which records which roles were given to which accounts.

3. GRANT

GRANT gives permissions to a user or a role. The common privileges are SELECT, INSERT, UPDATE, DELETE, and so on.

Where to run these commands: as root or another admin account. An admin can create users and roles and can grant privileges.

Example 4: Grant SELECT on one table to a user

SQL

GRANT SELECT
ON tuf_sql.students
TO 'raj'@'%';

Explanation This lets raj read the students table and nothing else.

How to check what was granted

SHOW GRANTS FOR 'raj'@'%';

If you are logged in as raj yourself, plain SHOW GRANTS; shows raj's privileges. If you are the admin and want to look at raj, use FOR.

Sample output

USAGE on *.* means "can log in, no server-wide privileges". The second line is the real permission you just gave.

Cross-check by logging in as raj

SELECT * FROM students;

This works, because SELECT was granted.

Why INSERT will fail

INSERT INTO tuf_sql.students (email, full_name, city, status, created_at)
VALUES ('new@tuf.org', 'new student', 'blr', 'ACTIVE', '2027-01-10 10:00:00');

MySQL refuses this with a "command denied" error, because raj only holds SELECT. INSERT was never granted.

Why it works GRANT adds exactly the privileges you list, for exactly the scope you name. Anything not listed stays forbidden.

Example 5: Grant several privileges at once

SQL

GRANT SELECT, INSERT, UPDATE
ON tuf_sql.students
TO 'raj'@'%';

Explanation

• raj can read rows (SELECT).
• raj can add new rows (INSERT).
• raj can change existing rows (UPDATE).
• raj still cannot DELETE, ALTER, or DROP, because those were not granted.

Check as admin

SHOW GRANTS FOR 'raj'@'%';

Check as raj

SELECT * FROM tuf_sql.students;

INSERT INTO tuf_sql.students (email, full_name, city, status, created_at)
VALUES ('new@tuf.org', 'new student', 'blr', 'ACTIVE', '2027-01-10 10:00:00');

UPDATE tuf_sql.students
SET city = 'mumbai'
WHERE student_id = 1;

All three work now. A DELETE would still fail.

Why it works Privileges add up. The earlier SELECT grant and this new grant are combined, so raj holds all three.

Example 6: Grant privileges to a role

SQL

GRANT SELECT, INSERT, UPDATE
ON tuf_sql.students
TO 'engineering';

Explanation The role engineering now contains these privileges. Any user who is later given this role will inherit them. This is scalable: you manage access at the role level, not person by person.

Check

SHOW GRANTS FOR 'engineering'@'%';

A role name written without a host is stored with host %, which is why you write 'engineering'@'%' here.

Example 7: Give a role to a user, and activate it

SQL

GRANT 'engineering' TO 'raj_ip'@'192.168.65.1';

Explanation The user raj_ip is now linked to the role engineering. But there is a catch: the role is linked, not yet active.

 GRANT 'engineering' TO raj_ip
              |
              v
 Role is LINKED to the user, but NOT ACTIVE
              |
              +--- SET DEFAULT ROLE ... ---> active automatically at every login
              |
              +--- SET ROLE 'engineering' -> active for this session only

Make the role active automatically (admin does this)

SET DEFAULT ROLE 'engineering' TO 'raj_ip'@'192.168.65.1';

Check

SHOW GRANTS FOR 'raj_ip'@'192.168.65.1';

The output shows a line like GRANT `engineering`@`%` TO `raj_ip`@`192.168.65.1` . To see the real privileges that come from the role, add USING:

SHOW GRANTS FOR 'raj_ip'@'192.168.65.1' USING 'engineering';

Why it works Without a default role, the user would have to run SET ROLE 'engineering'; at the start of every session before the privileges take effect. Setting a default role removes that extra step.

4. GRANT ALL

GRANT ALL is a shortcut that gives all available privileges at the scope you choose. Instead of listing SELECT, INSERT, UPDATE, DELETE, and more, you give everything in one statement.

The real power of GRANT ALL depends on where you apply it: table, database, or whole server.

 *.*                           whole server  (every database)
   |
   +-- tuf_sql.*               one database  (every table in it)
         |
         +-- tuf_sql.students  one table

General syntax

GRANT ALL
ON <scope>
TO 'user'@'host';

Example 8: All privileges on one table

SQL

GRANT ALL
ON tuf_sql.students
TO 'raj'@'%';

Run as root/admin.

• raj can SELECT, INSERT, UPDATE, and DELETE rows in students.
• raj can also do table-level actions such as TRUNCATE (which needs the DROP privilege, depending on engine and version).
• Access is limited strictly to the students table.

Use this when a user fully owns or manages a single table but should not touch other tables.

Example 9: All privileges on all tables of one database

SQL

GRANT ALL
ON tuf_sql.*
TO 'striver'@'%';

Run as root/admin.

• striver can access every table inside tuf_sql.
• New tables created later inside tuf_sql are automatically covered.
• No access to databases outside tuf_sql.

Use this when someone is responsible for an entire application database, such as a backend developer or the database owner.

Example 10: All privileges on all databases

SQL

GRANT ALL
ON *.*
TO 'admin_user'@'%';

Only root or a super-admin should ever run this.

• The user gets all grantable static privileges at the global *.* scope.
• They can read, change, or drop databases and tables covered by those privileges.
• This still does not include separately granted dynamic privileges or GRANT OPTION.

Warning: this is extremely powerful and risky. Give it only to trusted admins, never to application users.

Extra detail: ALL at *.* means all grantable static privileges at that scope. It does not include dynamic privileges or the ability to pass privileges to others; delegation needs WITH GRANT OPTION, explained next.

5. WITH GRANT OPTION

WITH GRANT OPTION lets the receiver give the same privileges to other people. You are not just giving access, you are giving the power to share access.

Syntax

GRANT <privileges>
ON <scope>
TO 'user'@'host'
WITH GRANT OPTION;

Example 11: Let raj share his access

SQL

GRANT SELECT, INSERT
ON tuf_sql.students
TO 'raj'@'%'
WITH GRANT OPTION;

Explanation

• raj can SELECT and INSERT on students.
• raj can now also run GRANT SELECT, INSERT ON tuf_sql.students ... for other users.
• raj still cannot grant privileges he does not have himself.

 root --- GRANT ... WITH GRANT OPTION ---> raj --- GRANT SELECT ---> amit
                                            |
                                            +-- raj can only pass on
                                                what raj already has

Why this matters In real systems, careless use of WITH GRANT OPTION can lead to a privilege explosion: many users end up with access indirectly, through people who were never meant to hand it out. If you do not want a user to re-share privileges, simply leave WITH GRANT OPTION out. That single keyword makes a big difference in how much control you keep.

6. ALTER USER

ALTER USER changes an existing user account. The two most common real-world uses are:

• Password rotation (a security policy)
• Locking and unlocking accounts (incident response)

These commands must be run by root/admin, or a user allowed to manage accounts. A normal user cannot change someone else's password or lock their account.

 CREATE USER ---> ACTIVE ---ACCOUNT LOCK---> LOCKED ---ACCOUNT UNLOCK---> ACTIVE
                    |                           |
                    +---------- DROP USER ------+----> DELETED (grants are gone)

Example 12: Password rotation

Password rotation means changing a password regularly, or right away after a suspected leak. It reduces risk if the old password was exposed.

Syntax

ALTER USER 'username'@'host'
IDENTIFIED BY 'new_password';

SQL

ALTER USER 'tuf_support'@'localhost' IDENTIFIED BY 'Temp#5678';

What happens

• The old password stops working immediately.
• New login attempts must use the new password.
• A session that is already open may keep running (this depends on the client), but every new connection needs the new password.

Example 13: Emergency lock

Locking blocks a user from logging in without deleting the account. It is useful for suspicious activity, employee offboarding, or a compromised password.

Syntax

ALTER USER 'username'@'host' ACCOUNT LOCK;

SQL

ALTER USER 'tuf_support'@'localhost' ACCOUNT LOCK;

What happens

• The user cannot open new sessions. Login attempts fail.
• The account still exists. Its roles, grants, and history are untouched.
• This is safer than DROP USER when you may need to restore access later.

Example 14: Unlock an account

Unlock is the reverse of lock. It allows logins again.

Syntax

ALTER USER 'username'@'host' ACCOUNT UNLOCK;

SQL

ALTER USER 'tuf_support'@'localhost' ACCOUNT UNLOCK;

What happens

• The user can log in again (as long as they know the correct password).
• All earlier privileges and roles are exactly as they were.

7. REVOKE

REVOKE removes permissions or roles that were granted earlier. It is the opposite of GRANT. The one keyword that changes is TO becomes FROM.

Run these as root/admin, or a user allowed to manage grants and roles. Normal users usually cannot revoke access from others.

Example 15: Revoke a role from a user

Syntax

REVOKE 'role_name'
FROM 'username'@'host';

SQL

REVOKE 'role_support_ops'
FROM 'tuf_support'@'localhost';

What happens

• The user no longer has that role.
• Every privilege that came from the role is removed from that user.

Example 16: Revoke specific privileges

Syntax

REVOKE privilege_list
ON db_name.table_name
FROM 'username'@'host';

SQL

REVOKE INSERT, UPDATE
ON tuf_sql.students
FROM 'raj'@'%';

What happens

• raj keeps the other privileges he still has (for example, SELECT).
• raj loses INSERT and UPDATE.
• If raj now tries an INSERT or UPDATE, it fails with a "permission denied" error.

 Before REVOKE:  raj has  SELECT  INSERT  UPDATE
 REVOKE INSERT, UPDATE ...
 After  REVOKE:  raj has  SELECT

Deep Dive

The principle of least privilege Give each account only the permissions it needs to do its job, and nothing more. A reporting tool needs SELECT. A web app often needs SELECT, INSERT, UPDATE, and DELETE on its own database. Almost nothing except a DBA needs DROP or GRANT ALL ON *.*. If an account is ever stolen, the damage is limited to what that account could do.

No deny rule MySQL has no "deny this privilege" command. Access is controlled only by what has been granted. To take something away, you REVOKE it. If two grants overlap (one direct, one through a role), the user gets the combined result.

Privilege levels A privilege can be given at different widths: the whole server (*.*), one database (tuf_sql.*), one table (tuf_sql.students), and even specific columns. Use the narrowest one that does the job.

When do changes take effect? After a GRANT or REVOKE, the effect is not the same everywhere:

• Table-level and column-level changes apply on the user's next request.
• Database-level changes apply the next time the user runs a USE db_name.
• Server-wide (global) privilege changes and password changes apply the next time the user connects.

So if you revoke something in an emergency and the user is still connected, the safest move is to lock the account and end their session too.

Lock vs DROP USER ACCOUNT LOCK blocks logins but keeps the account, its grants, and its roles. DROP USER deletes the account and its grants completely. Use lock when you might want the account back, and drop when it is permanently gone.

MySQL 8 notes

• Roles were added in MySQL 8.0.
• In MySQL 8, GRANT does not create a missing user. Create the user first with CREATE USER, then grant.
• Privilege data is stored in the mysql system schema (for example, mysql.user).

Real-world Use Cases

• Support and analytics teams: a read-only role with SELECT on a few tables, so they can look things up but never change data.
• Application accounts: one account per app, limited to its own database, using a specific host or IP so it cannot be used from elsewhere.
• Team-based access: roles like engineering and support, so a new hire gets the right access with one GRANT 'role' TO user statement.
• Incident response: ACCOUNT LOCK the moment credentials are suspected to be stolen, then rotate the password and unlock.
• Offboarding: lock the account on the last day, then drop it after a review period.
• Delegated administration: WITH GRANT OPTION for a team lead who manages access inside one database, without giving them server-wide power.

Review Tips

Interviewers commonly ask:

• "What is the difference between a user and a role in MySQL?" — Both live in mysql.user. A user has a password and can log in. A role has no password and is a bundle of privileges.
• "What does '%' mean in 'raj'@'%'?" — The user may connect from any host.
• "Does CREATE USER give any access?" — No. The user can only connect (USAGE).
• "I granted a role to a user but the privileges do not work. Why?" — The role is linked but not active. Use SET DEFAULT ROLE or SET ROLE.
• "What is the difference between GRANT ALL ON tuf_sql.* and GRANT ALL ON *.*?" — One database versus the whole server.
• "What does WITH GRANT OPTION do, and why is it risky?" — It lets the receiver re-grant the same privileges, which can spread access too widely.
• "How do you block a user quickly without losing their setup?" — ALTER USER ... ACCOUNT LOCK.
• "What is the principle of least privilege?" — Give only the permissions that are needed.

A strong answer mentions that privileges are additive and that MySQL has no explicit deny.

Common Mistakes to Avoid

Mistake 1: Thinking CREATE USER gives access

Why it happens: The account exists and can log in, so it feels ready.

Wrong assumption: after CREATE USER 'raj'@'%' IDENTIFIED BY '123456';, raj can query students.

Correct: Raj can only connect. Follow up with a GRANT.

Mistake 2: Granting to the wrong host

Why it happens: raj@% and raj@localhost look alike, but they are two different accounts.

Wrong:

GRANT SELECT ON tuf_sql.students TO 'raj'@'%';

...while the person actually logs in as 'raj'@'localhost'. That account still has no privileges.

Correct: Grant to the exact user@host pair that logs in. Check with SELECT user, host FROM mysql.user;.

Mistake 3: Granting a role and forgetting to activate it

Why it happens: GRANT 'engineering' TO user looks complete.

Result: the user logs in and their privileges are missing.

Correct: Run SET DEFAULT ROLE 'engineering' TO 'user'@'host';, or have the user run SET ROLE 'engineering'; each session.

Mistake 4: Giving GRANT ALL ON . to an application user

Why it happens: It is the fastest way to stop "permission denied" errors.

Why it is dangerous: If the app has a security hole, the attacker gets full control of the server.

Correct: Grant only the privileges the app needs, only on its own database.

Mistake 5: Using WITH GRANT OPTION carelessly

Why it happens: It seems helpful to let a team lead hand out access.

Why it is dangerous: Access can spread to people nobody approved.

Correct: Leave it out unless you truly want delegation, and review who holds it.

Mistake 6: Dropping a user in an emergency when locking would do

Why it happens: "Delete the account" sounds the most secure.

Why it hurts: All grants and roles are lost. If the account was needed again, you must rebuild everything.

Correct: Use ACCOUNT LOCK first. Drop later, after review.

Mistake 7: Writing REVOKE ... TO

Why it happens: It is copied from a GRANT statement.

Wrong:

REVOKE INSERT ON tuf_sql.students TO 'raj'@'%';

Correct: REVOKE uses FROM:

REVOKE INSERT ON tuf_sql.students FROM 'raj'@'%';

Mistake 8: Running SHOW GRANTS and expecting to see another user's privileges

Why it happens: The first example in many tutorials shows SHOW GRANTS; for raj, but that command shows the privileges of whoever is logged in.

Correct: As admin, use SHOW GRANTS FOR 'raj'@'%';.

Mistake 9: Expecting a REVOKE to cut off an open session instantly

Why it happens: Changes feel immediate.

Why it fails: Some levels (for example, global privileges) only apply on the next connection.

Correct: For urgent cases, lock the account and end the session as well.

Key Takeaways

• A privilege is a permission. The admin decides who gets which permission on which database or table.
• A MySQL account is always 'user'@'host'. The same name with a different host is a different account.
• '%' means any machine, 'localhost' means only the server machine, and a specific IP means only that machine.
• Creating a user gives no access. A new user only has USAGE (can connect).
• Users and roles share the mysql.user table. A user has a password and can log in. A role has no password and is a bundle of privileges.
• GRANT gives privileges to a user or a role. Privileges add up, and MySQL has no deny rule.
• A granted role is not active until you set a default role or run SET ROLE.
• GRANT ALL depends on the scope: one table, one database (db.*), or the whole server (*.*). Never give *.* to application users.
• WITH GRANT OPTION lets the receiver pass the same privileges on. Use it sparingly.
• ALTER USER handles password rotation, ACCOUNT LOCK, and ACCOUNT UNLOCK. Locking is safer than dropping when you may restore access.
• REVOKE is the opposite of GRANT, and it uses FROM instead of TO.
• Follow the principle of least privilege: give only what is needed.