CREATE USER & GRANT PRIVILEGES in MySQL

SELECT users

You can see the users on a MySQL server at any time using the statement:

SELECT user FROM mysql.user;

CREATE USER

The basic syntax is as follows:

CREATE USER [IF NOT EXISTS] 'username'@'hostname' IDENTIFIED BY 'password';

The hostname is the name of the host from which the user connects to the MySQL Server. If left blank, the user can connect from any host.

IF NOT EXISTS is optional and self-explanatory.

Example: CREATE USER IF NOT EXISTS 'eric'@'localhost' IDENTIFIED BY 'halfab33';

If the username and hostname contains special characters, such as spaces or hyphens, you need to enclose the username and hostname separately in quotes.

GRANT

You can grant privileges to a user for all databases on the server, one database, or only certain tables in a database.

The basic syntax is as follows:

GRANT PRIVILEGE ON database.table TO 'username'@'host';

Example:

GRANT CREATE ON testdb.countries TO 'eric'@'localhost';

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD ON *.* TO 'eric@'localhost' WITH GRANT OPTION;

One, or a comma-separated list, of privileges can be specified from CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD.

Or you can be brave and make the user “Superman”!

GRANT ALL PRIVILEGES ON *.* TO 'eric'@'localhost' WITH GRANT OPTION;

Giving only the minimum privileges necessary to a user is always best.

FLUSH PRIVILEGES

FLUSH PRIVILEGES;

Re-reads the privileges from the grant tables in the mysql system schema. As part of this operation, the server reads the global_grants table containing dynamic privilege assignments and registers any unregistered privileges found there.

SHOW

SHOW GRANTS FOR 'username'@'host';

REVOKE

Remove privileges from a user:

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

Change password

Changing a user’s password: ^[5]

ALTER USER 'username' IDENTIFIED BY 'new_password';

DROP USER

The basic syntax is as follows:

DROP USER 'username'@'localhost';

Example: DROP USER 'eric'@'localhost';

MySQL Workbench GUI

MySQL Workbench offers the convenience of a GUI to perform all these tasks: ^[2]

1. Select Users and Privileges
2. The Users and Privileges tab opens
3. Select a user
4. View the Details for the selected user’s account
5. Click Add Account to add a new user
6. Delete the selected user account
7. Apply changes if made