MySQL DROP DATABASE equivalent w/o root access or DROP privileges

Question

I am coordinating a number of PHP development projects. I have setup a workflow where developers all work with one Subversion repository. When they are ready to take a look at their application on staging servers upload a MySQL DB dump and create a tag.

I have configured a shell script that will automatically deploy the PHP code to the DocumentRoot and configure the proper permissions. I now need to script the loading of the database snapshot.

When I first configure a new application I create a database and user account like so:

CREATE DATABASE foodb;
GRANT ALL ON foodb.* TO user1@localhost IDENTIFIED BY 'password';

Now when the developers check a new version of the MySQL dump into Subversion I need to delete the old DB and restore a new snapshot. I'm currently using the following workflow.

1.) Login to MySQL as root

DROP DATABASE foodb;
CREATE DATABASE foodb;
GRANT ALL ON foodb.* TO user1@localhost IDENTIFIED BY 'password';

2.) Login to MySQL "user1"

mysql -h localhost -u user1 --password="drumitFed" foodb << new-db-dump-from-subversion.sql

I would like to combine steps 1 and 2 above. I imagine it would look something like this. The code should not only delete the tables but also views, indexes, and stored procedures.

note, that it has to run as the database user, not a superuser, and the database user does not have DROP DATABASE or CREATE DATABASE permissions.

mysql -h localhost -u user1 --password="password" foodb << delete-all-tables-and-views.sql
mysql -h localhost -u user1 --password="password" foodb << new-db-dump-from-subversion.sql

Answer 1

Your options are:

1. Run the scripts as root
2. Grant appropriate permissions to the database user(s), via role if possible.
3. Expose the ability for a user to supply a dump for a script, which gets run as root

A database isn't going to allow you to circumvent the access control.

Answer 2

You might be able to cheat somewhat.

If you can arrange it so that a script can run as root or the mysql user, you can have the script install the database with the proper database permissions and a "blank" schema setup.

shutdown mysql

copy -rp .../clean/* /var/lib/mysql/

startup mysql

So, the clean copy of the database has the created database and grant statements, but no data.

-daniel

Answer 3

First of all, superuser or not has no direct bearing here. mysql often has a "root" user, but that doesn't have to be associated with a root login.

Your question seems to be "how do I do X as a mysql user, and keep that user from having to have permission to do X", which leads to a "you can't" answer. So it seems there must be something else to your question. Why do you want to use the same user for everything?

Somehow, you need to have greater permissions to set up the environment. This could be done by having a separate mysql user, or a separate mysql server, or by temporarily shutting down the mysql server and running a separate instance of it with --skip-grant-tables for the duration of the setup.

Answer 4

You can grant global permissions to a user. This is not the most secure, but it does protect the root user password and if this is a system where dropping the database is a common operation then data integrity appears to be less of a concern.

You can use the --add-drop-database flag in mysqldump to drop the database before importing the dump:

--add-drop-database Add a 'DROP DATABASE' before each create.

Answer 5

I don't have time to test this, but try granting users the CREATE privilege MySQL wide, then the user who created each DB should have permissions to drop what they created, if not you could grant them drop permissions for that DB?

Answer 6

Why not do this?

mysql -h localhost -u root --password="xxx" foodb << delete-all-tables-and-views.sql
mysql -h localhost -u user1 --password="yyy" foodb << new-db-dump-from-subversion.sql

The script itself doesn't need to run as root. The MySQL "root" and the system's "root" are separate things.

Answer 7

You could use the information_schema to generates the SQL query that does the job. I don't know enough about the scripting solution you are using, so I can't suggest anything for that, but here's a sample SQL script that does everything from inside MySQL:

DELIMITER go

DROP PROCEDURE IF EXISTS p_drop_all_tables;
go

CREATE PROCEDURE p_drop_all_tables()
BEGIN
    DECLARE v_table_type VARCHAR(64);
    DECLARE v_table_name VARCHAR(64);
    DECLARE v_not_found TINYINT(1) DEFAULT FALSE;
    DECLARE csr_tables CURSOR FOR
        SELECT  CASE table_type
                    WHEN 'BASE TABLE' THEN 'TABLE'
                    ELSE table_type
                END table_type
        ,       table_name
        FROM    information_schema.TABLES
        WHERE   table_schema = SCHEMA()
        AND     table_type IN ('BASE TABLE', 'VIEW')
        ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND 
        SET v_not_found := TRUE;

    SET @@foreign_key_checks := OFF;
    OPEN csr_tables;
    table_loop: LOOP

        FETCH   csr_tables 
        INTO    v_table_type
        ,       v_table_name
        ;
        IF v_not_found THEN
            CLOSE csr_tables;
            LEAVE table_loop;
        END IF;

        SET @stmt := CONCAT(
            'DROP '
        ,   v_table_type
        ,   ' `', v_table_name, '`'
        );
        PREPARE stmt FROM @stmt;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;
    SET @@foreign_key_checks := ON;
END;
go

CALL p_drop_all_tables();
go

DROP PROCEDURE p_drop_all_tables;
go

DELIMITER;