[MDEV-30908] Create INVOKING_USER() function Created: 2023-03-22  Updated: 2024-01-12

Status: In Review
Project: MariaDB Server
Component/s: None
Fix Version/s: 11.5

Type: Task Priority: Minor
Reporter: Christian Gonzalez Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 1
Labels: privileges, procedures

Issue Links:
Relates
relates to MDEV-8017 current_user() in definer event/trigg... Closed

 Description   

Currently there are two different functions which can be used to get the information of the user executing a procedure:

  • CURRENT_USER(): Returns the user name and host name combination for the MariaDB account that the server used to authenticate the current client (i.e user and host columns from mysql.user table).
  • USER(): Returns the current MariaDB user name and host name, given when authenticating to MariaDB, as a string in the utf8 character set.

If you call these functions within a procedure with SQL SECURITY DEFINER, CURRENT_USER() will return the information of the procedure definer instead of the information of the user authenticated with the current client. The current alternative is to use the USER() but it might not return the same information. For example:

-- as root user
 
MariaDB [(none)]> delimiter /
 
MariaDB [(none)]> CREATE PROCEDURE mysql.user_function_demo() SQL SECURITY DEFINER BEGIN SELECT USER(), CURRENT_USER(); SELECT user,host FROM mysql.user; END/
 
MariaDB [(none)]> delimiter ;
 
-- as test_user user
 
MariaDB [(none)]> call mysql.user_function_demo();
 
+------------------------------------------------------+----------------+
 
| USER()                                               | CURRENT_USER() |
 
+------------------------------------------------------+----------------+
 
| test_user@ip-172-31-42-70.us-west-2.compute.internal | root@localhost |
 
+------------------------------------------------------+----------------+
 
1 row in set (0.000 sec)
 
 
 
+---------------+-----------+
 
| User          | Host      |
 
+---------------+-----------+
 
| test_user     | %         |
 
| mariadb.sys   | localhost |
 
| mysql.session | localhost |
 
| mysql.sys     | localhost |
 
| root          | localhost |
 
+---------------+-----------+
 
5 rows in set (0.001 sec)
 
 
 
Query OK, 0 rows affected (0.001 sec)

It would be useful to have a INVOKING_USER() function, which will return the same information than CURRENT_USER() but for the user invoking the procedure. Like:

MariaDB [(none)]> SELECT INVOKING_USER()
 
    -> ;
 
+-----------------+
 
| INVOKING_USER() |
 
+-----------------+
 
| test_user@%     |
 
+-----------------+
 
1 row in set (0.000 sec)



 Comments   
Comment by Daniel Lenski [ 2023-03-23 ]

To expand on this a bit…

  • CURRENT_USER() returns user@host in a form that can be easily matched to rows of mysql.user
    • However, inside a SQL SECURITY DEFINER procedure, it reflects the defining user rather than the invoking user.
    • (Per serg on MDEV-8017, that behavior is correct and required by the ANSI SQL standard. Fair enough.)
  • USER() reflects the invoking user, rather than the defining user, even inside a SQL SECURITY DEFINER procedure.
  • … but it cannot be easily matched to a row of mysql.user, because the @host portion reflects the hostname, and sometimes the TCP port (see MDEV-29651), from which the user has actually connected ⚠️

What would be useful here is a function that:

  1. Returns user@host which matches a row in mysql.user, like CURRENT_USER
  2. and reflects the invoking user, rather than the defining user, even inside a SQL SECURITY DEFINER procedure
Generated at Thu Feb 08 10:19:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.