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.%'.
To be able to create mysql users, you need to be able to insert into the users table in the mysql database. So create your master user and give him write access to the mysql database.
The documentation states ( http://dev.mysql.com/doc/refman/5.7/en/create-user.html )
To use CREATE USER, you must have the global CREATE USER privilege or
the INSERT privilege for the mysql database. When the read_only system
variable is enabled, CREATE USER additionally requires the SUPER
privilege.
So the process would be something like this:
(root)> create user masteruser ...
(root)> grant CREATE_USER to masteruser ...
(masteruser)> create user foo1 ...
(masteruser)> create user foo2 ...
Also, it is deemed good practice to use a test system to test such behaviour; so that when you mess up, there is no harm done to a actual real database.
Best Answer
The intervals for MySQL grants comes with different levels.
You simply extract the grants from the mysql schema.
Problem is: From which level do you need the extraction ???
You will need the following query to show you global, database, and table grants
For MySQL 5.7.12, you will see this:
This should give you a good starting point.
You could probably create it as a table with something like this