#76 - MySQL Managing users and privileges

One of the database admin tasks is to manage the user’s privileges. In some applications the databases hold critical information, therefore if should be carefully implemented. Some of the types of access that can be granted to the users are typically Read, Write, Edit and Delete. The syntax to execute these actions in MySQL are for example SELECT, INSERT, UPDATE, DELETE.

root is generally the user that has the most privileges in a database server. The other users can have the same level of privileges or limited privileges to a specific database, to table and even to a specific column.

Executing commands from prompt:

Change dir to the MySQL executable:

> cd "C:\Program Files\MySQL\MySQL Server 5.7\bin"

> mysql -u root -p -t -vvv

mysql> SHOW DATABASES;

+--------------------------+

| Database                 

+--------------------------+

| information_schema

| mysql                       

These two databases have information related to the management and configuration of the MySQL server.

Exploring mysql database:

mysql> USE mysql;

mysql> SHOW TABLES;

+---------------------------+

| Tables_in_mysql           

+---------------------------+

| columns_priv             

| db                       

| engine_cost         

| event                   

| func                     

| general_log         

| gtid_executed             

| help_category     

| help_keyword             

| help_relation             

| help_topic               

| innodb_index_stats      

| innodb_table_stats       

| ndb_binlog_index         

| plugin                   

| proc                     

| procs_priv               

| proxies_priv             

| server_cost               

| servers                   

| slave_master_info         

| slave_relay_log_info     

| slave_worker_info         

| slow_log                             

| tables_priv                         

| time_zone                           

| time_zone_leap_second     

| time_zone_name               

| time_zone_transition         

| time_zone_transition_type

| user                                   

+---------------------------+

mysql> DESCRIBE user;

Host and User are the Primary keys for this table user.

mysql> select user, host from user;

+---------------+-----------+

| user            | host     

+---------------+-----------+

| root            | localhost

+---------------+-----------+

localhost means this local computer/server, and root is the username.

mysql> SHOW GRANTS FOR 'root'@'localhost';

+---------------------------------------------------------------------+

| Grants for root@localhost                                           

+---------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

means root has all privileges (Read, Write, Delete, Create, etc) on all databases and all tables (*.*) and root user can grant privileges to other users.

List of all privileges configured:

mysql> SELECT * FROM information_schema.user_privileges;

+-----------------------------+----------------------------------------+

| GRANTEE                  |PRIVILEGE_TYPE         

+-----------------------------+----------------------------------------+

| 'root'@'localhost'         | SELECT               

| 'root'@'localhost'         | INSERT                 

| 'root'@'localhost'         | UPDATE                 

| 'root'@'localhost'         | DELETE                 

| 'root'@'localhost'         | CREATE               

| 'root'@'localhost'         | DROP                   

| 'root'@'localhost'         | RELOAD               

| 'root'@'localhost'         | SHUTDOWN         

| 'root'@'localhost'         | PROCESS             

| 'root'@'localhost'         | FILE                   

| 'root'@'localhost'         | REFERENCES   

| 'root'@'localhost'         | INDEX                 

| 'root'@'localhost'         | ALTER               

| 'root'@'localhost'         | SHOW DATABASES   

| 'root'@'localhost'         | SUPER                   

| 'root'@'localhost'         | CREATE TEMPORARY TABLES

| 'root'@'localhost'         | LOCK TABLES           

| 'root'@'localhost'         | EXECUTE                 

| 'root'@'localhost'         | REPLICATION SLAVE

| 'root'@'localhost'         | REPLICATION CLIENT

| 'root'@'localhost'         | CREATE VIEW             

| 'root'@'localhost'         | SHOW VIEW               

| 'root'@'localhost'         | CREATE ROUTINE     

| 'root'@'localhost'         | ALTER ROUTINE       

| 'root'@'localhost'         | CREATE USER           

| 'root'@'localhost'         | EVENT                   

| 'root'@'localhost'         | TRIGGER               

| 'root'@'localhost'         | CREATE TABLESPACE

Creating a new user:

mysql> CREATE USER 'scada'@'localhost' IDENTIFIED BY 'password';

Granting all privileges to the new 'scada' user in all tables of the test database. When the 'scada' user logs in, he will see and have access only to the assigned database test.

mysql> GRANT ALL ON test.* TO 'scada'@'localhost' WITH GRANT OPTION;

mysql> SELECT user, host FROM user;

+---------------+-----------+

| user            | host     

+---------------+-----------+

| root            | localhost

| scada         | localhost

+---------------+-----------+

To update the privileges assignment during the session:

mysql> FLUSH PRIVILEGES;

To delete an user from the MySQL server:

mysql> DROP USER 'scada'@'localhost';

Creating a new user and granting access to read only a single column names 'tagpath' from the database 'test' and table 'sqlth_te':

mysql> CREATE USER 'plc'@'localhost';

mysql> GRANT SELECT(tagpath) ON test.sqlth_te TO 'plc'@'localhost';

When the plc user logs in, it can only read from the column above:

mysql> SHOW COLUMNS FROM sqlth_te;