[Maria-discuss] Missing rights on 'CREATE ALGORITHM=UNDEFINED DEFINER...'
Hello all,
I have a request from a user who can not create a view in his database. He has the following rights granted:
+----------------------------------------------------------------------------------------------------------------+ | Grants for user@% | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '***************' | | GRANT ALL PRIVILEGES ON `dbname`.* TO 'user'@'%' | +----------------------------------------------------------------------------------------------------------------+
The view he tries to create looks like these:
CREATE ALGORITHM=UNDEFINED DEFINER=`xxxxxxxxxxx`@`xx.xxx.xxx.x/xxx.xxx.xxx.x` SQL SECURITY DEFINER VIEW `jos_docman_file_counts` AS select `jos_docman_documents`.`storage_path` AS `storage_path`,count(0) AS `count` from `jos_docman_documents` where (`jos_docman_documents`.`storage_type` = 'file') group by `jos_docman_documents`.`storage_path`;
And gets as error:
#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
What SUPER privilege is needed for this view?
The Database is MariaDB 10.1 on Centos 7.
Thanks for any hint about his. Greetings, Thomas
Am 13.12.2018 um 11:09 schrieb Thomas Plant:
Hello all,
I have a request from a user who can not create a view in his database. He has the following rights granted: +----------------------------------------------------------------------------------------------------------------+ | Grants for user@% | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '***************' | | GRANT ALL PRIVILEGES ON `dbname`.* TO 'user'@'%' | +----------------------------------------------------------------------------------------------------------------+
The view he tries to create looks like these:
CREATE ALGORITHM=UNDEFINED DEFINER=`xxxxxxxxxxx`@`xx.xxx.xxx.x/xxx.xxx.xxx.x` SQL SECURITY DEFINER VIEW `jos_docman_file_counts` AS select `jos_docman_documents`.`storage_path` AS `storage_path`,count(0) AS `count` from `jos_docman_documents` where (`jos_docman_documents`.`storage_type` = 'file') group by `jos_docman_documents`.`storage_path`;
And gets as error:
#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
What SUPER privilege is needed for this view?
The Database is MariaDB 10.1 on Centos 7.
Thanks for any hint about his. Greetings, Thomas
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Found something with Google:
https://stackoverflow.com/questions/13492857/create-view-without-super-privi...
So I'd say the installation routine of the plugin is using the wrong user in the DEFINER statement?
All doesn't include mean all.
https://dba.stackexchange.com/questions/4118/does-mysql-super-privilege-incl...
The SUPER priv can be granted with GRANT SUPER ON *.* TO user1@localhost (The error implies this has changed in 10.1 and I'm not too up to date with it)
But probably better not to do that if you can avoid it because it allows a lot of other stuff too. The error is being caused by the DEFINER / SQL SECURITY clauses...
"If you do not have the SUPER privilege, the only legal user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account."
https://mariadb.com/kb/en/library/create-view/
The user can probably just remove this stuff if the defaults will work for them.
Rhys
From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net] On Behalf Of Thomas Plant Sent: 13 December 2018 11:10 To: maria-discuss@lists.launchpad.net Subject: [Maria-discuss] Missing rights on 'CREATE ALGORITHM=UNDEFINED DEFINER...'
Hello all,
I have a request from a user who can not create a view in his database. He has the following rights granted:
+----------------------------------------------------------------------------------------------------------------+
| Grants for user@% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '***************' |
| GRANT ALL PRIVILEGES ON `dbname`.* TO 'user'@'%' |
+----------------------------------------------------------------------------------------------------------------+
The view he tries to create looks like these:
CREATE ALGORITHM=UNDEFINED DEFINER=`xxxxxxxxxxx`@`xx.xxx.xxx.x/xxx.xxx.xxx.x` SQL SECURITY DEFINER
VIEW `jos_docman_file_counts` AS select `jos_docman_documents`.`storage_path` AS `storage_path`,count(0) AS `count`
from `jos_docman_documents` where (`jos_docman_documents`.`storage_type` = 'file')
group by `jos_docman_documents`.`storage_path`;
And gets as error:
#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
What SUPER privilege is needed for this view?
The Database is MariaDB 10.1 on Centos 7.
Thanks for any hint about his.
Greetings,
Thomas
Am 13.12.2018 um 11:32 schrieb Rhys.Campbell@swisscom.com:
All doesn't include mean all.
https://dba.stackexchange.com/questions/4118/does-mysql-super-privilege-incl...
The SUPER priv can be granted with GRANT SUPER ON *.* TO user1@localhost (The error implies this has changed in 10.1 and I'm not too up to date with it)
But probably better not to do that if you can avoid it because it allows a lot of other stuff too. The error is being caused by the DEFINER / SQL SECURITY clauses…
"If you do not have the SUPER privilege, the only legal user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account."
https://mariadb.com/kb/en/library/create-view/
The user can probably just remove this stuff if the defaults will work for them.
Rhys
*From:*Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net]*On Behalf Of *Thomas Plant *Sent:* 13 December 2018 11:10 *To:* maria-discuss@lists.launchpad.net *Subject:* [Maria-discuss] Missing rights on 'CREATE ALGORITHM=UNDEFINED DEFINER...'
Hello all,
I have a request from a user who can not create a view in his database. He has the following rights granted:
+----------------------------------------------------------------------------------------------------------------+ | Grants for user@% | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '***************' | | GRANT ALL PRIVILEGES ON `dbname`.* TO 'user'@'%' | +----------------------------------------------------------------------------------------------------------------+ The view he tries to create looks like these: CREATE ALGORITHM=UNDEFINED DEFINER=`xxxxxxxxxxx`@`xx.xxx.xxx.x/xxx.xxx.xxx.x` SQL SECURITY DEFINER VIEW `jos_docman_file_counts` AS select `jos_docman_documents`.`storage_path` AS `storage_path`,count(0) AS `count` from `jos_docman_documents` where (`jos_docman_documents`.`storage_type` = 'file') group by `jos_docman_documents`.`storage_path`; And gets as error: #1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation What SUPER privilege is needed for this view? The Database is MariaDB 10.1 on Centos 7. Thanks for any hint about his. Greetings, Thomas
Thank you for the answer. As this DB server is for our shared hosting giving the SUPER privilege to the user account is not an option I'd say. I think the customer will have to sort this out with the vendor of the Joomla Plugin....
Regards, Thomas
participants (2)
-
Rhys.Campbell＠swisscom.com -
Thomas Plant