See also:

Migrating User Permissions

Replicating User Account Management Statements

Connecting to ClustrixDB 

To connect to ClustrixDB, 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 ClustrixDB. To create users, issue the CREATE USER or GRANT commands, both of which require the CREATE USER privilege. ClustrixDB 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 ClustrixDB. If a secure connection is not available, the user will encounter an error and be unable to connect.

Granting Privileges

ClustrixDB 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:

ClustrixDB 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]                                  |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION | 
+-------------------------------------------------------------+

To show grants for a specific user:

sql> show grants for sergei;
+-------------------------------------------------------------------------------------------------------+
| Grants for [email protected]%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sergei'@'%' IDENTIFIED BY PASSWORD '*F3A2A51A9B0FXXXXXXXXXXXXX32313728C250DBF' | 
+-------------------------------------------------------------------------------------------------------+

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

Securing Initial ClustrixDB Accounts

ClustrixDB creates an inital 'root'@'127.0.0.1' account. This is a superuser and if there is no password, any user can connect with no password and perform all operations. Clustrix 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 ClustrixDB creates the following user accounts:

UserPrivileges

'root'@'127.0.0.1'

 
'clxd'@'localhost'Runs the database process and is configurable as part of the installation. This user will not be created if ClustrixDB is configured to run as root.
'clxm'@'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 ClustrixDB is configured to run as root. When using the ClustrixDB 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'@'127.0.0.1'Used by Clustrix Support and internal processes. 
'clx_view_definer'@'127.0.0.1'Used by Clustrix Support and internal processes. 

These users must not be removed. 

Caveats for Managing Users