About the "drop user root'@'%';" command

Help
1

Hi,

I Executed the “drop user root’@’%’;” command.
After that, I can’t access the root.
Also, some sdb-admin commands fail.

Please what should I do?

I think anyone can make the same mistake.

Please advise.

Thank you in advance.

2

Hi,

You can set the start up only system variable insecure_accept_any_password_for_all_users (use tools to set it). Once that is set you can log in as the distributed user and CREATE USER or GRANT to create a root user again. Remember to remove the insecure_accept_any_password_for_all_users system variable when your done.

-Adam

3

Hi, adam
I want to know how to set ‘security_accept_any_password_for_all_users’ variable.
Thank you.

4

Thanks for the reply!!

I have tried testing, but haven’t solved it yet.

I added the below setting to memsqlctl.hcl, toolbox.hcl, toolbox-cache.hcl toolbox-state.hcl, memsql.cnf files and restarted MA.

   `insecure_accept_any_password_for_all_users = "ON"`

Please let me know in more detail.

Thanks again in advance.

5

The best way to set a config. variable is via:

Once you set insecure_accept_any_password_for_all_users and restart the master aggregator you can login as the distributed user and that user will let you recreate root. For example:

mysql -u distributed -h <master agg host here> --port <master agg port here>

MySQL [(none)]> GRANT ALL ON *.* TO ROOT@'%' WITH GRANT OPTION;

6

Hi,

I tried the way you told me, but it doesn’t work.
The following error has occurred in the settings.

[memsql@localhost tracelogs]$ sdb-admin update-config --key insecure_accept_any_password_for_all_users --value on --set-global -y -a
Toolbox is about to run 'memsqlctl update-config --key insecure_accept_any_password_for_all_users --value on --set-global' on the following nodes:
    - On host 127.0.0.1:
      + 9A339CEC4F5C3216BFAF34F16FACC691037DAB51
      + 2483E458DF3D752683AE1A87BD24EA2FB0252B80

Would you like to continue? [Y/n]:
Automatically selected yes, non-interactive mode enabled

✘ Failed to update configuration on 127.0.0.1
error running memsqlctl: error running command: `/usr/bin/memsqlctl --json --yes update-config --memsql-id 9A339CEC4F5C3216BFAF34F16FACC691037DAB51,2483E458DF3D752683AE1A87BD24EA2FB0252B80 --key insecure_accept_any_password_for_all_users --value on --set-global`: exit status 1:
stdout: (empty)
**stderr: Error 1045: Access denied for user 'root'@'localhost' (using password: YES)**

After restarting the MA and executing the mysql command, the same error “…Access denied…” occurs.

If there is no way,
I think there is a need for improvement so that it can be restored in the way you said.

Thank you.

7

Looks like without a root user you can’t set a system variable via sdb-admin. You’ll have to do it manually. That means editing the memsql.cnf file on each node you want to fix the root user on. Add one line to the file:

insecure_accept_any_password_for_all_users = true

Restart the node and then follow the rest of the instructions (restart the node, log in as distributed and GRANT ALL to root).

8

Thanks for your reply.

But it still hasn’t been resolved.
This is being tested, so don’t worry.

This problem can be reproduced simply. (Deploy cluster_in_a_box -> drop user root -> recovery …)
I think this is a bug and I hope you inform the tech department.

Thank you.

sdb-admin start-node --yes --all
Toolbox is about to perform the following actions:
  · Start all nodes in the cluster

Would you like to continue? [Y/n]:
Automatically selected yes, non-interactive mode enabled

✓ Successfully connected to host 127.0.0.1
✘ Failed to start Leaf node on 127.0.0.1 (1/1)
error running memsqlctl: error running command: `/usr/bin/memsqlctl --json --yes start-node --memsql-id 2483E458DF3D752683AE1A87BD24EA2FB0252B80`: exit status 1:
stdout: (empty)
stderr: MemSQL config values failed to validate: 2021-04-11 21:28:32.562   INFO: ./memsqld: initializing
2021-04-11 21:28:32.601   INFO: ./memsqld: initializing
2021-04-11 21:28:32.626   INFO: Skipping setuid because we are already user '1000' (uid 1000, gid 1000)
**ERROR: "insecure_accept_any_password_for_all_users" cannot be defined in the memsql.cnf. It can only be used as a command line parameter.**
2021-04-11 21:28:32.626  ERROR: Could not load config file.
2021-04-11 21:28:32.626  ERROR: ./memsqld: exiting
: error running command: `/opt/memsql-server-7.3.7-5f852cbfcf/memsqld --defaults-file /var/lib/memsql/474a343a-b895-4cf0-a6af-7e8e16095ed6/memsql.cnf --user 1000 --help`: exit status 1
9

Sorry about that. I didn’t know insecure_accept_any_password_for_all_users wasn’t allowed to be set in memsql.cnf. Its only allowed as a startup parameter to memsqld. We need a better documented way of doing this.

I’m trying to find out if there is a way of setting a startup parameter this via sdb-admin. The only way I know is to start memsqld directly and memsqld --insecure_accept_any_password_for_all_users --defaults-file <path to memsql.cnf file>

1 Like