2

I'm setting up an SQL user (setup process for new websites) which will need to GRANT the DROP command to another user (admin user (for authenticated admins), not the public-facing user) though is there a way to prevent the user from using the DROP command itself (through SQL)?

No, I can not use an existing administrator-permission SQL user as the setup process is public facing.

I've read that an SQL user can not GRANT permissions it does not have though this was listed on a third-party website and not Maria's documentation.

  • 1
    Being able to grant a privilege would be equivalent, in a practical sense, to possessing the privilege, because you could simply grant it to yourself. – Michael - sqlbot Sep 21 '16 at 20:47
0

True, you cannot use the GRANT command to give away grants you don't own as stated in the MySQL Documentation under the GRANT privilege:

The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess.

The only alternative would be to create a user that have the power to the following:

INSERT IGNORE INTO mysql.db SET
    db = '...',
    user = '...',
    host = '...'
;
UPDATE mysql.db SET Drop_priv = 'Y' WHERE ... ;`
FLUSH PRIVILEGES;`

The INSERT IGNORE adds the initial entry into mysql.db in case it did not already exist. Then, the UPDATE sets the drop_priv column to 'Y'. FLUSH PRIVILEGES must be done manually is needed since no GRANT command is ever executed (GRANT always executes FLUSH PRIVILEGES; automatically)

Create a Stored Procedure that accepts a database, user and host and executes these commands.

For the sake of example, suppose the following

  • Database is mydb
  • User is 'john'@'10.20.30.40

The commands to run would be

INSERT IGNORE INTO mysql.db SET
    db = 'mydb',
    user = 'john',
    host = '10.20.30.40'
;
UPDATE mysql.db SET Drop_priv='Y' WHERE db='mydb' AND user='john' AND host='10.20.30.40';
FLUSH PRIVILEGES;

Place these commands in a stored procedure

First, login as root@localhost, then run the following:

USE mydb
DROP PROCEDURE IF EXISTS GiveAwayDrop;
DELIMITER $$
CREATE PROCEDURE GiveAwayDrop (givenDB VARCHAR(64),usr VARCHAR(64),hst VARCHAR(64))
BEGIN
    INSERT IGNORE INTO mysql.db SET
        db   = givenDB,
        user = usr,
        host = hst
    ;
    UPDATE mysql.db SET Drop_priv='Y' WHERE db=givenDB AND user=usr AND host=hst;
    FLUSH PRIVILEGES;
END $$
DELIMITER ;

You must give away the EXECUTE grant to the user, so that user can run stored procedures:

GRANT EXECUTE on mydb.* TO 'john'@'10.20.30.40';

See my old post how to GRANT EXECUTE on MySQL for further clarification.

Then, user 'john'@'10.20.30.40' can execute

CALL mydb.GiveAwayDrop('givendb','fred','10.20.%'); 

to give out the DROP privilege to 'fred'@'10.20.%'. This should work if givendb database exists and 'fred'@'10.20.%' already exists in mysql.user.

GIVE IT A TRY !!!

  • That looks great though can you please state what you're doing conceptually/English first (edit it in or comment please)? I can't learn/understand new code if I don't understand the concept it's associated with. My best guess is that you're trying to limit the DROP command to only be triggered if the host is a certain IP but who is the host? The server? The client? Obviously it's only part of the whole concept. – John Sep 22 '16 at 13:49

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.