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'); |
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 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
|
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 |
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 |
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
|
option two | The second option invokes creating a SQL file that will be executed when the database starts, this is the more secure option
|
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.