Skip to main content

SingleStoreDB v8.1

Synchronizing Permissions Across Your Cluster

You can ease user account management by automatically keeping non-root user accounts, roles, and groups in sync across your cluster. This functionality is enabled through a new engine variable named sync_permissions. All user account operations performed on the master aggregator are propagated automatically to each child aggregator in the cluster. For example, if an administrator connects to the master aggregator, issues a GRANT to change a user’s password, and then tries to connect to a child aggregator as that user using the old password, the old password will be rejected. If this command is issued in the child aggregator, it is first forwarded to the master aggregator, then propagated in the same way.

sync_permissions variable

The sync_permissions global variable defines the behavior of the cluster with respect to local versus cluster-wide, or sync, users. This variable has the following two values:

  • ON The master aggregator will define and synchronize permissions for non-root users, groups, and roles across all aggregators in the cluster. This is the default value.

  • OFF Non-root users, roles, and groups have to be configured on each aggregator as with previous versions of SingleStoreDB.

Enabling sync permissions

Caution

This is a permanent change. Once sync_permissions is enabled, you cannot turn it off and you can no longer create any local non-root users, groups, or roles. When sync_permissions is enabled, as the number of users increases, the operations of changing role permissions, roles of a group, user pools, and roles of a user pool tend to consume more time. Therefore, enabling sync_permissions is not recommended if there are more than 100 users (not necessarily concurrent users) on the system.

  1. Connect to the master aggregator as root and drop all non-root users, if present. Any groups or roles you have created will be removed after you enable sync_permissions.

  2. Set the sync_permissions variable. Because this is a sync variable, once you set this value, it will be set on all child aggregators in your cluster.

    SET GLOBAL sync_permissions = ON;

  3. Recreate users, roles, groups, and grants as needed on the master aggregator.

Understanding sync users

When sync_permissions is set, any new users created will be “sync users”, with the exception of the local root user that is still present on each aggregator. The following rules apply to sync users:

  • If you run the DROP USER command, SingleStoreDB drops the sync user with that name (if one exists).

  • The SHOW USERS command will only show sync users.

  • If you run a GRANT on a user that doesn’t exist, SingleStoreDB will create a sync user with the specified name and then run the grant.

  • If you run a GRANT or REVOKE command on the master aggregator, SingleStoreDB will assume the specified user is a sync user.

  • If you run a GRANT or REVOKE command on a child aggregator, SingleStoreDB will forward the command to the master aggregator and assume the specified user is a sync user.

All of the same semantics apply to sync groups and sync roles as well.

Root user behavior

When sync_permissions = on, the root user has some special properties.

  • New root users can be created following the 'root'@'host' pattern; however, they will only be local users.

  • The root grant is “root@%”.

In this section: