MySQL – admin user is able to change password of the super user

amazon-rdsauthenticationMySQLpermissions

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)

Best Answer

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.

Related Solutions

Mysql – thesql server password change while restoring

According to me you have also dumped the mysql database which contains the users privileges in the users table of mysql database. & one more-thing you may have used --add-drop-database or --add-drop-table while generating the dump

i.e mysqldump -u user -p passwordd --add-drop-database --all-databases > /home/dump.sql

and when you have restored it on your local machine as your dump contains the drop table statements.so it had firstly dropped your user table in mysql database and then restored new one from the dump thats why you are facing the problem. so don't include the mysql database while generating the dump.

please see various option of mysqldump using mysqldump --help.

Thanks....

MySQL error: Access denied for user ‘a’@’localhost’ (using password: YES)

Here is a quick-and-dirty method for checking out how MySQL performs successful authentication.

Please run this query:

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in mysqld

CURRENT_USER() reports how you were allowed to authenticate by mysqld

Sometimes, USER() and CURRENT_USER() are different. That's because mysql authentication follows a specfic protocol.

According to MySQL 5.0 Certification Study Guide

enter image description here

pages 486,487 state the following on mysql's authentication algorithm:

There are two stages of client access control:

In the first stage, a client attempts to connect and the server either accepts or rejects the connection. For the attempt to succeed, some entry in the user table must match the host from which the client connects, the username, and the password.

In the second stage (which occurs only if a client has already connected sucessfully), the server checks every query it receives from the client to see whether the client has sufficient privileges to execute it.

The server matches a client against entries in the grant tables based on the host from which the client connects and the user the client provides. However, it's possible for more than one record to match:

Host values in grant tables may be specified as patterns contains wildcard values. If a grant table contains entries from myhost.example.com, %.example.com, %.com, and %, all of them match a client who connects from myhost.example.com.

Patterns are not allowed for the User values in grant table entries, but a username may be given as an empty string to specify an anonymous user. The empty string matches any username and thus effectively acts as a wildcard.

When the Host and the User values in more than one user table record match a client, the server must decide which one to use. It does this by sorting records with the most specific Host and User column values first, and choosing the matching record that occurs first in the sorted list, Sorting take place as follows:

In the Host Column, literal values such as localhost, 127.0.0.1, and myhost.example.com sort ahead of values such as %.example.com that have pattern characters in them. Pattern values are sorted according to how specific they are. For example, %.example.com is more specific than %.com, which is more specific than %.

In the User column, non-blank usernames sort ahead of blank usernames. That is, non-anonymous users sort ahead of anonymous users.

The server performs this sorting when it starts. It reads the grant tables into memory, sorts them, and uses the in-memory copies for access control.

From this description, you do not need to worry about the order of the mysql.user tables since there is an in-memory copy of the grant tables which is sorted as previously mentioned.

With regard to how you logged in, only mysql -u a worked. Go back and login again and run these commands

SELECT USER(),CURRENT_USER();
SELECT user,host,password FROM mysql.user;

Make sure that

every user has a password.
there are no anonymous users (when user is blank)

This is just a guess, but I suspect mysql -u a of connecting via localhost because when the connection protocol is not specified, the default is to connect via the socket file. There may exist an entry in mysql.user that allow anonymous localhost connection.

Run this query:

SELECT user,host,password FROM mysql.user WHERE user='' AND host='localhost';

If you get back a row with no password, that fully explains why mysq -u a works.

UPDATE 2012-01-19 11:12 EDT

Craig Efrein brought up an interesting question: if two identical usernames exist in the mysql.user table, one with a password and one without, does that mean that MySQL denies authentication when not using a password?

This question is an excellent heads up about MySQL user authentication.

Please note that the primary key of mysql.user is host,user. There are no other indexes. This allows multiple occurrences of a username. Each occurrence can have a different password or no password. This allows user 'dbuser' to login locally (dbuser@localhost) using no password and the same user login from another server within a given netblock (dbuser@'10.1.2.20') with a password like 'pass1' and that user to login remotely from anywhere (dbuser@'%') with a remote password like 'pass2'.

Given the authentication algorithm that MySQL uses, there are no restrictions placed on users with the presence or absense of a password.

This is why MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints brings out how to cleanup the authentication process:

On Unix, MySQL comes with a mysql_secure_installation script that can perform several helpful security-related operations on your installation. The script has the following capabilities:

Set a password for the root accounts

Remove any remotely accessible root accounts.

Remove the anonymous user accounts. This improves security because it prevents the possibility of anyone connecting to the MySQL server as root from a remote host. The results is that anyone who wants to connect as root must first be able to log in on the server host, which provides an additional barrier against attack.

Remove the test database (If you remove the anonymous accounts, you might also want to remove the test database to which they have access).

Best Answer

Related Solutions

Mysql – thesql server password change while restoring

MySQL error: Access denied for user ‘a’@’localhost’ (using password: YES)

UPDATE 2012-01-19 11:12 EDT

Related Question