1

I am new to MySQL administration and hence this question - I wanted to create a MySQL admin user who wasn't the super-user. I assumed Amazon RDS MySQL community server will be a good template to work from. Hence I created a MySQL 'admin' user with exactly the same permissions as the RDS. This worked well until a fellow developer realized that this admin user is able to change the password of a user with super privileges! However, an admin user with exactly same permissions on Amazon RDS can't do this.

I wanted to understand what I am doing wrong AND what would be the right set of privileges for an admin user so that she can perform most database tasks and still not takeover the system completely.

Here are the grants of the admin user on Amazon RDS MySQL -

> select * from mysql.user where user='admin'\G;
*************************** 1. row ***************************
                  Host: %
                  User: admin
              Password: *C58D6BEE46C829269E211EB77AA2005DECC89CF1
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: N
          Process_priv: Y
             File_priv: N
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: N
 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: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string:
      password_expired: N

Here are the grants of the user I created on my deployment - 

> select * from mysql.user where user='admin'\G;
*************************** 1. row ***************************
                  Host: %
                  User: admin
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: N
          Process_priv: Y
             File_priv: N
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: N
 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: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *9E88C5A2B9C165CCF2599545998EDA0B3704AE53
      password_expired: N
 password_last_changed: 2018-05-18 13:52:35
     password_lifetime: NULL
        account_locked: N

Neither user can create a root user -

Amazon RDS:

> GRANT ALL PRIVILEGES ON *.* TO 'randomroot'@'localhost' IDENTIFIED BY '<pwd>' WITH GRANT OPTION;
ERROR: 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)

My MySQL deployment:

> GRANT ALL PRIVILEGES ON *.* TO 'randomroot'@'localhost' IDENTIFIED BY '<pwd>' WITH GRANT OPTION;
ERROR: 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)

However, my user is able to change a root user's pwd, while the AWS admin user isn't.

Amazon RDS:

> select * from mysql.user where user = 'rdsadmin'\G;
*************************** 1. row ***************************
                  Host: localhost
                  User: rdsadmin
              Password: *0679641C78FA0E03C1E8F152B87FA2F8CC8F72E7
           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
                plugin: mysql_native_password
 authentication_string:
      password_expired: N

Super user password change attempt:

SET PASSWORD FOR 'rdsadmin'@'localhost' = PASSWORD('<pwd>');
ERROR: 1396 (HY000): Operation SET PASSWORD failed for rdsadmin@localhost

My MySQL deployment:

> select * from mysql.user where user = 'randomroot'\G;
*************************** 1. row ***************************
                  Host: localhost
                  User: randomroot
           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
                plugin: mysql_native_password
 authentication_string: *C2AD957A98109BD742F98E4D97E69634578C82E0
      password_expired: N
 password_last_changed: 2018-05-28 14:21:59
     password_lifetime: NULL
        account_locked: N

Super user password change attempt:

> SET PASSWORD FOR 'randomroot'@'localhost' = '<newpwd>';
Query OK, 0 rows affected (0.2871 sec)

Edit - Add grants for the admin user in both the cases

Amazon RDS

> SHOW GRANTS FOR admin\G;
*************************** 1. row ***************************
Grants for admin@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD <secret> WITH GRANT OPTION
1 row in set (0.2238 sec)

For my MySQL deployment:

> SHOW GRANTS for admin\G;
*************************** 1. row ***************************
Grants for admin@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'admin'@'%' WITH GRANT OPTION
1 row in set (0.2538 sec)
Improve this question
2
  • Can you add te output of: "SHOW GRANTS FOR admin;" (Add the IP if you created the user with IP), for both installations please
    – Mauricio Cacho
    May 28, 2018 at 15:14
  • @MauricioCacho thanks for looking at it. I have added the GRANTS output to the question. Did not create the user with specific IPs.
    – Vaibhaw
    May 28, 2018 at 15:52

1 Answer 1

Reset to default
2

The problem is when you try to "replicate" your problems in Amazon RDS; since it's not exactly the same. With Amazon RDS you will never have SUPER privilege

From https://aws.amazon.com/es/premiumsupport/knowledge-center/rds-mysql-functions/:

"...the MySQL SUPER privilege, which is restricted for RDS MySQL DB instances."

So, when you GRANT "WITH GRANT OPTION" you're granting:

From https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_grant-option :

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

Bottom line, you should consider creating another MySQL instance for your testing purposes, so you can replicate problems with the same enviroment.

Improve this answer
1
  • 1
    Additionally worth noting, it is impossible to GRANT ALL PRIVILEGES in RDS, specifically, because you do not possess all privileges (even as the master user)... and in MySQL, "root" is not a magic username. It's just conventionally used as the super-privileged user in a default installation, but there is nothing that differentiates it from any other account with regard to privileges.
    – Michael - sqlbot
    May 28, 2018 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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