Menu
▾
▴
#4489 (ok 4.2.6) Incorrect detection of privileges for routine creation
As I updated to PMA 3.5.2 I cannot create new or edit stored prozedures (String displayed: "You do not have the necessary privileges to create a routine"). My user isn't the root user but have the CREATE ROUTINE privilege. I tested on the same server the PMA alternative solution "Chive" with the same user and there I can create new procedures, so it must be a problem in PMA only.
PMA-Version: 3.5.2
MySQL-Version: 5.5
Cannot reproduce your problem under 3.5.2 (but using MySQL 5.6.4).
Are you sure you are trying to create a routine in the same db on which you have the privilege?
Yes, lem9, I am sure. Maybe it is a MySQL Bug, I found that on the server there is the problem with it is MySQL 5.5.17 on another server I tested that behaviour I have MySQL 5.5.16 and there are no problems with PMA. And I found out, that Chive doesn't test if you can add procedures the GUI element is always there, only if you have no privilege to add them it throws an error message.
This results in that in Chive you can add a procedure and not in PMA.
I tested the problem I described with different PMA versions. My Result:
PMA 3.5.2: The message I described
PMA 3.5.1: The message I described
PMA 3.5.0: All normal; I can create procedures and trigger
(The Trigger tab is invisible too in 3.5.2 and 3.5.1)
In 3.5.0 I see the trigger tab, but didn't have the right for it... strange
The function PMA_currentUserHasPrivilege() from common.lib.php is used to establish if you have privileges for certain actions. PMA takes all information about privileges form the INFORMATION_SCHEMA db. So there might be a MySQL issue with that db.
Another possibility is that the user is not correctly recognised.
What is your username?
What does SELECT CURRENT_USER(); query return?
Is it your (correctly spelled) username?
Also, what is the name of the db you are working on?
> What does SELECT CURRENT_USER(); query return?
Result: pXXXXXX@172.XX.X.X/255.XXX.X.X
X = Digits that identifies the user the the IP. (I make them anonymous)
> Also, what is the name of the db you are working on?
Result: usr_pXXXXXX_2
I hope that helped you.
If I use this query: SELECT `PRIVILEGE_TYPE` FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES` I only get "USAGE" back without any SELECT or other rights.
I can't reproduce the issue at the moment, though I haven't quite finished digging.
Also, I was under the impression that these were db-specific privileges. How about the below query?
SELECT * FROM `INFORMATION_SCHEMA`.`SCHEMA_PRIVILEGES`
> Also, I was under the impression that these were db-specific privileges.
> How about the below query?
I attached the result in PDF format here.
Any updates on this bug?
i am having this exact same issue, and i tracked down what is wrong with PMA_currentUserHasPrivilege. if you are using a pretty standard setup where 'user' can work on tables named user_%, then the logic in PMA_currentUserHasPrivilege is wrong. it will work fine if you have a specific table privelege (no wildcards). the changes i made which seems to do the right thing looks like this:
3743 // If a database name was provided and user does not have the
3744 // required global privilege, try database-wise permissions.
3745 if ($db !== null) {
3746 // need to escape wildcards in db and table names, see bug #3518484
3747 //$db = str_replace(array('%', '_'), array('\%', '\_'), $db);
3748 //$query .= " AND TABLE_SCHEMA='%s'";
3749 $query .= " AND '%s' LIKE TABLE_SCHEMA";
3750 if (PMA_DBI_fetch_value(
3751 sprintf(
3752 $query,
3753 'SCHEMA_PRIVILEGES',
3754 $username,
3755 $priv,
3756 PMA_sqlAddSlashes($db)
3757 )
3758 )
3759 ) {
3760 return true;
3761 }
3762 } else {
3763 // There was no database name provided and the user
3764 // does not have the correct global privilege.
3765 return false;
3766 }
3767 // If a table name was also provided and we still didn't
3768 // find any valid privileges, try table-wise privileges.
3769 if ($tbl !== null) {
3770 // need to escape wildcards in db and table names, see bug #3518484
3771 //$tbl = str_replace(array('%', '_'), array('\%', '\_'), $tbl);
3772 //$query .= " AND TABLE_NAME='%s'";
3773 $query .= " AND '%s' LIKE TABLE_NAME";
3774 if ($retval = PMA_DBI_fetch_value(
3775 sprintf(
3776 $query,
3777 'TABLE_PRIVILEGES',
3778 $username,
3779 $priv,
3780 PMA_sqlAddSlashes($db),
3781 PMA_sqlAddSlashes($tbl)
3782 )
3783 )
3784 ) {
3785 return true;
3786 }
3787 }
note, that i have removed the insertion of backslashes since we are now using the db/table name on the left side of like. given how mysql is storing the info, using like in this fashion seems like the obvious thing to do, but maybe there are ramfications i'm not thinking about.
on a separate note it seems that phpmyadmin won't allow editing of a routine when the user doesn't have the create routine priv even though it seems that alter routine should be good enough. although i can't see why you'd have that setup, so i'm less worried about that.
anyway, hopefully this change is the right answer, it certainly is making my setup work correctly.
Hi q7joey,
with your changes the view is correct. I can now see the routines.
Should be in the next release...
Moved to Patches
Hi Joe Pruett,
Thank you for the patch.
As you noted in you comment, PMA_currentUserHasPrivilege() method will not work when the permissions have been granted on wildcards. And wildcards can be used on both database/table name and hostname/IP when granting.
I do not see a straight forward way of fixing this. So I'm in favor of removing the PMA_currentUserHasPrivilege() method altogether. Would you be able to submit a patch to remove the method?
looks like it didn't make it into 3.5.4. is something more needed?
Joe,
read Madhura's latest question in this thread.
oh, i didn't read that closely enough to see that it was pointed at me. i don't feel familiar enough with why the test was put in there in the first place to agree that removing it is the right choice. i will play around with hostname wildcards and see if there is an obvious way to deal with this.
i don't think that we need to worry about the host in these tests. mysql treats user@% as a separate entity than user@host, so privs for user@% don't apply to user@host. and the current_user() function returns the entry that mysql is using, which is what will be in the privileges table. so i think the simple change to use like on the db and table name will cover all cases.