Creating Users and Setting Permissions in MySQL

Creating Users and Setting Permissions in MySQL - Opening the MySQL console application

There are two major ways to run the MySQL console. You can either sit down at the physical server, or you can telnet into the server and run the application remotely. In either situation, you will need to be at the command prompt of the machine that is running the MySQL server.

The next most logical step is to determine the directory where MySQL is installed. On most Unix and Linux based installations, MySQL is located in the /usr/local/bin directory. On a Windows server, MySQL is most likely found under c:/mysql/. If you are having difficulty locating MySQL, check with your installation’s documentation.

To create users and set permissions, you have to run the console as a user that has permission to do so. By default, MySQL is installed with a root account with these permissions. Typically this account has no password when connecting from the localhost.

To start the MySQL console, at the command prompt, change to the MySQL directory and run the console by typing "mysql –u=root". This opens the MySQL console as the user root. If all goes well, you will be greeted by something similar to the following prompt:

Welcome to the mysql monitor. Commands end with ; or \g.
Your mysql connection id is 4 to server version 3.23.36

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

It is not always necessary to specify the user as root, however, it has been included here to help ensure the proper connection to the MySQL console. If you are having trouble or a root password has also been specified you can try to launch mysql with the following revised command: "mysql –u=root –p=passwrd". Keep in mind that you should replace passwrd with your actual root password.

Setting up a new user
Now we are ready to setup some MySQL user accounts. Let's run through a few GRANT statements and discuss in detail what they are doing. These statements should be entered in at the MySQL console prompt. Be sure to include all of the quotations in the statements and to end each statement with a semicolon.

mysql> create database somedb
-> grant all privileges on somedb.* to someusr@"localhost" identified by 'passwrd';

The first part of this statement creates a database called somedb. This step can be left out if the database that you want to use already exists. The second part is the actual statement that sets up the user and the privileges held by that user. Lets look at it in greater detail.

somedb.*

This specifies the database for which the user created will be allowed to access. The .* indicates that this user will be permitted to work with all of the tables within the database somedb. If you are interested in limiting the user to only one table, then the name of that table should be specified after the period in place of the asterisk.

someusr@"localhost"

This is what actually sets up the user information. The someusr is the username that is being created. The portion after the @ indicates the host from which this user is allowed to connect. In this case, this user can only connect from the localhost.

passwrd

This is the password to be used by the user. It is suggested that you use something more creative than the one indicated here.

Connecting from any host
In the previous example, we created a user that could only connect to a specific database from the local machine. In this example, we loosen things up a bit and allow the user a little more freedom. Here is the statement:

mysql> grant all privileges on *.* to someusr@"%"
> identified by 'passwrd';

As with the previous example, lets take a look at the statement piece by piece.

*.*

This indicates that the user will be permitted to connect to all of the MySQL databases and all of the tables contained in those databases.

someusr@"%"

The user is again specified as someusr, however, this time a host of "%" is used. This indicates that this user may connect to the database from any host or IP number. A more secure way to do this is to specify the IP number of your workstation, however, sometimes this is not possible depending on your network setup.

As in the previous example, the password is set as passwrd.

Creating Users and Setting Permissions in MySQL - Reducing User Privileges

In both of the previous examples, we created users that were granted all privileges on their respective database(s). It may be wise to consider reducing these privileges, especially if the user is connecting from a remote workstation. This is accomplished with the following statement:

mysql> grant select,insert,update,delete,create,drop
-> on somedb.* to someusr@"%" identified by 'passwrd';

With this statement, the user someusr is allowed limited access to the database somedb from any host ("%") using the password passwrd.

The GRANT statement specifies that this user is only allowed to run a limited amount of statements on the MySQL server. This user will be allowed to: select records, insert records, update records, delete records, create databases, and DROP DATABASEs. More importantly, this user is not permitted to create users and set privileges.

You could further reduce a user's privileges by removing other items from the GRANT statement. The best policy here is that the user should only be given permission to access the functions that are necessary to perform their tasks.

Refresh the MySQL grant tables
After going through and setting up users and permissions with grant statements, there is one final step to making the whole thing work.

At this point it is important for you to understand where all of this permission information is kept. When MySQL is installed, a database called mysql is automatically created. This database contains several tables that hold all of the information pertaining to each user, the databases they have access to, the hosts they can connect from, and the privileges allowed for each.

MySQL only loads these user tables and the permissions held within when it first boots. It does not take another look at those tables unless it is told to do so. So, without this step, none of your newly created users will work at all.

There are several ways to reload the privilege tables, however since we are already logged in to the MySQL console, we can do so by running the following command:

mysql> flush privileges;

This tells MySQL to take another look at the user tables and hence puts all of your new users and privileges into operation.