What is the difference between GRANT and CREATE USER?

CREATE USER creates a new MySQL account with a username and password. GRANT gives that account permissions to access databases and perform operations. You need both: create the user first, then grant privileges. You can also combine them with CREATE USER ... IDENTIFIED BY.

REVOKE removes previously granted privileges from a user. For example: REVOKE SELECT ON database.* FROM username@host; removes read access. After revoking, the user can no longer perform those operations until privileges are granted again.

User Management & Privileges

Control who can access your database and what they can do with CREATE USER, GRANT, and REVOKE.

So far you have been working as a single all-powerful user. In production, you never give applications or users full access. Instead, you create specific users with only the permissions they need. This is the principle of least privilege, a cornerstone of database security.

Definition

MySQL user management controls who can access the database server and what they can do. CREATE USER creates accounts, GRANT assigns permissions, and REVOKE removes them.

Required step: User management requires administrative privileges. Some commands may not work in the playground. These are essential for real MySQL server administration.

Creating Users

A MySQL user is defined by a username and a host. The host determines where the user can connect from.

Create a user

CREATE USER 'app_user'@'localhost'
IDENTIFIED BY 'secure_password_123';

Creates a user 'app_user' that can only connect from localhost (the same machine). The password is required.

Create a user for remote access

CREATE USER 'remote_user'@'192.168.1.%'
IDENTIFIED BY 'secure_password_123';

Creates a user that can connect from any machine on the 192.168.1.x network. The % is a wildcard for the host IP.

Create a user for any host

CREATE USER 'admin'@'%'
IDENTIFIED BY 'secure_password_123';

Creates a user that can connect from any machine. Use with caution. This is less secure than restricting the host.

Granting Privileges

Privileges control what a user can do. You can grant privileges on specific databases, tables, or globally.

Grant SELECT on a database

GRANT SELECT ON school.* TO 'app_user'@'localhost';

Gives app_user read-only access to all tables in the school database. They can run SELECT but not INSERT, UPDATE, or DELETE.

Grant multiple privileges

GRANT SELECT, INSERT, UPDATE, DELETE
ON school.* TO 'app_user'@'localhost';

Gives app_user full read/write access (but not DDL). They can query and modify data but cannot create or drop tables.

Grant all privileges on a database

GRANT ALL PRIVILEGES ON school.*
TO 'app_user'@'localhost';

Gives app_user full control over the school database, including DDL (CREATE, ALTER, DROP). Use sparingly.

Grant global privileges

GRANT CREATE, ALTER, DROP ON *.*
TO 'admin'@'%';

Gives the admin user global DDL privileges across all databases. This is very powerful and should only be given to database administrators.

Viewing and Revoking Privileges

Show grants for a user

SHOW GRANTS FOR 'app_user'@'localhost';

Displays all privileges currently assigned to the user. Useful for auditing who has what access.

Revoke privileges

REVOKE INSERT, UPDATE ON school.*
FROM 'app_user'@'localhost';

Removes INSERT and UPDATE privileges. The user retains SELECT and DELETE. You can revoke specific privileges without revoking everything.

Revoke all privileges

REVOKE ALL PRIVILEGES ON school.*
FROM 'app_user'@'localhost';

Removes all privileges on the school database. The user still exists but cannot do anything on this database.

Dropping Users

Drop a user

DROP USER 'app_user'@'localhost';

Removes the user and all their privileges. The user can no longer connect. This is permanent.

Drop if exists

DROP USER IF EXISTS 'app_user'@'localhost';

Same as above but does not throw an error if the user does not exist. Safer in scripts.

Security rule:Principle of Least Privilege

Give each user only the minimum privileges they need to do their job. An application that only reads data should get SELECT only. A reporting tool might need SELECT and nothing else. Only database administrators should have CREATE, ALTER, and DROP.

Never give application users ALL PRIVILEGES. If the application is compromised, the attacker would have full control of your database.

FLUSH PRIVILEGES

After modifying grant tables directly with SQL (not through GRANT/REVOKE), you need to reload them for changes to take effect.

Reload privilege tables

FLUSH PRIVILEGES;

Reloads the mysql.user and grant tables from disk. Only needed when you modify grant tables directly with INSERT, UPDATE, or DELETE. GRANT and REVOKE apply changes immediately.

Backing Up Your Database

Regular backups are essential. The mysqldump tool exports your database as SQL statements that can recreate the tables and data.

Back up a database

mysqldump -u root -p school > school_backup.sql

Exports the entire school database to a SQL file. The file contains CREATE TABLE and INSERT statements. Use -u for username, -p for password prompt.

Restore from backup

mysql -u root -p school < school_backup.sql

Imports the backup file into the school database. The SQL file recreates the tables and inserts all data.

Best practices:Backup Strategy

Automate backups. Use cron jobs or scheduled tasks to run mysqldump daily.

Store backups off-site. Keep copies on a different server or cloud storage.

Test restores regularly. A backup is useless if you cannot restore from it.

Use --single-transaction for InnoDB. This gives a consistent snapshot without locking tables.

MySQL Replication

Replication copies data from a master server to one or more replica servers in real time. It is used for load balancing, high availability, and disaster recovery.

Architecture:How Replication Works

Master server: Records all data changes in a binary log.

Replica server: Connects to the master and reads the binary log, then replays the changes.

The replica keeps a local copy of the master's data. Reads can be directed to the replica to reduce load on the master.

Basic replication setup

-- On the master:
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='master_host',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='password',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=154;

-- On the replica:
START REPLICA;

Configures the replica to connect to the master and start copying data. SHOW REPLICA STATUS on the replica shows the replication state.

What does GRANT SELECT ON school.* TO 'user'@'localhost' do?

Why should you restrict the host in CREATE USER?

Key Takeaways

CREATE USER defines a username and restricts which hosts can connect.
GRANT assigns specific privileges (SELECT, INSERT, etc.) on databases or tables.
SHOW GRANTS displays a user's current privileges.
REVOKE removes specific privileges without dropping the user.
Follow the principle of least privilege. Give users only what they need.
Never give application users ALL PRIVILEGES.

Ready to test your knowledge?

Take a Quiz