MySQL: Check who’s trying to access data they should not

To illustrate how easy it’s to see who’s trying to access data they have not been granted for, we will first create a schema with two tables:

mysql> create database mydata;
mysql> use mydata
mysql> create table table1 (id int auto_increment primary key, 
              name varchar(20), something varchar(20));
mysql> create table table2 (id int auto_increment primary key, 
              name varchar(20), something varchar(20));

Now, let’s create a user :

mysql> create user myuser identified by 'mypassword';

And as it’s always good to talk about SQL ROLES, let’s define 3 roles for our user:

• myrole1: user has access to both tables in their entirety, reads and writes
• myrole2: user has access only to `table2`, reads and writes
• myrole3: user has only access to the column `name`of `table1` and just for reads

mysql> create role myrole1;
mysql> grant select, insert, update on mydata.* to myrole1;

mysql> create role myrole2;
mysql> grant select, insert, update on mydata.table2 to myrole2;

mysql> create role myrole3;
mysql> grant select(name) on mydata.table1 to myrole3;

Now let’s try to connect using our new user that doesn’t have any roles assigned yet:

$ mysqlsh myuser@localhost
Please provide the password for 'myuser@localhost': *****
MySQL   localhost:33060+   SQL  

Perfect we are connected, can we see the schema and use it ?

 MySQL   localhost:33060+   SQL  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.0018 sec)
 MySQL   localhost:33060+   SQL  use mydata
MySQL Error ERROR 1044: Access denied for user 'myuser'@'%' to database 'mydata

So far, so good. Let’s assigned the first role to our user:

mysql> grant myrole1 to myuser;

Now the user can use the role:

 MySQL   localhost:33060+   SQL  set role 'myrole1';
Query OK, 0 rows affected (0.0007 sec)
 MySQL   localhost:33060+   SQL  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydata             |
+--------------------+

Now I will add some data in both tables:

SQL> insert into table1 values (0, 'fred', 'aaa'), 
                               (0, 'kenny', 'bbb');
SQL> insert into table2 values (0, 'dave', 'ccc'), 
                               (0, 'miguel', 'ddd');

Of course the user can select and see everything:

MySQL   localhost:33060+    mydata  SQL  select * from table1;
+----+-------+-----------+
| id | name  | something |
+----+-------+-----------+
|  1 | fred  | aaa       |
|  2 | kenny | bbb       |
+----+-------+-----------+
2 rows in set (0.0011 sec)
 MySQL   localhost:33060+    mydata  SQL  select * from table2;
+----+--------+-----------+
| id | name   | something |
+----+--------+-----------+
|  1 | dave   | ccc       |
|  2 | miguel | ddd       |
+----+--------+-----------+
2 rows in set (0.0010 sec)

If you remember it, before we assigned any role to our user, we tried to use the schema and it failed. Let’s see if as the DBA having access to performance_schema we can see it:

mysql> select * from performance_schema.events_errors_summary_by_user_by_error 
where sum_error_raised>=1  and user='myuser' 
and ERROR_NAME like '%DENIED%' order by LAST_SEEN desc\G
******************* 1. row *******************
              USER: myuser
      ERROR_NUMBER: 1044
        ERROR_NAME: ER_DBACCESS_DENIED_ERROR
         SQL_STATE: 42000
  SUM_ERROR_RAISED: 1
 SUM_ERROR_HANDLED: 0
        FIRST_SEEN: 2019-11-15 03:25:17
         LAST_SEEN: 2019-11-15 03:25:17

Now let’ s change the role for the user, for example the most restrictive one and do some operations, but before that, we need to grant all remaining roles to our user:

mysql> grant myrole2, myrole3 to myuser;

 MySQL   localhost:33060+    mydata  SQL  set role 'myrole3';
Query OK, 0 rows affected (0.0008 sec)
 MySQL   localhost:33060+    mydata  SQL  select current_role();
+----------------+
| current_role() |
+----------------+
| myrole3@%    |
+----------------+
1 row in set (0.0008 sec)

And now let’s do some queries…

 MySQL   localhost:33060+    mydata  SQL  select * from table1;
ERROR: 1143: SELECT command denied to user 'myuser'@'localhost' 
for column 'id' in table 'table1'
 MySQL   localhost:33060+    mydata  SQL  select * from table2;
ERROR: 1142: SELECT command denied to user 'myuser'@'localhost' for table 'table2'
 MySQL   localhost:33060+    mydata  SQL  select name from table1;
+-------+
| name  |
+-------+
| fred  |
| kenny |
+-------+
2 rows in set (0.0010 sec)

And running the same query as above, we can now very all those access attempts that were not granted:

 mysql> select * from performance_schema.events_errors_summary_by_user_by_error 
where sum_error_raised>=1  and user='myuser' 
and ERROR_NAME like '%DENIED%' order by LAST_SEEN desc\G
 ******************* 1. row *******************
              USER: myuser
      ERROR_NUMBER: 1142
        ERROR_NAME: ER_TABLEACCESS_DENIED_ERROR
         SQL_STATE: 42000
  SUM_ERROR_RAISED: 1
 SUM_ERROR_HANDLED: 0
        FIRST_SEEN: 2019-11-15 03:40:50
         LAST_SEEN: 2019-11-15 03:41:43
 ******************* 2. row *******************
              USER: myuser
      ERROR_NUMBER: 1143
        ERROR_NAME: ER_COLUMNACCESS_DENIED_ERROR
         SQL_STATE: 42000
  SUM_ERROR_RAISED: 1
 SUM_ERROR_HANDLED: 0
        FIRST_SEEN: 2019-11-15 03:41:38
         LAST_SEEN: 2019-11-15 03:41:38
 ******************* 3. row *******************
              USER: myuser
      ERROR_NUMBER: 1044
        ERROR_NAME: ER_DBACCESS_DENIED_ERROR
         SQL_STATE: 42000
  SUM_ERROR_RAISED: 1
 SUM_ERROR_HANDLED: 0
        FIRST_SEEN: 2019-11-15 03:25:17
         LAST_SEEN: 2019-11-15 03:25:17

In summary, it’s very easy to very who’s trying to access unauthorized data and when it happened.

And don’t forget, it’s time to upgrade to MySQL 8 ! #MySQL8isGreat 😉

Related links to SQL ROLES:

• https://lefred.be/content/how-to-grant-privileges-to-users-in-mysql-8-0/
• https://lefred.be/content/some-queries-related-to-mysql-roles/
• https://lefred.be/content/mysql-8-0-roles-and-graphml/

Credits for the icons:

• Security Camera by Anton Barbarov from the Noun Project
• computer access control by monkik from the Noun Project