MySQL administration is more than running queries. You must also take precautions in security your data and managing the users who have access to this data. In larger corporations, you have several different users with different access permissions. You don't want to give root access to every user, which gives each user complete control of your server. Instead, root access is restricted to very few people, and then each additional user has its own unique permissions. This article explains how you can create and manage users on your MySQL database.

Understanding Users and Privileges

It's common in any security architecture to have a tiered and granular user permission graph. You don't want to give all users complete access to your database, but you need users to have the necessary permissions to run queries and perform daily tasks. When you work with application development, one user is used to connect any number of end users to the database. The application needs one user name, and this becomes the bridge between your other users and the database. In other words, hundreds of users connect to the database using an application, so you just need one user for this application.

You'll also have database administrators of different levels. You might want to give access to one database to one administrator, and then give access to a second database to a different administrator. You might want to make your security more granular and only give certain users access to specific tables. The more sensitive your database server becomes, the higher level of security you'll use on your databases.

A mistake in security and user privileges can create huge problems for any business. The wrong security gives access to unauthorized users, users who have left the company, or you give access to data that should only be seen by specific personnel. When you create users and grant privileges, you should be sure that you provide the right security without giving too many privileges that create risks.

A few items to note before you create your users:

1) MySQL user names can be up to 16 characters long. Some administrators use randomly generated user names with randomly generated complex passwords. When you create users that have a higher level of access, you should use user names that are complex. For instance, ‘john' is more easily guessed than ‘jo2015hn.' Your user names don't need to be as complex as passwords, but they should be difficult for hackers to guess.

2) The MySQL user name is not the same as the operating system account. You can use the same user name and password combination for the operating system as the database server, but this isn't recommended even though it's more convenient.

3) Never use blank passwords for a database server. Your password should be complex and contain alphanumeric characters as well as special characters.

4) MySQL uses its own encryption scheme for passwords. MySQL has a PASSWORD() function, and the same scheme is used to encrypt your MySQL administrator password.

Once you create a user, you then need to grant it permissions. One difference between MySQL and other database platforms is that the user and host name determine user permissions. The host name helps MySQL determine users who have access to specific hosts. For instance, you might have a user named "john" but you only want to give john access to the local database. The user would have access to the "localhost" host name, but it would not have access to the remote host. The host name can be a fully qualified name, an IP or a wildcard host name that gives access to several databases.

Database administrators can assign privileges to databases, tables and even columns. For instance, suppose you have a table with patient information. For HIPAA compliance, you can only give a selected number of individuals access to columns in the Customer table. You can't give them access to social security numbers, but you can give them access to columns such as the first and last name for them to run reports. This is called column-level security.

Creating and Using New Users

MySQL has a CREATE USER statement that lets you create a new user on your database. If you're working with a new database, you might only have a root user set up. To create users, you first need to log in with the root user or another user account that has permissions to create users.

To create a new user, use the following command:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

The command is pretty self-explanatory. The user name is "myuser." As we mentioned previously, you need to specify a host name for the user. The host name in this example is localhost. The localhost host name is shorthand for "this local computer." For instance, if your database is installed on a server named "myserver," using localhost as the host name would log in myuser on the myserver host. If you're located on a different server and try to log in using localhost, MySQL would block you since the local host name is different.

The IDENTIFIED BY statement specifies the user's password. Passwords should be complex especially when you plan to give the user elevated privileges. The MySQL database usually hosts all of the company's digital information, so it should have only the highest security level when working with access permissions.

In some cases, you want to give a user permission regardless of the host name. You might want to give a specific administrator access from any host, because the user needs to manage several servers and you don't want to create numerous users for each host name. You can accomplish this by using the host wildcard, which is the % character.

Let's look at an example. The following statement creates a user named myuser that gives access from all host names.

CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword';

This command is usually reserved for root level administrators. You don't want to give standard users access from any domain, because it reduces the effectiveness of your security.

GRANT Permissions

After you create a user, they still can't do anything with the login. They can log in to the MySQL server, but they can't access databases or query any data. For this reason, you need to give the user privileges.

There are numerous table and column level privileges that you can grant. You can also grant users the privileges to create other users or revoke other user privileges. The permissions you grant to a user should depend on what level of access you think they should have. The rule for security permissions is to give a user the least amount of privileges they need to accomplish normal tasks. For instance, you shouldn't grant all permissions to a standard user when they only need to run SELECT statements on a Customer table.

Let's first take a look at the standard template syntax for granting permissions.

GRANT [type of permission] ON [database name].[table name] FROM ‘[username]'@'localhost';

Even though it's not recommended, you should know how to grant all privileges to a user. The following statement is how you grant all privileges.

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

The GRANT ALL option gives all rights to the user myuser on all databases and all tables. As you can guess, this gives the user full control over your databases and associated data. You would use this command with trusted users and higher level administrators.

Let's reduce the privileges for our myuser@localhost account. Instead, we just want to give this user full control of a specific database. You would use this type of privileges with an administrator for a specific database. In some cases, you might want an app to have full control on a database to manipulate tables and data from within the application.

The following example gives myuser full access privileges on the "sampledatabase" database.

GRANT ALL sampledatabase.* TO 'myuser'@'localhost' WITH GRANT OPTION;

Notice this statement also includes the GRANT OPTION statement. This means that the user can grant other users permissions to the database. The user can only pass on his own permissions, which means since myuser has access to the sampledatabase, he can only pass permissions on the sampledatabase to other users. If we had only specified SELECT permissions, then myuser would only be able to pass SELECT permissions to other users.

With the above example, the myuser user has access to SELECT, INSERT, DELETE and UPDATE data. If your database contains highly sensitive information, you might not want this user to have so many privileges. Maybe you only want to allow myuser to read data, but they shouldn't be able to change it. In this case, you would grant myuser only SELECT permissions on the database.

To specify permissions on the sampledatabase database, use the following command.

GRANT SELECT ON sampledatabase.* TO 'myuser'@'localhost';

The wildcard asterisk in this statement again tells MySQL to grant permissions on all tables, but in this instance we only allow the user to SELECT data from the sampledatabase tables.

We can narrow down permissions even more. We can only give myuser access to one specific table. For instance, suppose you want myuser to only read customer data, but you don't want this user to have access to financial data in the same database. Using the wildcard and previous samples, the user would have access to all tables and therefore, all table data. MySQL lets you specify specific tables when granting user permissions.

Take a look at the following example.

GRANT SELECT ON sampledatabase.Customer TO 'myuser'@'localhost';

In the above query, the user only has access to the Customer table. The only action myuser can run on the table is the SELECT statement, so the user can only read the data but can't change or delete it.

You can narrow user permissions even further. The lowest level you can grant is column level permissions. These permissions specify the columns a user can read, edit or delete. For instance, suppose you only want myuser to have read permissions on the Customer first_name column.

The following SQL statement specifies read permission on the first_name column.

GRANT SELECT(first_name) ON sampledatabase.Customer TO 'myuser'@'localhost';

Now, your user only has access to one column on the Customer table.

After you grant permissions, you need to use the FLUSH command. Think of the FLUSH command as a way to flush user permissions and reassign any new ones. You need to run the command after granting permissions to tell the database to re-read the current user permission table, or your newly granted permissions will go unnoticed until the next time you reboot the server.

To run this command, type the following statement into your MySQL command line.

FLUSH PRIVILEGES;

Reviewing Users in Your Database

When you have employees or other administrators come and go, you must occasionally review the list of users that have access to your database server. Sometimes, administrators leave and you forget to remove the user name. You might need to review users to identify if your database was hacked and privileges elevated for an unknown account. Occasionally, it's good to do a thorough review of your users and remove and edit them where necessary.

The MySQL database stores most of its configurations in system tables. Users are also stored in system tables. For user accounts, you must query the mysql.user table to view a list of users and hosts with permissions to your database.

First, let's look at a list of users that have been created in MySQL. The following command lets you view all users.

select user,host from mysql.user;

This command shows only two columns from the user table. Since we created a user named myuser, you should see a similar output as the following.

user, host

------------------------------

root                 %

myuser           localhost

The root user is the main administrator, so it typically has the host wildcard associated with it. The root user has full access to all databases and tables, so it's common to see it with elevated and all privileges.

We created a myuser account with the host name localhost, so we should also see it in the user table.

The above command showed us the users we created in the database, but we still don't know the user permissions associated with each user. When you administer a MySQL database, you want to know who has access to data and who can change permissions on the database tables. To view a list of permissions, you use the SHOW GRANTS command. This command displays users and associated permissions.

Take a look at the following example.

SHOW GRANTS for 'root'@'%';

As expected, the following output is shown.

Grants for root@% 

-------------------------------------

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION

But, now we want to know what access was given to myuser. We can run the same command on the myuser account to view access permissions. Take a look at the following command.

SHOW GRANTS for 'myuser'@'localhost';

The output looks like the following.

Grants for myuser@localhost

-----------------------------------------------

GRANT USAGE ON sampledatabase.* TO 'myuser'@'localhost'

The above output tells you that the myuser account has privileges on the sampledatabase. We changed this user's permissions several times, but this output shows that the myuser account can read, edit and delete data on any table.

You can also view all privileges for all users by querying the user_privileges table. This is also a MySQL system table, and it has several records when you have numerous users. The following is an example of a SELECT statement for the user_privileges table.

SELECT * FROM information_schema.user_privileges;

You will see similar output as the following.

Grantee, Table_Catalog, Privilege_Type, Is_Grantable

---------------------------------------------------------------------------

‘root'@'localhost'     NULL SELECT                    YES

‘root'@'localhost'     NULL UPDATE        YES

‘root'@'localhost'     NULL DELETE                    YES

‘root'@'localhost'     NULL CREATE        YES

‘root'@'localhost'     NULL INSERT                     YES

‘myuser'@'localhost'           NULL SELECT                    NO

‘myuser'@'localhost'           NULL UPDATE        NO

‘myuser'@'localhost'           NULL DELETE                    NO

‘myuser'@'localhost'           NULL INSERT                     NO

Note that this is just a subset of the data that you would see for root. The root account has access on every level, so every permission would be included in your result set. The Is_Grantable column determines if the user can pass permissions to other users. In this example, root can transfer permissions. Our myuser account is not able to grant permissions to other users, but it has all four main SQL commands available to it.

Revoking Permissions and Deleting Users

If you saw any suspicious user accounts or privileges, you can either revoke permissions, delete the user or do both. If you think you were hacked, you'd want to investigate further, but the first precaution is to remove user permissions. If you revoke permissions for an account that must have access to specific data, it can cause errors in applications or you can interrupt a user's daily productivity. You should be sure that you want to remove a user and account privileges before you revoke them.

For example, suppose you want to remove the DELETE privilege from the myuser account permissions. You don't want to delete the user, but you want to stop the account from deleting any records. You use the REVOKE SQL command to remove the permission from the list of options.

The following SQL statement removes the DELETE permission from your user.

REVOKE DELETE ON sampledatabase.* FROM 'myuser'@'localhost';

In the above example, the myuser@localhost account no longer has the ability to delete records in the sampledatabase database. The user can still read and edit data, but it can no longer remove records. If myuser has the ability to delete records in other databases, these permissions would not be affected in other databases.

You also have the option to completely delete the user altogether. An option if you don't know if you should delete the user is to revoke all of its privileges to ensure that nothing fails. If the user is not needed on any system, then you can use the DROP command. The DROP command for users is similar to the statement for tables. Using the DROP statement deletes the user from the MySQL system.

The following command deletes the user from the system.

DROP USER ‘myuser'@'localhost';

Understanding and Updating Passwords

As a database administrator, you're responsible for all security on the MySQL server. This means you must change passwords regularly, especially for user accounts that have elevated privileges on tables and critical databases. Passwords should be complex, and they should be at least 8-10 characters. Some administrators use auto-generating password programs, and they store the passwords in an encrypted vault on a server. Users with permissions can then view these passwords when they too need to manage the MySQL server.

Most administrators use a word that's masked with numbers and special characters. For instance, instead of using the password ‘swordfish', the database administrator uses the password ‘$w0rdf1sh'. This makes the password easier to remember but difficult to hack.

MySQL has an internal encryption scheme, so you use plain text passwords when you change them on the server. However, they are stored using encryption for security reasons.

For instance, suppose you have a 30-day rule to change the root password each month. You want to change the password for root. The following statement shows you how to change a user's password.

SET PASSWORD FOR ‘root'@'localhost' = PASSWORD (‘mynewpassword');

That's all it takes. One statement and you change root's password. If any applications use the database, they also need to be changed. You shouldn't have any applications using the root account, but most applications use a user name and password to connect to the database. Changing the application's user account password would cause errors when the application connected to the database. Keep this in mind when you change user account passwords.

This article gave you an overview of managing user accounts and passwords. Keeping track of users and their permissions greatly reduces the chance that your database goes hacked without notice. By managing users, you keep granular control over your data and protect it from unauthorized access.