MySQL has become increasingly popular in the last year because of its ease of use and price. Setting up a MySQL Server on a Windows machine is so easy you may not even realize it is there. Installing MySQL on a Linux machine isn’t quite as easy, but anyone with any experience installing programs on Linux wouldn’t have a problem. So, now that you have your server installed, how do you manage a multi-user environment? This is where your privileges come in.
All my past experience with MySQL Server was in a single user environment (me) and I didn’t have to worry about user authentication or rights and privileges. Recently I was posed with the problem of setting up a multi-user environment for a MySQL Server running on Redhat Linux 7.1 and I ran into a few bumps. Here is what I figured out about MySQL privileges:
The first thing to address is the root account. There is a root account on the Linux box and a root account for the MySQL server. In an ideal installation, immediately after you install the server, you set the root password in order to connect to the MySQL server. You should use the mysqladmin script in the $mysql/bin directory where $mysql is the directory where you installed MySQL. To set the new root password, from the bin directory, type:
This is what the MySQL manual says to do; however, it did not work for me. I was getting “access denied” when trying to connect as root. So, I found another option, which is to start the mysql server with privileges disabled. This was a lifesaver for me because it allowed me to connect to the database and configure all the user options without having to be logged in as root. What you do is stop the server, then start the server like this:
This will start the MySQL server with no privileges set. Be careful because that means there is no authentication at all! Anyone can connect! With that in mind, now you have the freedom to grant and revoke privileges all day. Now is a good time to introduce a nice MySQL front end called MySQL-Front. It has this great little user manager section that grants ands revokes all the privileges you need for you. You can download it here. (see below)
Although this program makes it easy, you should have an understanding of what is actually going on. There are three tables that determine a user’s privileges; DB, USER, and HOST. The most important is USER (of course).
When a client tries to connect, MySQL checks the user table first to see if the user exists, then it checks to make sure the right password was given. If so, then it checks the host field to see if the host that they are connecting from matches the host value given for that user. If there is a ‘%’ sign for the value of host, then the user is allowed to connect from any host. If a specific host is given (i.e. 192.168.1.10) then MySQL checks the HOST table for the entry where the host value from the USER table matches the host entry in the HOST table. If a match is made, then MySQL finds which databases they are allowed to see and what privileges they are allowed to have by finding each host entry. If a match is not made, then the user can only connect and can’t see any databases.
For example, the host table might have 192.168.1.10 in the host field and TEST in the db field, which means the user with 192.168.1.10 as a host can see the TEST database only. For each database, there needs to be an entry in the host table or a “%” for all databases. Once MySQL has determined that the user can connect and what databases the user can see, it then checks the DB table to see what actions this user can take on any particular database. These privileges include select, delete, update, create, drop, insert, grant, references, index, and alter. These privileges apply to the whole database, so if someone has select privileges on the TEST database, then they have privileges to select every table in the TEST database.
MySQL can also restrict access on the table level and even on the column level. What this means is that a user can have zero privileges on a database, but can have all privileges on a table in that database. Alternatively, a user can have zero privileges on a database, restricted privileges on a table, and all privileges on any particular column on that table. This is done by inserting records into the TABLE_PRIV table and the COLUMN_PRIV table.
Table privileges are the same as the database privileges above. Column privileges could be select, insert, update, or references. In the TABLE_PRIV table, you can restrict access on the table specified by putting each privilege separated by a comma in the table_priv column like this: “select, insert, update.” You can also restrict access to all columns in that table by putting in the privileges separated by a comma in the column_priv column in the TABLE_PRIV table.
If you want to restrict access on only certain columns in a table, you have to insert a record in the COLUMN_PRIV table and specify the privileges for a user in the column_priv column of theCOLUMN_PRIV table.
This might sound a little confusing, but once you get the MySQL-Front program installed you can easily browse the tables after you have added and edited users in the User Manager section. This will allow you to see what you have done in the User Manager section at the table level. Also, MySQL-Front shows you the SQL commands that it executes against the database in order to accomplish the privilege settings you chose. They consist of inserting records into the tables I just talked about and flushing privileges.
MySQL is a nice little database server that allows you to fill many simple database needs with a free database. Although it is not a true RDBMS, it gets the job done when it comes to storing and retrieving data and having multi-user environment capabilities.
I hope this information will be useful to you and as always feel free to email me with any questions or concerns. Later.
Nick Pile is an Murdok staff writer
