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. MySQL Server stores account and privilege information in the grant tables of the mysql system database. 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 on the RDC.

On this page:


About MySQL account names

Each MySQL account name consists of a username and a client hostname in the format 'user_name'@'host_name'. 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 hostname. To grant different sets of privileges to a single user based on the client hosts from which the user connects, create separate 'user_name'@'host_name' entries for each username-hostname combination. For example:

Account name Description
'bkyloren'@'login%.bigred2.uits.iu.edu'
Allows user bkyloren to connect from any of the Big Red II login nodes (e.g., login1.bigred2.uits.iu.edu)
'bkyloren'@'rsip.bigred2.uits.iu.edu'
Allows user bkyloren to connect from Big Red II compute nodes, the hostnames of which are protected behind a realm-specific IP (RSIP) gateway
'bkyloren'@'%.karst.uits.iu.edu'
Allows user 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:

  • On the RDC, MySQL account user_name strings can be up to 16 characters long.
  • Quotes are necessary to specify a user_name string containing a special character (e.g., a space or -), or a host_name string containing a special or wildcard character (e.g., . or %). The user_name and host_name, if quoted, must be quoted separately (i.e., 'user_name@host_name' is invalid).
  • The user_name 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_name) associated with an account, you can use a host name 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.

For more, see the following sections of the MySQL 5.7 Reference Manual:

Add an account and set access privileges

To use the mysql client program on the RDC to add a new MySQL account and set access privileges for that account:

  1. Log into the RDC using whichever IU personal or group account credentials are associated with the RDC account to which the database account belongs:
      ssh username@rdc.uits.iu.edu
    
  2. On the RDC, log into your MySQL server instance:
      mysql --host=<host> --port=<port> -u <root_username> -p
    
  3. At the MySQL prompt, use a GRANT statement to create a new account entry and set its access privileges for a specific database:
        GRANT privilege_type ON db_name TO 'user_name'@'host_name' IDENTIFIED BY 'password';
    Note:
    RDC MySQL databases support implicit account creation, allowing you to create a new account with a GRANT statement (i.e., not requiring you to use a CREATE USER statement). However, this works only if you include the IDENTIFIED BY clause to specify a non-blank MySQL-native password. For more about the GRANT statement, see the GRANT Syntax section of the MySQL 5.7 Reference Manual.

    For example, the following statement creates an account that grants database-level privileges on my_db to username bkyloren connecting with password ih8luke from any of the login nodes on Karst:

      GRANT SELECT, INSERT, UPDATE, DELETE ON my_db.* TO 'bkyloren'@'%.karst.uits.iu.edu' IDENTIFIED BY 'ih8luke';

    If the command executes successfully, MySQL should respond with:

      Query OK, 0 rows affected (0.03 sec) 
    
  4. To make your changes take effect immediately, reload the grant table into memory; at the MySQL prompt, enter:
      FLUSH PRIVILEGES;

    MySQL should respond with:

      Query OK, 0 rows affected (0.04 sec)
    

Assign different privilege levels

In the example from the previous section, the GRANT statement gave the newly created account the following database privileges on the specified database:

  • SELECT: Permission to use SELECT statements to retrieve rows from existing tables
  • INSERT: Permission to use of INSERT statements to insert rows into existing tables
  • UPDATE: Permission to use UPDATE statements to update rows in existing tables
  • DELETE: Permission to use DELETE statements to delete rows from existing tables

Database privileges can be granted for a specific database (e.g., my_db.*) or for all databases on a given server (e.g., *.*). Database privileges apply to all objects within the specified database(s). Consequently, executing the GRANT statement in the example gives the specified user permission to select, insert, update, and delete records related to any object in the specified database. However, the user will not be able to create or drop tables, or (more importantly) create users and set privileges.

To assign table-level privileges instead, thereby restricting the newly created user's privileges to a particular table (e.g., this_table) within a database (e.g., my_db), use db_name.table_name syntax in your GRANT statement; for example:

  GRANT SELECT, INSERT, UPDATE, DELETE ON my_db.this_table TO 'user_name'@'host_name' IDENTIFIED BY 'password';

To assign column-level privileges, thereby restricting the new user's privileges to specific columns (e.g., column1 and column2) in a particular table (e.g., this_table) and, if desired, defining separate privileges for each column, add the columns (surrounded with parentheses) to your GRANT statement; for example:

  GRANT SELECT (column1), INSERT (column1,column2) ON my_db.this_table TO 'user_name'@'host_name' IDENTIFIED BY 'password';

The most secure policy is to grant users only the privileges necessary to allow them to perform their required tasks. For more on MySQL account management statements and privileges, see the following sections of the MySQL 5.7 Reference Manual:

Get help

For more about using MySQL Server, see the following sections of the MySQL 5.7 Reference Manual:

If you have questions, or need help connecting to or administering your RDC MySQL database, email the UITS High Performance Systems group.

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

  • Fill out this form to submit your issue to the UITS Support Center.
  • Please note that you must be affiliated with Indiana University to receive support.
  • All fields are required.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.

  • Fill out this form to submit your comment to the IU Knowledge Base.
  • If you are affiliated with Indiana University and need help with a computing problem, please use the I need help with a computing problem section above, or contact your campus Support Center.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.