Managing Users, Roles, and Privileges
MySQL 5.7 uses a system of roles to manage database permissions. Create users from both the UI and from MySQL Shell. Users who are created from the UI have nearly identical
privileges as admin, but cannot create other users. Since admin has both CREATE USER and GRANT options, it can create a user and give them all the privileges that it has, including the privilege to create
new users.
mysql> SELECT DISTINCT GRANTEE FROM information_schema.user_privileges;
+-----------------------------+
| GRANTEE |
+-----------------------------+
| 'ibm'@'localhost' |
| 'mysql.session'@'localhost' |
| 'mysql.sys'@'localhost' |
| 'ibm-backup'@'localhost' |
| 'admin'@'%' |
| 'ibm-replication'@'%' |
| 'ibm-monitor'@'%' |
+-----------------------------+
+-----------------+
| user |
+-----------------+
| admin |
| ibm-monitor |
| ibm-replication |
| ibm |
| ibm-backup |
| mysql.session |
| mysql.sys |
+-----------------+
The users below are maintained by IBM Cloud and shouldn't be altered or deleted by you:
| ibm-monitor |
| ibm-replication |
| ibm |
| ibm-backup |
When you provision a new deployment in IBM Cloud, you are automatically given an admin user to access and manage MySQL.
Add users in the UI in Service Credentials, with the Cloud Databases CLI plug-in, or the Cloud Databases API.
User management commands
For security reasons, we recommend that you do not run DML (Data Manipulation Language) queries on the mysql.user table. To protect against altering the mysql.user table, you should use DML queries to manage users.
Manage users by using commands such as CREATE USER, ALTER USER, RENAME USER, and DROP USER.
A list of users with their hosts information, but without auth information and password hashes, can be extracted by running the following command:
mysql> SELECT DISTINCT GRANTEE FROM information_schema.user_privileges;
The admin user
When you provision a new deployment in IBM Cloud, you are automatically given an admin user to access and manage MySQL. Once you set the admin password, use it to connect to your deployment.
When admin creates a resource in a database, like a table, admin owns that object. Users who are created from the UI have permissions to *.*, which means that any newly created user is able to see any database
automatically. Use MySQL sh, or modify permissions of a UI-created user to restrict access. To limit permissions, remove global privileges, if enabled, and grant privileges to a database, or database set, to which a given user is expected
to have access.
Set the admin password before using it to connect.
Setting the Admin Password in the UI
Set your Admin Password through the UI by selecting your instance from the Resource List in the IBM Cloud Dashboard. Then, select Settings. Next, select Change Database Admin Password.
Setting the Admin Password in the CLI
Use the cdb user-password command from the IBM Cloud CLI Cloud Databases plug-in to set the admin password.
For example, to set the admin password for a deployment named example-deployment, use the following command:
ibmcloud cdb user-password example-deployment admin <newpassword>
Setting the Admin Password in the API
The Foundation Endpoint that is shown on the Overview panel Deployment Details section of your service provides the base URL to access this deployment through the API. Use it with the Set specified user's password endpoint to set the admin password.
curl -X PATCH `https://api.{region}.databases.cloud.ibm.com/v5/ibm/deployments/{id}/users/admin` \
-H `Authorization: Bearer <>` \
-H `Content-Type: application/json` \
-d `{"password":"newrootpasswordsupersecure21"}` \
Other ibm Users
The ibm and the ibm-replication accounts are the only superusers on your deployment. A superuser account is not available for you to use. These users are internal administrative accounts that manage replication, metrics,
and other functions that ensure the stability of your deployment.
Users created with mysql
You can bypass creating users through IBM Cloud entirely, and create users directly in MySQL with mysql. This allows you to make use of MySQL's native role and user management.
Users/roles created in mysql must have all of their privileges set manually, as well as privileges to the objects that they create.
Users that are created directly in MySQL do not appear in Service Credentials, but you can add them if you choose.
Note that these users are not integrated with IAM controls, even if added to Service Credentials.
User access to tables
While you cannot delete mysql database, users can drop tables, including the mysql.users table that contains internal users. Clients shouldn't delete any table belonging to mysql database as this action
can result in a broken formation, which can only be resolved with a Point-in-time recovery (PITR).
IBM Cloud does not alert on formation breaking because of a system table dropped by a client.
More Users and Connection Strings
Access to your Databases for MySQL deployment is not limited to the admin user. You can create users by using the Service Credentials panel, the IBM CLI, or through the IBM Cloud Databases API.
All users on your deployment can use the connection strings, including connection strings for either public or private endpoints.
When you create a user, it is assigned certain database roles and privileges. These privileges include the ability to log in, create databases, and create other users. For more information, see the Managing Users, Roles, and Privileges page.
Creating Users in Service Credentials
- Navigate to the service dashboard for your service.
- Click Service Credentials to open the Service Credentials panel.
- Click **New Credential__.
- Choose a descriptive name for your new credential.
- (Optional) Specify whether the new credentials should use a public or private endpoint. Use either
{ "service-endpoints": "public" }/{ "service-endpoints": "private" }in the Add Inline Configuration Parameters field to generate connection strings using the specified endpoint. Use of the endpoint is not enforced. It just controls which hostnames are in the connection strings. Public endpoints are generated by default. - Click **Add__ to provision the new credentials. A username and password, and an associated database user in the MySQL database are auto-generated.
The new credentials appear in the table, and the connection strings are available as JSON in a click-to-copy field under View Credentials.
Creating Users from the command line
If you manage your service through the IBM Cloud CLI and the cloud databases plug-in, you can create a new user with cdb user-create. For example, to create a new user for
an "example-deployment", use the following command.
ibmcloud cdb user-create example-deployment <newusername> <newpassword>
Once the task finishes, you can retrieve the new user's connection strings with the ibmcloud cdb deployment-connections command.
Creating Users from the API
The Foundation Endpoint that is shown on the Overview panel Deployment details of your service provides the base URL to access this deployment through the API. To create and manage users, use the base URL with the
/users endpoint.
curl -X POST 'https://api.{region}.databases.cloud.ibm.com/v4/ibm/deployments/{id}/users' \
-H "Authorization: Bearer $APIKEY" \
-H "Content-Type: application/json" \
-d '{"username":"jane_smith", "password":"newsupersecurepassword"}'
Once the task has finished, you can retrieve the new user's connection strings, from the /users/{userid}/connections endpoint.
Adding users to Service Credentials
Creating a new user from the CLI doesn't automatically populate that user's connection strings into Service Credentials. If you want to add them there, you can create a new credential with the existing user information.
Enter the username and password in the JSON field Add Inline Configuration Parameters, or specify a file where the JSON information is stored. For example, putting {"existing_credentials":{"username":"Robert","password":"supersecure"}} in the field generates Service Credentials with the username "Robert" and password "supersecure" filled into connection strings.
Generating credentials from an existing user does not check for or create that user.