User Management

MySQL does a little different to other database servers, a user in MySQL is a username and a host string (hostname, IP address, fully qualified domain name or netmask), for example

each of the above users can have different passwords, a user could have multiple entries, MySQL will try and match the most specific username. You can use wildcards % and _ in host strings here are some examples

192.168.1.% matches 192.168.1.[0-255]
192.168._.% matches 192.168.[0-9].[0-255]
%.hostname.com matches any host ending in "hostname.com

To create, drop or rename a user see below

display users select * from mysql.user;
display who you are logged in as select user();
select current_user();
create user create user 'ops'@'192.168.2.%' identified by 'password';
delete user drop user 'ops'@'192.168.2.%';
rename user rename user 'ops'@'192.168.2.%' to 'support'@'192.168.2.%';
reset password set password for 'ops'@'192.168.2.%' = password('new password');

Access Control Lists

As with other databases users are granted access to database objects, MySQL uses the least privilege when an account is created, what I mean by this is that they have no permissions to do anything until an administrator grants this privilege. The ACL (also called the grant tables) are held in memory, when a user tries to access an object it uses the most specific credentials first if a user has multiple credentials. MySQL uses the standard grant and revoke commands to allow a user access to objects or to take them away.

First lets see what privileges a user can have

all grants all privileges (except grant option)
alter allows a user to use alter table
alter routine allows a user to alter or drop stored routines
create allows a user to use create table
create routine allows a user to create stored routines
create temporary tables allows a user to use create temporary table
create user

allows a user to use

  • create user
  • drop user
  • rename user
  • revoke all privileges
create view allows a user to use create view
delete allows a user to use delete
drop allows a user to use drop
event allows a user to use create event and drop event
execute allows a user to run stored routines
file allows a user to execute both select into outfile and load data infile
grant option allows a user to grant other users privileges
index allows a user to use create index and drop index
insert allows a user to use create insert
lock tables allows a user to use lock tables
process allows a user to see all processes when executing show processlist
references this is not used
reload allows a user to execute flush
replication client allows a user to execute both show master status and show slave status
replication slave needed by the replication slave to read binary logs from the master
select allows a user to execute select
show databases when a user executes show databases command will return a list of all databases
show view allows a user to execute show create view
shutdown allows a user to execute mysqladmin shutdown
super

allows a user to execute

  • change master kill
  • kill
  • purge master logs
  • set global
trigger allows a user to use create trigger and drop trigger
update allows a user to execute update
usage allows a user to connect

There are five levels that privileges can have

Global

Global privileges apply to all databases on a MySQL server, these privileges are stored in mysql.user table, below is an example

sql> grant reload,shutdown on *.* to 'ops'@'192.168.%';

Database Database privileges apply to all objects to a specified database, these privileges are stored in mysql.db and mysql.host tables, below is an example

sql> grant all on <database>.* to 'ops'@'192.168.%';
Table Table privileges apply to all columns in a given table, these privileges are stored in the mysql.tables_priv table, below is an example

sql> grant all on <database>.<table_name> to 'ops'@'192.168.%';
Column

Column privileges apply to one or more columns in a given table, these privileges are stored in mysql.columns_priv table, below is an example

sql> grant select (col1,col2), insert (col1,col2), update (col1,col2) on <database>.<table_name> to 'ops'@'192.168.%';

Routine

The create routine, alter routine, execute and grant privileges apply to stored routines (functions and procedures), they can be granted at the global and database level, they are stored in mysql.procs_priv table, se below for an example

sql> grant create routine on database.* to 'ops'@'192.168.%';

I am not going to detail on revoke as it is self explaining and very similar to the grant command.

You can display what privileges you have using either show grants or using the unix commandline command mk-show-grants (maatkit tool kit is required)

display privileges ## using mysql commandline tool
sql> show grants

## using the unix commandline
#> mk-show-grants -uroot -ppassword

Resetting the root password

There are times when the root password is lost or you have started a new job and no-one knows what the root password is, the process of resetting the root password will require a restart of the MySQL server. The first option involves using the skip-grants-table option, the second option involves creating a SQL file and using the init-file option to call this SQL apon startup, here are both options

option one

This process involves using the skip-grants-table option, which is less secure than option two as it allows anyone for a brief period of time to access the MySQL server, as when the server is restarted it is "wide open" with anyone able to log in with all privileges without specifying a username

  1. Edit the configuration file (mysql.ini) and the line skip-grants-table
  2. Restart the server
  3. connect to the server using mysql
  4. change the root password

    update mysql.user set password=PASSWORD('new password') where user='root';
  5. exit the client and stop the database, remove the skip-grants-table and restart
option two

The second option invokes creating a SQL file that will be executed when the database starts, this is the more secure option

  1. create a sql file, call it what you like, the contents should be

    update mysql.user set password=PASSWORD('new password') where user='root'; FLUSH privileges;
  2. once the file has been saved make a note of the full path and filename
  3. edit the configuration file (mysql.ini) and add the below line in the mysqld section

    init-file=<path>/<file_name>
  4. stop and restart the mysql server
  5. check that the root password as been reset
  6. remove the line from task 3 from the configuration file

Debugging Account Problems

There will be a time when a user will call regarding an account or privilege problem, below are a few of the common account problems that occur and how to fault find.

The most common problem i find is the just simply trying to login to mysql, this can be a simple password reset or a confusion on the users account, to change a users password

changing a users password update mysql.user set password=PASSWORD('new password') where user=<user>;
FLUSH privileges;

The next thing to check is the users privileges, the details of the user are held in the mysql.user table, remember MySQL is different from other databases in that an account is made up of a username and an host string, mostly it is the host string that needs to be checked, check that either the hostname or IP address has been set correctly within the mysql.user table, you can use the command below to check if the user has a login for where he/she is trying to login from, sometimes there could be double entries specially for global accounts like admin, ops and each different having a different password

check the users grants show grants for <username>@<hostname or IP address>

show grants for 'ops'@'localhost';
show grants for 'ops'@'192.168.0.30';

One thing to note with MySQL is that there is no locking of accounts after a number of unsuccessful attempts like in other databases.

Another common issue that I have is when you try to connect you get an error message stating "Can't connect to local mysqld through socket '/path/to/mysqld.sock'", this only happens on Unix/Linux servers, the socket file can be located by examining the my.cnf file and looking for the entry socket, locate the file and check the permission and that it exists., if it does not, restart MySQL and this file should be recreated, if not then check the permissions on the directory and make sure that you can write to it.

The next problem is that user has actually logged in with no problems but cannot access anything, first thing to check is to make sure that he is logged in with who he thinks he has logged in as, to check what account you have logged in as see the below command

check who you are logged in as select user();
select current_user();

If the user still has access problems accessing tables, etc then you need to check the privilege tables which I identified earlier in this section and grant the user access to what he needs.