MySQL user administration

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)

Administering MySQL from the command line, 9 (15)

• How to install MySQL 5.7 on CentOS 7
• How to install MySQL 8.0 in Ubuntu 20.04
• How to install MySQL 8.0 in Debian
• How to install MariaDB on Alpine Linux
• How to install MariaDB on NetBSD?
• MySQL 8.0, change root password
• MySQL server administration – Basic
• Create / modify / delete tables in MySQL
• MySQL user administration
• MySQL – Execute SQL script
• Disable innodb engine in MySQL
• Show MySQL storage engines
• Character set available in MySQL
• Connect to a MySQL server from the LAN
• MySQL can not write in the /tmp dir