MySQL user administration

MySQL

MySQL has a flexible and refined security scheme based on access control list. A user in MySQL is identified by the login and ID of the client computer (IP, name), this is based on the principle that the user Pepe that connects from the office does not have to be the same Pepe that connects from the House. MySQL uses tables user, db, host, tables_priv, columns_priv, and procs_priv from mysql database to manage every user privileges allowing to define different level access: database, tables, columns, and operations (select, insert, delete, update, grant). In this guide we will show how create and delete users, establish and revoke permissions.

Create user

create user user-name [identified by 'contraseña'];
-Where: user-name = 'usr-name'@'comp-name', if omitted @'comp-name' then 
the user can connect to the mysql server from any client computer

-If identified by 'password' is omitted, then the user can connect
to the mysql server without specifying password (not recommended method)

Connect to the server mysql (localhost)

For more info see: MySQL Server Administration – Basic

mysql -u sedlav -p

Once authenticated if we try to perform any operation, the mysql server will deny it due we not have established privileges to the user sedlav

Establish / revoke privileges

If the user which are establishing privileges does not exist then the mysql server creates it and assign the corresponding perms

Set privileges. Synopsis

grant privileges (columns) on data_base.table_name to usr[@nom-comp] 
[identified by contraseña];

Revoke privileges. Synopsis

revoke privileges (columns), grant option on data_base.table_name from usr[@nom-comp];

Where privileges = all | select[,insert,[update]]

Examples

Set global permissions (all the permissions on all tables in all databases)

grant all on *.* to sedlav@localhost identified by password;

Revoke global permissions

revoke all, grant option from sedlav@localhost;

Establish / revoke global privileges to user sedlav on database prueba

Set
grant all on prueba.* to sedlav@localhost identified by password;
Revoke
revoke all on prueba.* from sedlav@localhost identified by password;

Establish / revoke privileges specific to a table

Set
grant select,insert on prueba.usuario to sedlav@localhost identified by password;
Revoke
revoke select,insert on prueba.usuario from sedlav@localhost;

Establish / revoke specific privileges for a column

Set
grant update(column-name) on prueba.usuario to sedlav@localhost
identified by password;
Revoke
revoke update(column-name) on prueba.usuario from sedlav@localhost;

Show privileges

For the current session
show grants;
For a given user
show grants for nom-usuario[@nom-comp];

Refresh privileges

flush privileges;

Delete user

drop sedlav[@nom-pc];

Recommended reading

-help grant (MySQL)
-help revoke (MySQL)
-help drop user (MySQL)

YouTube video

Administering MySQL from the command line, 9 (15)

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.