SQL

User And Permissions Management In MySQL

February 20, 2016

By default, MySQL comes with a root user configured. You can also create additional users, change passwords for users, and assign what databases and tables they have access to. From MySQL, you can can create a basic user using the CREATE USER statement, providing a user, a location, and then using IDENTIFIED BY followed by a password. In production, this would look similar to the following, using krypted as the user and mysecretpassword as the password:

CREATE USER 'krypted'@'localhost' IDENTIFIED BY 'mysecretpassword';

Once you’ve created a user, you’ll want to assign what the user can access. Here, the * wildcard is pretty handy. In the following command, we’ll use the GRANT statement along with ALL PRIVILEGES to give this new krypted user access to all of the databases running on MySQL:

GRANT ALL PRIVILEGES ON * . * TO 'krypted'@'localhost';

Pretty easy so far. Just flush the permissions with the FLUSH PRIVILEGES statement and krypted’s now all good to access anything that exists in MySQL when the command was run.

FLUSH PRIVILEGES;

Once you’ve flushed, you can see what a user can access using the SHOW GRANTS statement:

SHOW GRANTS FOR 'krypted'@'localhost';

If you create new databases, do so again. To login as the user, you can then just run mysql followed by the -u option to define the user:

mysql -u krypted -p

To remove permissions, use the REVOKE statement. Let’s remove the ALL PRIVILEGES from krypted:

REVOKE ALL PRIVILEGES ON *.* FROM 'krypted'@'localhost';

To delete a user, use the DROP statement, and then USER, followed by who we’re deleting:

DROP USER ‘krypted’@‘localhost’;

If you were then going to create the user and provide a different level of privileges you could replace ALL PRIVILEGES with one of the following:

  • ALL PRIVILEGES: Gives the user full access to a database (or all included with a wildcard)
  • CREATE: Gives a user the ability to create new databases or tables within a database the access is provided
  • DROP: Gives a user the ability to use the DROP statement to remove tables or databases
  • DELETE: Gives a user the ability to delete rows from tables
  • GRANT OPTION: Gives a user the ability to add and remove privileges for other users
  • INSERT: Gives a user the ability to create rows into tables using the INSERT statement
  • SELECT: Gives a user the ability to use SELECT statements (similar to read in POSIX)
  • UPDATE: Gives a user the ability to update table rows only

We can also string some of this together in one statement, such as if we wanted the krypted password to expire in 60 days:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'new_password' PASSWORD EXPIRE INTERVAL 60 DAY;

A full list of options would include the following syntax, with options including maximum queries, max connections, ssl, auto-lock, etc:

CREATE USER [IF NOT EXISTS]
user_specification [, user_specification] ...
[REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}]
[WITH resource_option [resource_option] ...]
[password_option | lock_option] ...

user_specification:
user [ auth_option ]

auth_option: {
IDENTIFIED BY ‘auth_string’
| IDENTIFIED BY PASSWORD ‘hash_string’
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY ‘auth_string’
| IDENTIFIED WITH auth_plugin AS ‘hash_string’
}

tsl_option: {
SSL
| X509
| CIPHER ‘cipher’
| ISSUER ‘issuer’
| SUBJECT ‘subject’
}

resource_option: {
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}

password_option: {
PASSWORD EXPIRE
| PASSWORD EXPIRE DEFAULT
| PASSWORD EXPIRE NEVER
| PASSWORD EXPIRE INTERVAL N DAY
}

lock_option: {
ACCOUNT LOCK
| ACCOUNT UNLOCK
}