Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Sv translation
languageen


Section


Column

Table of Contents


Column


Panel

See also:

Migrating User Permissions

Replicating User Account Management Statements



Pre-configured Users

Warning

Do not modify these pre-configured users. You may cause unspecified behavior in the database.

ClustrixDB comes with pre-configured users in the system.users table as follows:

  • root is defined to establish initial root access to ClustrixDB.
  • clustrix_ui user is used internally by ClustrixGUI. 
  • mysql_slave user is used by the slave processes running on the cluster. 
  • clx_supportclx_maint, and clx_view_definer are reserved exclusively for use by Customer Support.

Default root Account

ClustrixDB automatically creates a root user account. This root user is root@'%' and it allows root logins from anywhere. This differs from MySQL, which creates root@localhost.

Info

Root users are not included when importing users using clustrix_clone_users.

This difference in default root users between MySQL and ClustrixDB can lead to issues when importing triggers.  If a trigger happened to be created by the default root@localhost user on MySQL, it may import but then fail to execute on ClustrixDB, with an error like:

ERROR 1449 (HY000): [11309] The user specified as the definer for the trigger

This is because the trigger definition has DEFINER=`root`@`localhost` and ClustrixDB does not map this to the root@'%' user. The simplest way to work around this is to create a root@localhost user on ClustrixDB.

Creating and Managing Users

User information is stored in the system.users table in ClustrixDB, versus the mysql.user table in MySQL. To create users, issue the CREATE USER or GRANT commands, both of which require the CREATE USER privilege.

For example:

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');

To remove a user from the database, use the DROP USER command.

You can qualify ClustrixDB user names by specifying CIDR, subnet mask, SQL wildcards, and FQDN qualifiers.

For example:

'user'@'10.21.2.0/24'
'user'@'10.21.0.0/16'
'user'@'10.0.0.0/255.0.0.0'
'user'@'10.21.2.0/255.255.255.0'
'user'@'10.21%'
'user'@'10.%'
'user'@'%.%.2.55'
'user'@'%.%.%.com'
'user'@'%.colo.%.%'
'user'@'client1.____.com'
'user'@'%.c%.%s.c__'
'user'@'client1.clustrix.com'
'user'@'10.21.2.64'

 

Note

ClustrixDB does not support creating or modifying user data via the mysql.users table.

Authentication Plugins

ClustrixDB supports the mysql_native_password and sha256_password authentication plugins. For compatibility purposes, ClustrixDB provides read-only support for the secure_auth global variable, but the value is always 1. 

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:

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

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

sql> show grants;
+-------------------------------------------------------------+
| Grants for root@10.2.2.243                                  |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION | 
+-------------------------------------------------------------+
1 row in set (0.00 sec)

To list permissions for a specific individual:

sql> show grants for sergei;
+-------------------------------------------------------------------------------------------------------+
| Grants for sergei@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sergei'@'%' IDENTIFIED BY PASSWORD '*F3A2A51A9B0FXXXXXXXXXXXXX32313728C250DBF' | 
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Supported Privileges

ClustrixDB supports the following privileges:

  • ALL [PRIVILEGES]: All Privileges with the exception of GRANT option
  • ALTER: Allow use of ALTER TABLE
  • CREATE: Allow CREATE TABLE and CREATE DATABASE
  • CREATE USER: Allow CREATE USER and DROP USER
  • CREATE VIEW: Allow CREATE VIEW
  • DELETE: Allow DELETE
  • DROP: Allow DROP TABLE, DROP DATABASE, and DROP VIEW
  • GRANT OPTION: Allow GRANT
  • INDEX: Allow CREATE INDEX and DROP INDEX
  • INSERT: Allow INSERT
  • REPLICATION CLIENT: Allow SHOW MASTER STATUS and SHOW SLAVE STATUS
  • REPLICATION SLAVE: Allow reading binary logs
  • SELECT: Allow SELECT
  • SUPER: Allow administrative commands
  • UPDATE: Allow UPDATE
  • USAGE: Allow login

ClustrixDB ignores the following privileges (no syntax error but the command has no effect):

  • ALTER ROUTINE
  • CREATE TEMPORARY TABLES
  • EXECUTE
  • FILE
  • LOCK TABLES
  • PROCESS
  • REFERENCES
  • RELOAD
  • SHOW DATABASES
  • SHOW VIEW
  • SHUTDOWN (requires SUPER Privilege)

See also section on ClustrixDB versus MySQL Privileges

...