MySql Create User script not working on Windows

user3763748 0
Viewed 24

I need to create a SQL script for configuring a couple of application accounts in MySQL on a Windows machine. I'm using the current MySQL version 8. release. I can create these accounts and grant permissions using the MySQL Workbench and the user is able to login using the workbench. If I use the following script, the user cannot log in...

DROP USER IF EXISTS testuser@localhost;
CREATE USER IF NOT EXISTS testuser@localhost IDENTIFIED BY 'Test@user1';
FLUSH PRIVILEGES;
GRANT INSERT, SHOW VIEW ON ehaw.MsgQueue TO testuser@localhost;
GRANT SELECT, SHOW VIEW ON ehaw.userMsgQueue TO testuser@localhost;
SHOW GRANTS FOR 'testuser'@localhost

When I try to connect using the MySQL Workbench with this account and password, I am presented with a "Cannot Connect to Database Server" error dialog.

Note that this user account will be used for "public" access through an application interface. I am explicitly trying to limit permissions on this account to only those required. The account needs to insert records in a specific table, select records from a specific view, and connect to the database...

mysql-workbench mysql
1 Answers

The long and short of it is that I did something stupid that caused me to incorrectly think I had a problem.

Having admitted this, the code sample I provided was badly done and I offer the following as a better example of least privilege management.

DROP USER IF EXISTS user@localhost;
CREATE USER IF NOT EXISTS user@localhost IDENTIFIED BY '$user1';
GRANT INSERT, SHOW VIEW ON schema.msgQueue TO user@localhost;
GRANT SELECT, SHOW VIEW ON schema.userMsgQueue TO user@localhost;
GRANT SELECT, SHOW VIEW on schema.eventMetadata to user@localhost;
SHOW GRANTS FOR 'user'@'localhost';

Lesson learned, case closed...

user37637480
Related