2

I have a MySQL user (rundeck) that is being used as part of a deployment process. Its purpose is to create a new database, create a new user, and grant permissions to the new user for the new database. The problem is that I can't seem to find a granular permission for the user that allows granting permissions without also exposing the data in the database. If I run with any permissions other than GRANT and ALL, I get the following output:

mysql> GRANT ALL ON `mydatabase`.* TO ''@'192.168.101.%';
ERROR 1044 (42000): Access denied for user 'rundeck'@'hostname.com' to database 'mydatabase'

I really would like to restrict the rundeck user from accessing the data, but can't get GRANT to work without also using ALL. If I use both together, it works perfectly, unsurprisingly. We suspect that the mydatabase.* may be the element requiring additional permissions. Can anyone suggest a group of permissions that will allow a user to grant permissions to a database that they cannot access the data to?

SHOW GRANTS output from working configuration:

GRANT ALL PRIVILEGES ON . TO 'rundeck'@'hostname.com' IDENTIFIED BY PASSWORD 'passhash' WITH GRANT OPTION

SHOW GRANTS output from non-working configuration:

GRANT CREATE, DROP, RELOAD, SHOW DATABASES, CREATE USER ON . TO 'rundeck'@'hostname.com' IDENTIFIED BY PASSWORD 'passhash' WITH GRANT OPTION

  • I originally posted this at stackoverflow.com/questions/19324924/… (where it didn't really get any traction, it is now deleted), before I was aware of this site. Hopefully it is more appropriate here. – Brad Werth Sep 4 '14 at 17:33
  • 1
    Run SHOW GRANTS FOR 'rundeck'@'hostname.com'; and post the output (hide password) – RolandoMySQLDBA Sep 4 '14 at 17:41
  • @RolandoMySQLDBA - Thanks, added. FWIW, the other operations in the list perform as desired. – Brad Werth Sep 4 '14 at 18:52
2

The fundamental problem you have is this:

You cannot grant another user a privilege which you yourself do not have; the GRANT OPTION privilege enables you to assign only those privileges which you yourself possess.

http://dev.mysql.com/doc/refman/5.6/en/grant.html

This makes sense, since if it were otherwise, then you could just grant privileges to yourself.

There is one way around this, depending on how much flexibility you have with the calling code -- stored procedures can run with the credentials of the defining user (as opposed to the invoking user). If the calling system could call a procedure instead of using GRANT ..., then you can create a procedure that is executable by the restricted user, granting other privileges as appropriate.

  • Interesting... I assume there is no way to get to something like GRANT GRANT TO 'rundeck'@'hostname.com' IDENTIFIED BY PASSWORD 'passhash' WITH GRANT OPTION? – Brad Werth Sep 4 '14 at 20:22
  • 1
    No, that's what WITH GRANT OPTION does. Allowing a user to grant privileges is only one step removed from giving that user the privilege, so you wouldn't really gain any real security if what you were trying were possible. If it were, and that account were compromised, it could just be used to give privileges to a different account, and the data would still be exposed. – Michael - sqlbot Sep 4 '14 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.