Create user accounts for your MySQL database at IU RDC

• Overview
• About MySQL account names
• Add an account and set access privileges
• Get help

Overview

As the owner of a MySQL database on the Research Database Complex (RDC) at Indiana University, you are responsible for creating MySQL accounts and granting them the appropriate access privileges. Based on that information, the server authenticates users connecting from specific client hosts and determines which operations each user connection is permitted to perform.

Following are instructions for creating MySQL accounts and granting access privileges using the mysql client program and the administrative API procedures on the RDC.

About MySQL account names

All logins and schemas in the RDC are prefixed with the name of your RDC account. Each MySQL login consists of a user and a client host in the format 'prefix_user'@'host'. To allow a user to connect from multiple client hosts or multiple nodes on a single client host, use the % wildcard to replace all or part of a client host. To grant different sets of privileges to a single user based on the client hosts from which the user connects, create separate 'prefix_user'@'host' entries for each user-host combination.

For example, if you have an RDC account called "biolab", you could create the following logins:

Consider the following when entering MySQL account names:

• Quotes are necessary to specify a user string containing a special character (for example, a space or -), or a host string containing a special or wildcard character (for example, . or %). The user and host, if quoted, must be quoted separately ('user@host' is invalid).
• The user string and any associated password you create to grant access to your server instance is not related in any way to the user's IU username and passphrase, or to any login credentials used by Windows, macOS, or Linux systems.
• To indicate the client host (for example, host) associated with an account, you can use a hostname (for example, see Hostnames of IU research supercomputers) or a public IP address. You should use whichever format is returned by your network's domain name server.
• Users connecting from personal workstations (for example, via Aqua Data Studio or MySQL Workbench), must provide you with their public IP addresses or hostnames. (Users can find their public IP addresses by visiting the What Is My Public IP website.) Setting up accounts for users connecting from workstations with dynamically assigned IP addresses is possible but may be somewhat complex depending on your knowledge of network services.
• To accommodate users connecting from on-campus personal computers at IU Bloomington or IU Indianapolis, ask them to request static IP addresses, and then use those static IP addresses to create their MySQL accounts.
• To accommodate IU users connecting from off-campus networks, ask them to use IU's SSL VPN, and then use the appropriate IP range (using wildcards or a netmask) to create their MySQL accounts. For help determining the correct IP ranges for IU's SSL VPN and other campus networks, contact your department's local UITS support person.

Add a database login and set access privileges

You can use several administrative API procedures to create a new MySQL login and assign access privileges:

• api.createUserBasic
• api.createUserReadOnly
• api.createUser
• api.grant

The following examples illustrate how you would use these procedures to create new logins in your "biolab" account in the RDC and manage access to the "stats" schema:

• Create a new login called biolab_web with the specified password, and grant it SELECT, INSERT, UPDATE, and DELETE privileges on the "stats" schema:

  CALL api.createUserBasic('web','%','MyPa$w0rd5','stats',true,false)

• Create a new login called biolab_read with the specified password, and grant it SELECT privileges on the "stats" schema:

  CALL api.createUserReadOnly('read','%','MyPa$w0rd5','stats',true,false)

• Create a new login called biolab_log with the specified password, and grant it the USAGE privilege on all "biolab" schemas:

  CALL api.createUser(('log','%','MyPa$w0rd5',true,false)

• Grant the biolab_log user SELECT and INSERT privileges on all tables in the "stats" schema:

  CALL api.grant('biolab_log', '%', 'SELECT,INSERT', 'biolab_stats', '*')

The most secure policy is to grant users only the privileges necessary to let them perform their required tasks. For more on MySQL privileges, in the MySQL 8.0 Reference Manual, see the Privileges Provided by MySQL section.

Get help