Re: logon trigger to restrict login from workbench

[Georgi Kodinov]

I know it's a bit of a long shot, but here's what we can do when we combine performance_schema.connect_attrs (https://dev.mysql.com/doc/refman/5.7/en/session-connect-attrs-table.html) with the init-connect (https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_init_connect) option:

Here's what session_attrs has on my computer:
mysql> select * from performance_schema.session_connect_attrs;
+----------------+-----------------+------------+------------------+
| PROCESSLIST_ID | ATTR_NAME | ATTR_VALUE | ORDINAL_POSITION |
+----------------+-----------------+------------+------------------+
| 2 | _os | Win64 | 0 |
| 2 | _client_name | libmysql | 1 |
| 2 | _pid | 4380 | 2 |
| 2 | _thread | 12248 | 3 |
| 2 | _platform | x86_64 | 4 |
| 2 | program_name | mysql | 5 |
| 2 | _client_version | 5.7.22 | 6 |
+----------------+-----------------+------------+------------------+
7 rows in set (0.00 sec)

So one could eventually define:

mysql> use mysql;
Database changed
mysql> delimiter |
mysql>
mysql> create procedure p1()
-> begin
-> if (0 < (select count(*) from performance_schema.session_connect_attrs where attr_name='program_name' and attr_value='mysql')) then
-> signal sqlstate '45000' set message_text='not mysql';
-> end if;
-> end|
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call p1();
ERROR 1644 (45000): not mysql
mysql> set global init_connect='call mysql.p1()';
mysql> create user t1@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> exit;
Bye


Now t1 can't do anything with mysql:
mysql.exe -u t1 -P 13000
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.22-debug-log

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s
ERROR 2013 (HY000): Lost connection to MySQL server during query

whereas root can still proceed:

mysql.exe -u root -P 13000
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.22-debug-log Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s
--------------
..\client\Debug\mysql.exe Ver 14.14 Distrib 5.7.23, for Win64 (x86_64)

Connection id: 18
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256
Using delimiter: ;
Server version: 5.7.22-debug-log Source distribution
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: cp850
Conn. characterset: cp850
TCP port: 13000
Uptime: 12 min 12 sec

Threads: 1 Questions: 51 Slow queries: 0 Opens: 119 Flush tables: 1 Open tables: 112 Queries per second avg: 0.069
--------------

mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Georgi "Joro" Kodinov
MySQL SrvGen team lead
Plovdiv, Bulgaria