http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> Since `BACKUP` does not backup user...
# questions-and-troubleshooting
s
@Rocky Since 
BACKUP
does not backup users/grants/permissions, we'd like to manage the cluster in a way that allow us to create user/permissions outside of the regular schema management code. I noticed several CREATE USER/ROLE etc do not have idempotent IF NOT EXISTS variant. Ideally instead of maintaining a big script of SQL command, we'd like a declarative approach to listing users, roles and grants in the style of terraform where a reconciliation process would handle required changes. Do you have tooling suggestion for this ?
r
Managing user accounts, roles, and permissions in a declarative, "Infrastructure as Code" (IaC) fashion is a common requirement for production StarRocks clusters. Since the native 
BACKUP
command does not include metadata for the privilege system, you are correct that a separate management layer is needed. Here are the best tooling suggestions for a declarative approach: 1. Terraform Provider (Community) While there isn't an "official" engine-level Terraform provider from StarRocks yet (the official 
celerdatabyoc
provider focuses on cluster deployment), there is a growing community provider: * Provider: svdimchenko/terraform-provider-starrocks * Capabilities: It aims to manage StarRocks-specific resources like Users, Roles, Grants, and Resource Groups. * Why it fits: It uses the Terraform state to perform reconciliation. If you define a role in your 
.tf
file and it’s missing in the cluster, Terraform will create it. If you remove a grant from the code, it will run the 
REVOKE
command for you. 2. Apache Ranger (Enterprise Declarative Policy) StarRocks has deep integration with Apache Ranger for centralized security management. * How it works: Instead of running 
GRANT
statements in SQL, you define access policies in Ranger's web UI or via its REST API (which can also be managed via a Terraform provider for Ranger). * Benefit: Ranger serves as a single source of truth for permissions. When a user queries StarRocks, the FE (Frontend) checks the policy with Ranger. This completely offloads permission management from the SQL engine’s metadata. 3. MySQL Terraform Provider (Basic Usage) Because StarRocks is MySQL-compatible, you can use the standard Terraform MySQL Provider. * Caveat: This works well for 
CREATE USER
and standard 
SELECT/INSERT
grants on databases/tables. However, it will not work for StarRocks-specific syntax such as: * Grants on 
CATALOGS
. * Grants on 
RESOURCE GROUPS
or 
STORAGE VOLUMES
. * StarRocks' unique Role hierarchy (unless using raw SQL blocks). 4. Custom Python/Go "Reconciliation" Script If the existing Terraform providers don't cover your specific StarRocks version's syntax (like the new RBAC system in v3.x+), many teams use a "Config-as-Code" approach: 1. Define state in YAML: List users, roles, and their associated grants. 2. Script: Write a small script that: * Queries 
sys.grants_to_users
and 
sys.grants_to_roles
to get the current state. * Parses your YAML file for the desired state. * Calculates the diff and executes the necessary 
CREATE
, 
DROP
, 
GRANT
, or 
REVOKE
commands. Summary Recommendation * For the "Terraform-native" experience: Start with the 
svdimchenko/starrocks
 provider. It is the most direct answer to your request for a reconciliation-based tool. * For large-scale/multi-tool environments: Use Apache Ranger. It is the recommended "industrial" way to manage declarative permissions across the modern data stack, including StarRocks. * For simple automation: Maintain a script that iterates through a JSON/YAML definition and uses the 
system
views (like 
information_schema
or 
sys
) to check for existence before running commands, effectively simulating 
IF NOT EXISTS
logic. References • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/user_privs.md
s
@Rocky I'm working on this reconciliation code. I'm wondering how to get the mysql native password hash of a user so that my code can determine when to update the password of an existing users to support password rotation processes.
r
For your reconciliation tool, you can retrieve the stored password hash (ciphertext) by querying StarRocks' internal metadata views or using standard MySQL-compatible commands. Since StarRocks aims for high MySQL protocol compatibility, it exposes a virtual 
mysql
database that contains user information. 1. Querying the 
mysql.user
Table The most direct way to get the hash for comparison is to query the 
authentication_string
column in the 
mysql.user
table. This is a "virtual" table provided by the Frontend (FE) for compatibility:
Copy code
sql
SELECT User, Host, authentication_string 
FROM mysql.user 
WHERE User = 'your_username';
* Reconciliation Logic: In your code, you can compare the 
authentication_string
returned by this query with the hash of your "target" password. * Generating the Target Hash: To know what the hash should be for a given plaintext password without actually setting it, use the 
password()
function:
Copy code
sql
    SELECT password('my_new_password');
    -- Returns something like: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
2. Using 
SHOW CREATE USER
 If you prefer a statement-based approach, you can use 
SHOW CREATE USER
. Although it is not always listed in the primary account management documentation, it is supported for MySQL compatibility:
Copy code
sql
SHOW CREATE USER 'jack'@'172.10.1.10';
Output Example:
Copy code
text
CREATE USER 'jack'@'172.10.1.10' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' ...
You can parse the string after the 
AS
keyword to extract the current hash. 3. Checking Authentication Metadata If you need to verify which authentication plugin is active (e.g., 
MYSQL_NATIVE_PASSWORD
vs 
LDAP
), use the 
SHOW AUTHENTICATION
command:
Copy code
sql
SHOW ALL AUTHENTICATION FOR 'jack';
This returns: * `UserIdentity`: The identity string. * `Password`: 
Yes
or 
No
(indicates if a password is set). * `AuthPlugin`: The plugin used (e.g., 
MYSQL_NATIVE_PASSWORD
). Summary for your Declarative Tool To support idempotent "password rotation," your tool's reconciliation loop should: 1. Read: 
SELECT authentication_string FROM mysql.user WHERE ...
2. Compare: Check if the retrieved hash matches 
password('desired_plaintext')
. 3. Update: If they differ, execute:
Copy code
sql
    ALTER USER 'user'@'host' IDENTIFIED BY 'new_plaintext';
    -- OR, to avoid sending plaintext over the wire if you already have the hash:
    ALTER USER 'user'@'host' IDENTIFIED WITH mysql_native_password AS '*HASH_STRING';
Note on Permissions: To query 
mysql.user
or run 
SHOW ALL AUTHENTICATION
, the user running the reconciliation script must have the 
user_admin
system-defined role. References • sql-reference/sql-statements/account-management/SHOW_AUTHENTICATION.md • administration/user_privs/authentication/native_authentication.md • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/privilege_faq.md • sql-reference/sql-statements/account-management/CREATE_USER.md • sql-reference/sql-statements/account-management/SHOW_GRANTS.md
s
@Rocky There is no 
mysql.user
such table, can you recommend a starrocks specific system view
r
You are correct that StarRocks does not maintain a physical 
mysql.user
table in the same way a traditional MySQL instance does. Instead, StarRocks uses a virtualized 
mysql
database and its own System Metadatabase (
sys
) for privilege management. For your reconciliation tool, there isn't a single "flat table" that exposes the raw hashes for all users. However, you can retrieve this information programmatically using the following methods: 1. The 
SHOW CREATE USER
Command (Recommended) Since your goal is reconciliation (matching the current cluster state to your declarative code), the most reliable way to get the exact hash is to execute 
SHOW CREATE USER
for each user identity.
Copy code
sql
SHOW CREATE USER 'jack'@'172.10.1.10';
Output:
Copy code
text
CREATE USER 'jack'@'172.10.1.10' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' ...
Your reconciliation script can parse the string following the 
AS
keyword. If the hash in the cluster doesn't match the hash generated by your code (using the 
password()
function locally or via SQL), you trigger an 
ALTER USER ... IDENTIFIED BY ...
. 2. The 
sys.grants_to_users
View While StarRocks doesn't have a 
mysql.user
table with a password column, it provides the 
sys.grants_to_users
 view. This is the StarRocks-native way to list all users and their identities, though it does not include the password hash for security reasons. You can use it to discover the list of users that need to be reconciled:
Copy code
sql
SELECT DISTINCT GRANTEE FROM sys.grants_to_users;
3. StarRocks Logic for Password Comparison In your reconciliation logic, you don't necessarily need to "decrypt" anything. You can perform the comparison like this: 1. Target State: Your code has a plaintext password (or a pre-computed hash). 2. Current State: Call 
SHOW CREATE USER
for the target 
user_identity
. 3. Diff: Extract the hash from the 
SHOW CREATE USER
output. 4. Verification: _ If you have the plaintext, run 
SELECT password('your_plaintext')
in StarRocks to see what the cluster _would* produce as a hash. * Compare the result with the extracted hash from step 3. Important Note on User Identities In StarRocks, a user is identified by a 
user_identity
(e.g., 
'username'@'host'
). * To get a list of all unique identities to iterate through, use 
SHOW USERS
(available in v3.0+). * Ensure your tool uses the exact 
user_identity
string, as 
ALTER USER
and 
SHOW CREATE USER
require the full identity, not just the username. Summary of Tooling Strategy For a declarative "Terraform-style" tool: 1. Read: Query 
SHOW USERS
to get the list of existing identities. 2. Inspect: For each user, run 
SHOW CREATE USER
and parse the 
AS '*HASH'
part. 3. Reconcile: * Create: If identity is missing in the cluster. * Update Password: If the parsed hash $\neq$ 
password('target_plaintext')
. * Update Roles/Grants: Use 
sys.grants_to_users
to diff the privileges. References • sql-reference/sql-statements/account-management/SHOW_USERS.md • sql-reference/sys/sys.md • administration/user_privs/authentication/native_authentication.md • sql-reference/sql-statements/account-management/SHOW_AUTHENTICATION.md • administration/user_privs/authorization/privilege_faq.md References • sql-reference/sys/sys.md
s
@Rocky Can I attach custom metadata to a user ?
r
As of the current version, StarRocks does not support a dedicated 
COMMENT
field or arbitrary key-value metadata within the 
CREATE USER
syntax. However, there are two practical ways to achieve this for your reconciliation tool: 1. Using User Properties (v3.3.3+) From StarRocks v3.3.3 onwards, you can attach specific properties to a user. While the system validates most keys, you can use the 
session.
prefix to store metadata that acts like a session variable for that user. Syntax:
Copy code
sql
CREATE USER 'reconciler_user' 
PROPERTIES (
    "session.user_metadata_owner" = "platform_team",
    "session.user_metadata_env" = "production"
);
* How to read it: Use 
SHOW PROPERTY FOR 'username'
. * Constraint: The key must be prefixed with 
session.
. Even if the session variable doesn't exist globally, the system often allows storing it as a user-level override, though it is primarily intended for valid session variables like 
query_timeout
. 2. The "Role-as-Tag" Pattern (Recommended) Since StarRocks allows custom role names, many teams use empty "tagging roles" to attach metadata to users or other roles. This is the most visible and robust way to track ownership or classification in a declarative system. Workflow: 1. Define a Tag Role: 
CREATE ROLE 'meta:owner:data_science';
2. Attach to User: 
GRANT 'meta:owner:data_science' TO USER 'jack'@'%';
3. Reconcile: Your tool can query 
sys.grants_to_users
to see which "metadata roles" are assigned to a user. 3. External State Mapping For a true Terraform-style reconciliation process, the best practice is to keep the "rich" metadata (like descriptions, creation dates, or Jira tickets) in your source of truth (e.g., a YAML/JSON file in Git) rather than trying to force it into the database engine. * In Git:
Copy code
yaml
    users:
      • name: jack
        host: '%'
        roles: [analyst]
        metadata: # Stay in Git, not in StarRocks
          owner: team_a
          purpose: "bi_reporting"
* In StarRocks: Only the state required for operation (Users, Roles, Grants) is materialized. Summary of Commands for your Tool: * To check properties: 
SHOW PROPERTY FOR 'username';
* To check "metadata roles": 
SELECT * FROM sys.grants_to_users WHERE GRANTEE = "'username'@'host'";
* To update properties: 
ALTER USER 'username' SET PROPERTIES ("max_user_connections" = "500");
References • sql-reference/sql-statements/account-management/CREATE_USER.md • sql-reference/sql-statements/account-management/SHOW_PROPERTY.md • sql-reference/sys/sys.md References • sql-reference/sql-statements/account-management/CREATE_USER.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/user_privs/authentication/native_authentication.md • sql-reference/sql-statements/account-management/ALTER_USER.md
s
@Rocky https://docs.starrocks.io/docs/sql-reference/sql-statements/account-management/GRANT/ does not show grant for 
TRUNCATE TABLE
command so which grant is actually required to be able to TRUNCATE a table ?
r
In StarRocks, the 
TRUNCATE TABLE
command is controlled by the 
DELETE
 privilege. Although 
TRUNCATE
is often considered a DDL operation in other database systems, StarRocks maps it to the 
DELETE
privilege because its primary purpose is to remove all data from a table or partition—a task that 
DELETE
also performs but less efficiently. Verification from Documentation According to the StarRocks privilege system (v3.0+): * 
DELETE
Privilege: Specifically described as the power to "Delete data from a table based on the specified condition or deletes all the data from a table." * Best Practice Guidance: StarRocks documentation explicitly states that risky operations like 
DELETE
or 
TRUNCATE
are protected by the 
DELETE
privilege (often bundled in roles like 
db_admin
). How to Grant It To allow a user to truncate a specific table, you should execute:
Copy code
sql
GRANT DELETE ON TABLE <db_name>.<table_name> TO USER 'username'@'host';
Or for all tables in a database:
Copy code
sql
GRANT DELETE ON ALL TABLES IN DATABASE <db_name> TO USER 'username'@'host';
Key Differences to Note * Compared to `DROP`: 
TRUNCATE
does not require the 
DROP
privilege. 
DROP
is used for deleting the table schema entirely, whereas 
TRUNCATE
preserves the table structure, indexes, and permissions. * Performance: While both require the same 
DELETE
privilege, 
TRUNCATE
is significantly faster and more resource-efficient than a 
DELETE FROM table
command because it operates at the metadata level rather than generating individual row versions. References • sql-reference/sql-statements/table_bucket_part_index/TRUNCATE_TABLE.md • administration/user_privs/authorization/privilege_item.md • sql-reference/sql-statements/account-management/GRANT.md References • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/privilege_item.md • sql-reference/sql-statements/account-management/GRANT.md
Open in Slack
PreviousNext
Powered by