Create user with no privileges in RDS for MySQL fails, error 1396

Question

After going through all the motions to create a database instance on AWS using the RDS tool I've been able to access the database through MySQL Workbench. I've used the master username and password chosen on creation of the instance.

However I wish to create new users who have restricted privileges and can't seem to achieve this without returning an error.

As one can see I've tried it through workbench as well as gitbash and recieved the linked errors.

Here's an image of the interface. I've tried creating a user with zero privaleges and still the same error messages. I know the privilege SUPER can't be granted in RDS. Creating the user fails, without any grants.

The database I'm trying to create new users for is empty and I'm currently using the default VPC. Not sure if either of those affect this.

Any help is appreciated!

I haven't set up any EC2 instance or BD security group, not sure if that's also relevant (Even though I intend to setup in a VPC).

Answer 1

This wouldn't be related to security groups. Your RDS instance isn't actually aware of them -- they only control access to the host running the RDS instance at the TCP layer, in the AWS network.

Regarding your issue -- I think what you'll find is that you somewhere along the line actually did manage to create the user:

mysql> CREATE USER 'stackoverflow'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'stackoverflow'@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for 'stackoverflow'@'%'

mysql> CREATE USER 'stackoverflow';
ERROR 1396 (HY000): Operation CREATE USER failed for 'stackoverflow'@'%'

mysql>

To see the user accounts on the server SELECT * FROM mysql.user;.

Note that user 'stackoverflow' and 'stackoverflow'@'%' refer to the same user, where @'%' means the user's login privilege is not restricted by source IP address or host by MySQL (it can and will still be restricted by the security group settings, as I'll show in some more detail, below).

The mysql.user table contains all of the accounts the server knows about. This table (and several other grant tables) can be manipulated manually, but it's best to use the GRANT, REVOKE, CREATE USER, and DROP USER statements.

---

Potentially helpful later... if you do set up the security group in such a way that you can't connect to the server (because your source IP address isn't permitted) this will not cause any behavior change on the part of MySQL -- your connection will simply time out, never actually reaching the server, regardless of username and password.

It's a common error you'll see entirely too many people making (including here on SO) to start dinking around with permissions on the server, when they encounter this specific problem -- don't do that -- the problem is always one of network connectivity if you see exactly the following behavior:

$ mysql -h my-rds-with-security-group-too-restrictive.jozxyqk.us-west-2.rds.amazonaws.com

ERROR 2003 (HY000): Can't connect to MySQL server on 'my-rds-with...' (110)

ERROR 2003 can mean several things... but the money is at the end of the line. That code (110) at the end is telling you you're never reaching the server -- it's not a permissions issue, if you see this exact code code. Look it up:

$ perror 110
OS error code 110:  Connection timed out

(Linux error code 110 shown; Mac is probably the same code, since I suspect it's a standard POSIX error code, while Windows makes up their own. I believe it's 10060 on Windows that conveys the same meaning.)

Answer 2

Looks like a duplicate of this question: Creating A New MySQL User In Amazon RDS Environment

mysql -u [your_master_username] -p -h YOURRDSENDPOINT.rds.amazonaws.com

CREATE USER 'jeffrey'@'%' IDENTIFIED BY 'somepassword'; GRANT SELECT ON [your_database].[some_table] TO 'jeffrey'@'%';