Connecting to Xpand 

To connect to Xpand, use the mysql client to connect: 

shell> mysql -p db_name

By default, the MySQL client will use the current Unix user name to log in. To specify a different user, use the -u or --user option:

shell> mysql --user=clxm -p db_name

If you do not supply a password after the -p option, the client will prompt for one.

Creating and Managing Users

User information is stored in the system.users table in Xpand. To create users, issue the CREATE USER or GRANT commands, both of which require the CREATE USER privilege. Xpand does not permit blank usernames. 

sql> CREATE USER 'test_user'@'%' IDENTIFIED BY 'test_passwd'; 
sql> GRANT INSERT on test.* to 'test_user'@'client1' IDENTIFIED BY'test_passwd';

To change a user's login password, use the SET PASSWORD command:

sql> SET PASSWORD FOR test_user = PASSWORD('new_passwd');

Use DROP USER to remove a user.

SHA256 passwords

By default, passwords use mysql_native_password. To change an existing users’ password to use sha256 encryption:

sql> ALTER USER '[email protected]'%' IDENTIFIED WITH sha256_password BY 'foo';

To set it back to use mysql_native_password:

sql> ALTER USER 'seymour'@’%’ IDENTIFIED WITH mysql_native_password by 'foo';

Users with a password encrypted with SHA256 must use encrypted connections and a client that supports SHA256 (mysql 5.7+) to connect to Xpand. If a secure connection is not available, the user will encounter an error and be unable to connect.

Granting Privileges

Xpand supports an access control system that is similar to that of MySQL. You can grant privileges globally (using ON *.* ), at the database level ( ON <dbname>.*), or at the table level (ON <dbname>.<tablename>).

To modify privileges, use the GRANT and REVOKE commands, which differ from their MySQL equivalents as follows:

  • column_list and object_type are ignored.
  • The ssl options cipher issuer and subject are not supported

Xpand does not allow SYSTEM tables to be modified directly. Use SQL to modify users and privileges.

To display permissions, issue the SHOW GRANTS statement. For example, to list permissions for the current user:

sql> show grants;
| Grants for [email protected]                                  |

To show grants for a specific user:

sql> show grants for sergei;
| Grants for [email protected]%                                                                                   |

For a full list of the supported and unsupported privileges, see User Privileges.

Securing Initial Xpand Accounts

Xpand creates an inital 'root'@'' account. This is a superuser and if there is no password, any user can connect with no password and perform all operations. Xpand strongly recommends setting a root password.

To see which users may not have passwords:

sql> SELECT username, host, hex(password) from system.users;         

Default Users

As part of the installation process, by default Xpand creates the following user accounts:



'xpand'@'localhost'Runs the database process and is configurable as part of the installation. This user will not be created if Xpand is configured to run as root.
'xpandm'@'localhost'Used to manage the database and is configurable as part of the installation. Created with fewer privileges than clxd. This user will not be created if Xpand is configured to run as root. When using the Xpand AMI, this is 'clustrix'@'localhost'
'mysql_slave'Use by the Replication slave process. Login is not possible for this user regardless of whether a password is set.
'clx_maint'@''Used by Xpand Support and internal processes. 
'clx_view_definer'@''Used by Xpand Support and internal processes. 

These users must not be removed. 

Caveats for Managing Users

  • Xpand does not support:
    • netmask notation for IP addresses
    • directly modifying the users table. 
  • Xpand allows usernames and hostnames to be up to 256 characters long and will truncate names longer than the permitted length. Xpand recommends that usernames be 16 characters or shorter and hostnames be 60 characters or shorter. 
  • No labels