SQL User Management and Permissions — Controlling Access to Your Database

A reporting account needs to read class data, while an administration account manages student records. Sharing one unrestricted account removes that distinction.

Database accounts and privileges determine which operations a connection may perform. Least privilege gives each role the permissions needed for its actual work.

Create a named account with an appropriate authentication policy, grant specific privileges at the intended scope, inspect the grants and revoke access when the role changes. MySQL account identities include both user and host, so the same user name with a different host is a different account.

Related concepts: Views and Prevent SQL Injection and Stored Procedures.

• Protect sensitive data (like student marks)
• Enforce roles (teacher vs admin vs auditor)
• Track and log who did what

SQL · portable concepts; preserved examples identify their engine

-- Syntax (MySQL):
CREATE USER 'teacher1'@'localhost' IDENTIFIED BY 'SecurePass@123';

This creates a user named teacher1 who can connect from the local machine.

SQL · portable concepts; preserved examples identify their engine

Give read-only access to students table:

GRANT SELECT ON school.students TO 'teacher1'@'localhost';

Give full access to marks table:

GRANT SELECT, INSERT, UPDATE ON school.marks TO 'teacher1'@'localhost';

When FLUSH PRIVILEGES is relevant

Normal CREATE USER, GRANT and REVOKE statements apply their account changes without this command. The retained command is useful when reloading grant tables after direct modifications; it is not a required final step for the grants above.

FLUSH PRIVILEGES;

SQL · portable concepts; preserved examples identify their engine

SHOW GRANTS FOR 'teacher1'@'localhost';

GRANT SELECT ON school.students TO 'teacher1'@'localhost';
GRANT SELECT, INSERT, UPDATE ON school.marks TO 'teacher1'@'localhost';

SQL · portable concepts; preserved examples identify their engine

REVOKE INSERT, UPDATE ON school.marks FROM 'teacher1'@'localhost';

This removes data modification rights but retains read-only access.

SQL · portable concepts; preserved examples identify their engine

DROP USER 'teacher1'@'localhost';

This permanently removes the user from the system.

SQL · portable concepts; preserved examples identify their engine

• Teachers: Read/write access to marks, no access to admin tables
• Principal: Full access to all tables
• Auditors: Read-only access to financial and academic data
• IT Admins: ALL privileges + permission to create/drop users

• Never use root for applications
• Create separate users for read vs write operations
• Change default passwords immediately
• Use REVOKE when permissions are no longer needed
• Review SHOW GRANTS regularly

GRANT and REVOKE take effect through the account-management statements; MySQL does not require FLUSH PRIVILEGES after normal account statements. Example passwords are placeholders, not credentials to deploy. Read access can still expose sensitive information.

Design a read-only report role. Decide whether it needs SELECT, UPDATE or DROP, then test the shown report through the chosen account.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT name FROM practice_students WHERE class = '10A' ORDER BY roll_no;

The report requires SELECT on its permitted data source, not permission to update records or drop the table. A real privilege-denial test needs separate configured accounts on the chosen server.

Database accounts and privileges determine which operations a connection may perform. Least privilege gives each role the permissions needed for its actual work.

The report requires SELECT on its permitted data source, not permission to update records or drop the table. A real privilege-denial test needs separate configured accounts on the chosen server.

Continue your understanding with Views and Prevent SQL Injection and Stored Procedures.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.