Mysql – Connecting to a new RDS instance from an EC2 instance

amazon ec2amazon-rdsMySQLpermissions

I'm trying to use MediaWiki and WP to connect to two MySQL databases.
The databases are working properly and so are the forms but
when I try to establish a connection to the database with MediaWiki or WP,
I just get a DB connection error: Permission denied.

Some facts:

I'm able to…

  • Connect to RDS by MySQL Workbrench to add/remove a users/table/Schema
  • Connect to RDS by ec2 Command-line ( mysql -u MYUSER -p -h ENDPOINT )

I'm NOT able to…

  • Check my server logs in MySQL Workbrench or Command-line Could not acquire management access for administration. Exception: Unsupported administration target type: ('windows', 'linux', 'wmi')
  • Remove rdsadmin or Revert the asingeded roles and privileges Error changing account rdsadmin@localhost: Access denied on rdsadmin@localhost
  • Grant DBA/MaintenenaceAdmin/ProcessAdmin or ReplicationAdmin to WPuser Error changing account WPuser@%: Access denied for user 'WPuser'@'%' (using password: YES)

What i have done so far:

  1. I added the Elastic/Private and even Public IP address for the EC2 instance to the VPC security group. as follows: xx.xx.xxx.xxx/32 and xxx.xx.xx.xxx/32. I'm Not useing EC2-Classic Platform, so there is no RDS security group as i'm seeing.
    enter image description here

  2. GRANT ALL ON%.* TO WPuser@%`; and GRANT index, create, select, insert, update, delete, drop, alter, lock tables on WIKIdb.* to
    'WIKIuser'@'ENDPOINT';

  3. See the following picture to have a over view of the rules
    enter image description here

Again, my questions are:

What i missed and why would be the solution to establish those applications to databases?

Also do you have anything in mind, how can i solve the "I'm NOT able to…" section to "I'm able to…" ?

I'd be grateful for any help on these

Best Answer

Check my server logs in MySQL Workbrench or Command-line Could not acquire management access for administration.

Right, you can't do that with workbench and RDS because that requires shell access, which RDS does not allow. The logs are accessible through the console and API.

Remove rdsadmin or Revert the asingeded roles and privileges

Right, you can't do that, because rdsadmin is the account that the RDS infrastructure uses to manage your instance. You don't control it.

Grant DBA/MaintenenaceAdmin/ProcessAdmin or Replication Admin to WPuser Error changing account WPuser@%: Access denied for user 'WPuser'@'%' (using password: YES)

The problem here is threefold.

The "roles" in workbench were a ridiculous idea on the part of Oracle because they do not correspond to real structures on MySQL... They're just fancy packaging for permission presets and you'd be better served to ignore that functionality and understand how the permissions actually work.

The DBA role doesn't work on RDS because that includes the SUPER privilege, which RDS does not provide, so that one wouldn't work anyway, even if you hadn't made the final error:

You appear to be trying to give "WPUser" permissions... while you're logged in as "WPUser." You can't grant permissions to yourself. You also can't grant permissions you don't hold. If WPUser is the master user for your RDS instance, it already has all the permissions it can have... though you shouldn't use your master user for your application's access to the database.

I added the Elastic/Private and even Public IP address for the EC2 instance to the VPC security group

That isn't likely the problem, here. If the security group isn't right, you'll fail to connect, after a lengthy timeout, and the error code will not be "access denied."

Also potentially incorrect, depending on what you are using as 'ENDPOINT':

GRANT ... ON WIKIdb.* to 'WIKIuser'@'ENDPOINT';

If 'ENDPOINT' is the RDS endpoint, this is incorrect. You can never connect to RDS from the RDS endpoint, or even from 'localhost'.

The final argument there needs to be the IP address of the server where the wiki code is running, or a wildcard for the netblock, like '172.31.%'.

Related Question