Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30908

make SESSION_USER() standard compatible

Details

    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)
      

      Attachments

        Issue Links

          Activity

            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
            dlenski Daniel Lenski (Inactive) added a comment - 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: Returns user@host which matches a row in mysql.user , like CURRENT_USER and reflects the invoking user, rather than the defining user, even inside a SQL SECURITY DEFINER procedure

            Implemented behavior:

            • existing function SESSION_USER(), which used to be an alias for USER() will now show the value of CURRENT_USER() when the session was created.
            • that is, it will show a user@host pair from the mysql.global_priv table, like CURRENT_USER()
            • but unlike CURRENT_USER() it will not change inside stored routines and views
            • this is SQL Standard behavior of the SESSION_USER()
            • backward-compatible behavior can be restored with the old mode SESSION_USER_IS_USER
            serg Sergei Golubchik added a comment - Implemented behavior: existing function SESSION_USER() , which used to be an alias for USER() will now show the value of CURRENT_USER() when the session was created. that is, it will show a user@host pair from the mysql.global_priv table, like CURRENT_USER() but unlike CURRENT_USER() it will not change inside stored routines and views this is SQL Standard behavior of the SESSION_USER() backward-compatible behavior can be restored with the old mode SESSION_USER_IS_USER

            pushed into bb-11.7-MDEV-30908-session-user

            serg Sergei Golubchik added a comment - pushed into bb-11.7- MDEV-30908 -session-user

            PR https://github.com/MariaDB/server/pull/2985 is still pending to be merged..

            otto Otto Kekäläinen added a comment - PR https://github.com/MariaDB/server/pull/2985 is still pending to be merged..

            Of course. According to https://mariadb.com/kb/en/mariadb-quality-development-rules/ it requires a tester sign-off. Which is why it's in the IN TESTING state now.

            serg Sergei Golubchik added a comment - Of course. According to https://mariadb.com/kb/en/mariadb-quality-development-rules/ it requires a tester sign-off. Which is why it's in the IN TESTING state now.

            ok to push

            ramesh Ramesh Sivaraman added a comment - ok to push

            PR is merged

            serg Sergei Golubchik added a comment - PR is merged

            People

              serg Sergei Golubchik
              Christianggm Christian Gonzalez
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.