Create user accounts for your MySQL database on the RDC at IU

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.

On this page:


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 "music", you could create the following logins:

Account name Description
'music_bkyloren'@'login%.bigred2.uits.iu.edu'
Allows user music_bkyloren to connect from any of the Big Red II login nodes (e.g., login1.bigred2.uits.iu.edu)
'music_bkyloren'@'rsip.bigred2.uits.iu.edu'
Allows user music_bkyloren to connect from Big Red II compute nodes, the hostnames of which are protected behind a realm-specific IP (RSIP) gateway
'music_bkyloren'@'%.karst.uits.iu.edu'
Allows user music_bkyloren to connect from any of the Karst login nodes (e.g., h2.karst.uits.iu.edu)

Consider the following when entering MySQL account names:

  • Quotes are necessary to specify a user string containing a special character (e.g., a space or -), or a host string containing a special or wildcard character (e.g., . or %). The user and host, if quoted, must be quoted separately (i.e., '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 (e.g., host) associated with an account, you can use a hostname or a public IP address (you should use whichever format is returned by your network's domain name server).
  • Users connecting from personal workstations (e.g., 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 IUPUI, 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 IT Pro.

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.createBasicUser
  • api.createReadOnlyUser
  • api.createUser
  • api.grant

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

API procedure Description
CALL api.createBasicUser('web','%',<password>,'jazz')
Creates a new login called music_web with the specified password, and grants it SELECT, INSERT, UPDATE, and DELETE privileges on the "jazz" schema.
CALL api.createReadOnlyUser('read','%',<password>,'jazz)
Creates a new login called music_read with the specified password, and grants it SELECT privileges on the "jazz" schema.
CALL api.createUser(('log','%',<password>)
Creates a new login called music_log with the specified password, and grants it the USAGE privilege on the "music" schemas.
CALL api.grant('music_log', '%', 'SELECT,INSERT', 'music_jazz', '*')
Grants the music_log user SELECT and INSERT privileges on all tables in the "jazz" schema.
Note:

To meet the minimum conditions required for a strong password, MySQL login passwords must contain at least:

  • Nine characters
  • Two uppercase letters
  • Two lowercase letters
  • Two numbers
  • Two of the following allowed special characters:
      ‘ ~ ! @ # $ % ^ & * ( ) _ - + = { } [ ] / < > , . ; ? ' : | (space)
    

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 5.7 Reference Manual, see the Privileges Provided by MySQL section.

Get help

If you have questions, or need help connecting to or administering your RDC MySQL database, email the RDC Administration team.

This is document adgk in the Knowledge Base.
Last modified on 2018-09-28 16:11:01.

Contact us

For help or to comment, email the UITS Support Center.