docs/en/administration/user_privs/authorization/privilege_faq.md
This error may happen if the role is not activated. You can run select current_role(); to query the roles that have been activated for the user in the current session. If the required role is not activated, run SET ROLE to activate this role and perform operations using this role.
If you want roles to be automatically activated upon login, the user_admin role can run SET DEFAULT ROLE or ALTER USER DEFAULT ROLE to set a default role for each user. After the default role is set, it will be automatically activated when the user logs in.
If you want all the assigned roles of all users to be automatically activated upon login, you can run the following command. This operation requires the OPERATE permission at the System level.
SET GLOBAL activate_all_roles_on_login = TRUE;
However, we recommend that you follow the principle of "least privilege" by setting default roles with limited privileges to prevent potential risks. For example:
read_only role that has only the SELECT privilege as the default role, while avoiding setting roles with privileges like ALTER, DROP, and INSERT as default roles.db_admin role as the default role, while avoiding setting the node_admin role, which has the privilege to add and drop nodes, as the default role.This approach helps ensure that users are assigned roles with appropriate permissions, reducing the risk of unintended operations.
You can run GRANT to assign the required privileges or roles to users.
GRANT ALL ON ALL TABLES IN DATABASE <db_name> TO USER <user_identity>;), but the user still cannot create tables in the database. Why?Creating tables within a database requires the database-level CREATE TABLE privilege. You need to grant the privilege to the user.
GRANT CREATE TABLE ON DATABASE <db_name> TO USER <user_identity>;;
GRANT ALL ON DATABASE <db_name> TO USER <user_identity>;, but nothing is returned when the user runs SHOW TABLES; in this database. Why?SHOW TABLES; returns only tables on which the user has any privilege. If the user has no privilege on a table, this table will not be returned. You can grant any privilege on all tables in this database (using SELECT for example) to the user:
GRANT SELECT ON ALL TABLES IN DATABASE <db_name> TO USER <user_identity>;
The statement above is equivalent to GRANT select_priv ON db.* TO <user_identity>; used in versions earlier than v3.0.
http://<fe_ip>:<fe_http_port>?The user must have the cluster_admin role.
Before v3.0, after a user is granted privileges on a table, the privileges would still remain even if the table was dropped and recreated. Starting from v3.0, privileges will no longer be retained after a table is dropped and recreated.
You can obtain the full user list via querying the system view sys.grants_to_users or executing SHOW USERS, and then query each user individually using SHOW GRANTS FOR <user_identity>.
When the number of users or tables is very large, queries on system views sys.grants_to_users, sys.grants_to_roles, and sys.role_edges may take a long time. These views are computed in real time, consuming a proportion of FE resources. Therefore, it is not recommended to run such operations frequently at large scale.
Yes. Recreating a catalog will cause its relative privileges to be lost. You should back up all user privileges first and restore them after the catalog is recreated.
Not at the moment. Users must manually back up and restore permissions using SHOW GRANTS for each user.
Yes. The KILL command now requires the OPERATE privilege, and a user can only kill queries that were initiated by themselves.
For native tables, privileges are tied to the table ID, not the table name. This ensures data security, as table names can change arbitrarily. If privileges were to follow table names, it could cause data leakage. Similarly, when a table is dropped, its permissions are removed because the object no longer exists.
For external tables, historical versions behaved the same as internal tables. However, because external table metadata is not managed by StarRocks, delays or privilege loss can occur. To address this, future versions will use table-name–based permission management for external tables, which aligns with the expected behavior.
Below is a sample script for backing up the user privilege information in the cluster.
#!/bin/bash
# MySQL connection info
HOST=""
PORT="9030"
USER="root"
PASSWORD=""
OUTPUT_FILE="user_privileges.txt"
# Clear output file
> $OUTPUT_FILE
# Get user list
users=$(mysql -h$HOST -P$PORT -u$USER -p$PASSWORD -e "SHOW USERS;" | sed -e '1d' -e '/^+/d')
# Loop through users and get privileges
for user in $users; do
echo "Privileges for $user:" >> $OUTPUT_FILE
mysql -h$HOST -P$PORT -u$USER -p$PASSWORD -e "SHOW GRANTS FOR $user;" >> $OUTPUT_FILE
echo "" >> $OUTPUT_FILE
done
echo "All user privileges have been written to $OUTPUT_FILE"
Normal functions cannot be granted using IN ALL DATABASES; they can only be granted within the current database. While global functions are granted on the ALL DATABASES scale.