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 are performed on the master aggregator and 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.
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:
-
OFF Non-root users, roles, and groups have to be configured on each aggregator as with previous versions of MemSQL. This is the default value.
-
ON The master aggregator will define and synchronize permissions for non-root users, groups, and roles across all aggregators in the cluster.
Deprecation Notice:
The sync_permissions
setting ON
will become the default in a future release, and the OFF
setting will
no longer be supported. New MemSQL application development should be done using sync_permissions
set to ON
. If
you are setting up a new cluster, it is recommended that you set sync_permissions
to ON
immediately.
Using the ON
setting from the start will simplify future upgrades and avoid
confusion that can result from having permissions set differently on different aggregators.
Leaves do not use cluster permissions, so setting the sync_permissions
value on those nodes does not matter.
Enabling sync permissions
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.
-
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 enablesync_permissions
. -
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;
-
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, MemSQL 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, MemSQL will create a sync user with the specified name and then run the grant. - If you run a
GRANT
orREVOKE
command on the master aggregator, MemSQL will assume the specified user is a sync user. - If you run a
GRANT
orREVOKE
command on a child aggregator, MemSQL will raise an error.
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.
- The root user is not present in
SHOW USERS
; however, it is still on the node. - New root users can be created following the
'root'@'host'
pattern; however, they will only be local users. - The root grant is “root@%”.