Mysql : user with privileges to create databases, users and grant all privileges on those dbs to users

MySQL

Is it possible in mysql to create user who is able to create database, user and grant all privileges on this database to that user?I dont want to use root user in script

Best Answer

There is nothing special about "root".

CREATE USER foo@localhost ...;   -- establish the existence and pwd of the user
GRANT CREATE DATABASE     -- so foo can do CREATE DATABASE
      ...                 -- and other things (as needed)
    ON *.*                -- probably have to give permission at this level
    TO foo@localhost      -- the user
    WITH GRANT OPTION;    -- so foo can do GRANTs

See also SUPER -- decide whether or not you want this non-user to have all the privileges it entails.

For security reasons, only @localhost should be used with WITH GRANT OPTION.

STEP #1

Add these lines just under the [mysqld] group header in my.ini

[mysqld]
skip-grant-tables
skip-networking

STEP 2

Open Windows Command Line as Administrator, and reboot MySQL Service

C:\> net stop mysql
C:\> net start mysql

STEP 3

You can login to MySQL

C:\> mysql <Hit Enter>

STEP 4

From the mysql prompt, run this

SET @PasswordHash = PASSWORD('whateverpasswordiwant');
REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = 'localhost',
              Password = @PasswordHash,
             Select_priv = 'Y',
             Insert_priv = 'Y',
             Update_priv = 'Y',
             Delete_priv = 'Y',
             Create_priv = 'Y',
               Drop_priv = 'Y',
             Reload_priv = 'Y',
           Shutdown_priv = 'Y',
            Process_priv = 'Y',
               File_priv = 'Y',
              Grant_priv = 'Y',
         References_priv = 'Y',
              Index_priv = 'Y',
              Alter_priv = 'Y',
            Show_db_priv = 'Y',
              Super_priv = 'Y',
   Create_tmp_table_priv = 'Y',
        Lock_tables_priv = 'Y',
            Execute_priv = 'Y',
         Repl_slave_priv = 'Y',
        Repl_client_priv = 'Y',
        Create_view_priv = 'Y',
          Show_view_priv = 'Y',
     Create_routine_priv = 'Y',
      Alter_routine_priv = 'Y',
        Create_user_priv = 'Y',
              Event_priv = 'Y',
            Trigger_priv = 'Y',
  Create_tablespace_priv = 'Y',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;
REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = '127.0.0.1',
              Password = @PasswordHash,
             Select_priv = 'Y',
             Insert_priv = 'Y',
             Update_priv = 'Y',
             Delete_priv = 'Y',
             Create_priv = 'Y',
               Drop_priv = 'Y',
             Reload_priv = 'Y',
           Shutdown_priv = 'Y',
            Process_priv = 'Y',
               File_priv = 'Y',
              Grant_priv = 'Y',
         References_priv = 'Y',
              Index_priv = 'Y',
              Alter_priv = 'Y',
            Show_db_priv = 'Y',
              Super_priv = 'Y',
   Create_tmp_table_priv = 'Y',
        Lock_tables_priv = 'Y',
            Execute_priv = 'Y',
         Repl_slave_priv = 'Y',
        Repl_client_priv = 'Y',
        Create_view_priv = 'Y',
          Show_view_priv = 'Y',
     Create_routine_priv = 'Y',
      Alter_routine_priv = 'Y',
        Create_user_priv = 'Y',
              Event_priv = 'Y',
            Trigger_priv = 'Y',
  Create_tablespace_priv = 'Y',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;
exit

STEP 5

Remove skip-grant-tables and skip-networking from my.ini

STEP 6

Restart MySQL

C:\> net stop mysql
C:\> net start mysql

STEP 7

Try logging into MySQL

GIVE IT A TRY !!!

MySQL – Grant All Privileges Except ‘Create User’

This works:

As root:

mysql> CREATE USER 'myuser'@'%' IDENTIFIED BY 'billy';
Query OK, 0 rows affected (0.04 sec)

Then, on the sport schema, grant ALL to myuser

mysql> GRANT ALL ON sport.* TO 'myuser'@'%';
Query OK, 0 rows affected (0.02 sec)

Exit and then log on as the new user myuser

mysql> exit;
Bye
[pol@localhost inst]$ ./bin/mysql -S ./mysql.sock -u myuser -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.22-log Source distribution

Show all the schemas myuser can see

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sport              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

There are many more schemas on this server - it's just that myuser can't see them.

mysql> use sport;
Database changed

Then, try to create a user as myuser.

mysql> create user 'fred'@'%' IDENTIFIED BY 'mary';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

Fails.

But, I can do this.

mysql> create table test (a int, b varchar(10));
Query OK, 0 rows affected (0.53 sec)

mysql> insert into test values(3, 'testvalue');
Query OK, 1 row affected (0.04 sec)

Take a look at the three answers here, here and here for a better idea of what's going on. Always test your users after creation - it's very easy to give users too much power.

You can also do it this way (a more elegant solution perhaps)

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 
    INDEX, ALTER, CREATE TEMPORARY TABLES 
ON sport.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Finally, you could just remove the CREATE USER privilege (and, of course, the WITH GRANT OPTION) from this answer to have a "super user" who can do everything except create new users.

I think @sqlbot may have figured out the root of your problem.