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

Event called procedure executed by other than definer user

Details

    • Task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • N/A
    • OTHER
    • None

    Description

      Event (CREATE EVENT) called procedure executed by other than definer user. I logged that USER() is event_scheduler@localhost, not the user that listed as event definer: information_schema.events.definer
      Is there possibility to run procedure as definer?

      Attachments

        Activity

          Please see documentation about stored procedures
          https://mariadb.com/kb/en/create-procedure/

          elenst Elena Stepanova added a comment - Please see documentation about stored procedures https://mariadb.com/kb/en/create-procedure/

          Thank you for answer. However procedure setting SQL SECURITY (I'm familiar with) doesn't help in number of situations. My procedure has SQL SECURITY INVOKER. And USER() within it returns 'event_scheduler@localhost'. When I change procedure to SQL SECURITY DEFINER, I can use privilege level of procedure definer, but still for USER() I'm getting 'event_scheduler@localhost', while the event was defined by someone else. And this is a problem, when you have views with filter conditions using USER(), triggers using USER() for audit purposes and so on.
          So in my opinion this is limited functionality comparing with Oracle, where jobs can be executed using job definer rights. And this relates to CREATE PROXY functionality which is also missing in MariaDB.
          Thanks anyway.

          balta Tadas Balaišis added a comment - Thank you for answer. However procedure setting SQL SECURITY (I'm familiar with) doesn't help in number of situations. My procedure has SQL SECURITY INVOKER. And USER() within it returns 'event_scheduler@localhost'. When I change procedure to SQL SECURITY DEFINER, I can use privilege level of procedure definer, but still for USER() I'm getting 'event_scheduler@localhost', while the event was defined by someone else. And this is a problem, when you have views with filter conditions using USER(), triggers using USER() for audit purposes and so on. So in my opinion this is limited functionality comparing with Oracle, where jobs can be executed using job definer rights. And this relates to CREATE PROXY functionality which is also missing in MariaDB. Thanks anyway.

          USER() in MariaDB returns the username/hostname that the user was connecting with, it does not show the account name that the user was authenticated as. CURRENT_USER is the account name, and with SQL SECURITY DEFINER the CURRENT_USER value changes to match the definer. Any views or triggers that are interested in account names should use CURRENT_USER, not USER().

          But perhaps in Oracle USER() is the account name? And you expect USER() to behave as in Oracle? Perhaps we need to change the behavior of USER() function on Oracle mode? (that is, this issue, perhaps, is not about events or stored routines or definers, but about USER() function which is misleading for Oracle users}

          serg Sergei Golubchik added a comment - USER() in MariaDB returns the username/hostname that the user was connecting with, it does not show the account name that the user was authenticated as. CURRENT_USER is the account name, and with SQL SECURITY DEFINER the CURRENT_USER value changes to match the definer. Any views or triggers that are interested in account names should use CURRENT_USER , not USER() . But perhaps in Oracle USER() is the account name? And you expect USER() to behave as in Oracle? Perhaps we need to change the behavior of USER() function on Oracle mode? (that is, this issue, perhaps, is not about events or stored routines or definers, but about USER() function which is misleading for Oracle users}

          Thank you for USER(), CURRENT_USER() explanation. I'd rather stay with USER(), because
          When calling CURRENT_USER() in a stored procedure, it returns the owner of the stored procedure
          In some cases CURRENT_USER() would be helpful, but not always. The reason I created this issue is that the EVENT definer is not the same user that creates session for specific scheduled time. I'm fine to have USER() as logged username regardless what is procedure SQL SECURITY or who is procedure definer. It's just some inconvenience for me to map additionally event_scheduler user to event definer user in application logic, but I'm OK with this workaround.

          balta Tadas Balaišis added a comment - Thank you for USER(), CURRENT_USER() explanation. I'd rather stay with USER(), because When calling CURRENT_USER() in a stored procedure, it returns the owner of the stored procedure In some cases CURRENT_USER() would be helpful, but not always. The reason I created this issue is that the EVENT definer is not the same user that creates session for specific scheduled time. I'm fine to have USER() as logged username regardless what is procedure SQL SECURITY or who is procedure definer. It's just some inconvenience for me to map additionally event_scheduler user to event definer user in application logic, but I'm OK with this workaround.

          People

            Unassigned Unassigned
            balta Tadas Balaišis
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.