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?
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.
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}
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.
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
Tadas Balaišis
Votes:
0Vote for this issue
Watchers:
3Start 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.
{"report":{"fcp":2185.0999999046326,"ttfb":246.39999985694885,"pageVisibility":"visible","entityId":132991,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"b4ab4975-f1b2-445c-b936-11592b96fe9c","navigationType":0,"readyForUser":2156.899999856949,"redirectCount":0,"resourceLoadedEnd":1668.5,"resourceLoadedStart":269.89999985694885,"resourceTiming":[{"duration":84.20000004768372,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":269.89999985694885,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":269.89999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":354.09999990463257,"responseStart":0,"secureConnectionStart":0},{"duration":89.10000014305115,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":270.19999980926514,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":270.19999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":359.2999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":1079.7999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":270.5,"connectEnd":270.5,"connectStart":270.5,"domainLookupEnd":270.5,"domainLookupStart":270.5,"fetchStart":270.5,"redirectEnd":0,"redirectStart":0,"requestStart":355.2999999523163,"responseEnd":1350.2999999523163,"responseStart":376.59999990463257,"secureConnectionStart":270.5},{"duration":1362.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true&whisper-enabled=true","startTime":270.59999990463257,"connectEnd":270.59999990463257,"connectStart":270.59999990463257,"domainLookupEnd":270.59999990463257,"domainLookupStart":270.59999990463257,"fetchStart":270.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":360.5,"responseEnd":1632.8999998569489,"responseStart":416.69999980926514,"secureConnectionStart":270.59999990463257},{"duration":183.70000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":270.7999999523163,"connectEnd":270.7999999523163,"connectStart":270.7999999523163,"domainLookupEnd":270.7999999523163,"domainLookupStart":270.7999999523163,"fetchStart":270.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":361,"responseEnd":454.5,"responseStart":439.19999980926514,"secureConnectionStart":270.7999999523163},{"duration":255.20000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":270.89999985694885,"connectEnd":270.89999985694885,"connectStart":270.89999985694885,"domainLookupEnd":270.89999985694885,"domainLookupStart":270.89999985694885,"fetchStart":270.89999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":416.19999980926514,"responseEnd":526.0999999046326,"responseStart":501.2999999523163,"secureConnectionStart":270.89999985694885},{"duration":255,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":271.19999980926514,"connectEnd":271.19999980926514,"connectStart":271.19999980926514,"domainLookupEnd":271.19999980926514,"domainLookupStart":271.19999980926514,"fetchStart":271.19999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":416.39999985694885,"responseEnd":526.1999998092651,"responseStart":507,"secureConnectionStart":271.19999980926514},{"duration":112.79999995231628,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":271.39999985694885,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":271.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":384.19999980926514,"responseStart":0,"secureConnectionStart":0},{"duration":271,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":271.5,"connectEnd":271.5,"connectStart":271.5,"domainLookupEnd":271.5,"domainLookupStart":271.5,"fetchStart":271.5,"redirectEnd":0,"redirectStart":0,"requestStart":452.7999999523163,"responseEnd":542.5,"responseStart":527,"secureConnectionStart":271.5},{"duration":165,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":271.69999980926514,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":271.69999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":436.69999980926514,"responseStart":0,"secureConnectionStart":0},{"duration":287,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":271.7999999523163,"connectEnd":271.7999999523163,"connectStart":271.7999999523163,"domainLookupEnd":271.7999999523163,"domainLookupStart":271.7999999523163,"fetchStart":271.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":454,"responseEnd":558.7999999523163,"responseStart":527.5999999046326,"secureConnectionStart":271.7999999523163},{"duration":1394.4000000953674,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":272.69999980926514,"connectEnd":272.69999980926514,"connectStart":272.69999980926514,"domainLookupEnd":272.69999980926514,"domainLookupStart":272.69999980926514,"fetchStart":272.69999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":854.5,"responseEnd":1667.0999999046326,"responseStart":1652.2999999523163,"secureConnectionStart":272.69999980926514},{"duration":1367.3000001907349,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":301.19999980926514,"connectEnd":301.19999980926514,"connectStart":301.19999980926514,"domainLookupEnd":301.19999980926514,"domainLookupStart":301.19999980926514,"fetchStart":301.19999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":868.7999999523163,"responseEnd":1668.5,"responseStart":1652.7999999523163,"secureConnectionStart":301.19999980926514},{"duration":302.60000014305115,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1544.6999998092651,"connectEnd":1544.6999998092651,"connectStart":1544.6999998092651,"domainLookupEnd":1544.6999998092651,"domainLookupStart":1544.6999998092651,"fetchStart":1544.6999998092651,"redirectEnd":0,"redirectStart":0,"requestStart":1774.8999998569489,"responseEnd":1847.2999999523163,"responseStart":1813,"secureConnectionStart":1544.6999998092651}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":88,"responseStart":247,"responseEnd":302,"domLoading":252,"domInteractive":2268,"domContentLoadedEventStart":2268,"domContentLoadedEventEnd":2349,"domComplete":3555,"loadEventStart":3555,"loadEventEnd":3555,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":2210.0999999046326},{"name":"bigPipe.sidebar-id.end","time":2211.0999999046326},{"name":"bigPipe.activity-panel-pipe-id.start","time":2211.2999999523163},{"name":"bigPipe.activity-panel-pipe-id.end","time":2213},{"name":"activityTabFullyLoaded","time":2380.2999999523163}],"measures":[],"correlationId":"7f36382082b272","effectiveType":"4g","downlink":9.9,"rtt":0,"serverDuration":96,"dbReadsTimeInMs":14,"dbConnsTimeInMs":24,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Please see documentation about stored procedures
https://mariadb.com/kb/en/create-procedure/