MySQL Privileges

🔐 What are MySQL Privileges?

MySQL privileges control what actions users can perform on databases and tables. They ensure security by limiting access to authorized users only, protecting your data from unauthorized modifications.


-- Grant SELECT privilege to a user
GRANT SELECT ON database_name.* TO 'username'@'localhost';

Common Privilege Types

📖

SELECT

Read data from tables

GRANT SELECT ON db.* TO 'user'@'host';

✏️

INSERT

Add new records to tables

GRANT INSERT ON db.* TO 'user'@'host';

🔄

UPDATE

Modify existing records

GRANT UPDATE ON db.* TO 'user'@'host';

🗑️

DELETE

Remove records from tables

GRANT DELETE ON db.* TO 'user'@'host';

🔹 Creating Users and Granting Privileges

To create a new user and assign privileges, use the CREATE USER and GRANT statements. This allows you to control exactly what each user can do in your database system.

-- Create a new user
CREATE USER 'john'@'localhost' IDENTIFIED BY 'password123';

-- Grant specific privileges
GRANT SELECT, INSERT, UPDATE ON company_db.* TO 'john'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

🔹 Granting All Privileges

For administrative users, you can grant all privileges at once. This gives complete control over databases, tables, and other users. Use this carefully as it provides full access to your MySQL server.

-- Grant all privileges on all databases
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

-- Grant all privileges on specific database
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

🔹 Viewing User Privileges

You can check what privileges a user has using the SHOW GRANTS command. This displays all permissions assigned to a specific user account, helping you audit and manage access control effectively.

-- Show privileges for current user
SHOW GRANTS;

-- Show privileges for specific user
SHOW GRANTS FOR 'john'@'localhost';

-- Show all users
SELECT user, host FROM mysql.user;

🔹 Revoking Privileges

When you need to remove access, use the REVOKE statement. This removes specific privileges from a user without deleting their account. It's essential for maintaining proper security when user roles change or access needs to be restricted.

-- Revoke specific privileges
REVOKE INSERT, UPDATE ON company_db.* FROM 'john'@'localhost';

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON *.* FROM 'john'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

🔹 Database-Level vs Table-Level Privileges

MySQL allows you to grant privileges at different levels for fine-grained control. Database-level privileges apply to all tables in a database, while table-level privileges target specific tables. Column-level privileges provide even more precise control over individual columns.

-- Database-level privilege
GRANT SELECT ON mydb.* TO 'user'@'localhost';

-- Table-level privilege
GRANT SELECT ON mydb.employees TO 'user'@'localhost';

-- Column-level privilege
GRANT SELECT (name, email) ON mydb.employees TO 'user'@'localhost';

🔹 Common Privilege Combinations

Different user roles require different privilege sets. Here are typical combinations for common scenarios like read-only users, data entry users, and developers who need broader access but not full administrative control.

-- Read-only user
GRANT SELECT ON mydb.* TO 'readonly'@'localhost';

-- Data entry user
GRANT SELECT, INSERT ON mydb.* TO 'dataentry'@'localhost';

-- Developer user
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP 
ON mydb.* TO 'developer'@'localhost';

-- Backup user
GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'backup'@'localhost';

🔹 Deleting Users

When a user account is no longer needed, you should remove it completely using the DROP USER statement. This automatically revokes all privileges and removes the user from the MySQL system, ensuring no orphaned accounts remain.

-- Delete a user
DROP USER 'john'@'localhost';

-- Delete multiple users
DROP USER 'user1'@'localhost', 'user2'@'localhost';

-- Check if user exists before deleting
DROP USER IF EXISTS 'john'@'localhost';