v.24.12New Feature

Enhancements to ALTER USER and ALTER ROLE Syntax for Incremental Modifications

Official CH changelog text
Add syntax ALTER USER {ADD|MODIFY|DROP SETTING}, ALTER USER {ADD|DROP PROFILE}, the same for ALTER ROLE and ALTER PROFILE. So instead of replacing all the set of settings, you can modify it. #72050 (pufit).
Introduces syntax extensions for ALTER USER, ALTER ROLE, and ALTER PROFILE to allow adding, modifying, or dropping individual settings and profiles without replacing the entire set.

Why it matters

This feature addresses the limitation of having to replace the entire set of settings when altering users, roles, or profiles. It enables fine-grained modifications, simplifying management and reducing the risk of configuration errors by allowing incremental updates.

How to use it

Use the new syntax variants in ALTER USER, ALTER ROLE, or ALTER PROFILE statements as follows:

- To add, modify, or drop individual settings:

ALTER USER user_name ADD SETTING setting_name = value;
ALTER USER user_name MODIFY SETTING setting_name = value;
ALTER USER user_name DROP SETTING setting_name;


- To add or drop profiles:

ALTER USER user_name ADD PROFILE profile_name;
ALTER USER user_name DROP PROFILE profile_name;


The same syntax applies for ALTER ROLE and ALTER PROFILE commands.

Related resources

PreviousNext