MySQL Forums
Forum List  »  Triggers

Re: logon trigger to restrict login from workbench
Posted by: Georgi Kodinov
Date: March 02, 2018 03:36AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: logon trigger to restrict login from workbench
1181
March 02, 2018 03:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.