http://starrocks.io logo
Join Slack
Powered by
l
@Rocky hello
r
Hello! I'm Rocky, your StarRocks assistant. How can I help you today?
l
@Rocky how to create user with backup and restore privilage
r
To create a user with backup and restore privileges in StarRocks (v3.0 and later), you must grant the 
REPOSITORY
privilege at the system level along with specific operational privileges depending on the scope (global, database, or table). Global Backup and Restore To allow a user to back up and restore any database or table, follow these steps: 1. Create the user:
Copy code
sql
   CREATE USER 'backup_user'@'%' IDENTIFIED BY 'your_password';
2. Create a role (recommended) and grant privileges:
Copy code
sql
   CREATE ROLE recover_role;

   -- 1. Essential system privilege for backup/restore
   GRANT REPOSITORY ON SYSTEM TO ROLE recover_role;

   -- 2. Privileges to create databases during restore
   GRANT CREATE DATABASE ON CATALOG default_catalog TO ROLE recover_role;

   -- 3. Privileges to create tables during restore
   GRANT CREATE TABLE ON ALL DATABASES TO ROLE recover_role;

   -- 4. Privileges to load (INSERT) and backup (EXPORT) data
   GRANT INSERT, EXPORT ON ALL TABLES IN ALL DATABASES TO ROLE recover_role;
3. Assign the role to the user:
Copy code
sql
   GRANT recover_role TO USER 'backup_user'@'%';
Specific Database Scope If you only want the user to manage a specific database (e.g., 
my_db
), adjust the scope:
Copy code
sql
GRANT REPOSITORY ON SYSTEM TO ROLE db_recover_role;
GRANT CREATE TABLE ON DATABASE my_db TO ROLE db_recover_role;
GRANT INSERT, EXPORT ON ALL TABLES IN DATABASE my_db TO ROLE db_recover_role;
Key Requirements * REPOSITORY: This is a system-level privilege required to interact with the backup repository. * INSERT: Required for 
RESTORE
operations to write data into tables. * EXPORT: Required for 
BACKUP
operations to read data out of tables. * CREATE DATABASE/TABLE: Required for 
RESTORE
operations if the target objects do not already exist. _Note: In versions earlier than v3.0, the 
admin_priv
privilege was generally required for these operations._ References • administration/user_privs/authorization/User_privilege.md • sql-reference/sql-statements/account-management/GRANT.md • sql-reference/sql-statements/backup_restore/BACKUP.md • sql-reference/sql-statements/backup_restore/RESTORE.md
l
@Rocky how to list all users can have access role to a specific database in starrocks
r
In StarRocks (v3.0+), the most efficient way to list users with access to a specific database is by querying the 
sys
(system) views. These views allow you to see both direct privileges and those inherited via roles. You can use the following queries to find users with access to a database (e.g., 
your_database_name
): 1. Users with direct privileges on the database This query checks users who were granted privileges directly (e.g., 
GRANT SELECT ON DATABASE db1 TO USER user1
):
Copy code
sql
SELECT GRANTEE, PRIVILEGE_TYPE 
FROM sys.grants_to_users 
WHERE OBJECT_DATABASE = 'your_database_name';
2. Users with access via Roles This query identifies roles that have access to the database and lists the users assigned to those roles.
Copy code
sql
SELECT 
    e.TO_USER AS User, 
    g.GRANTEE AS Role, 
    g.PRIVILEGE_TYPE
FROM sys.grants_to_roles g
JOIN sys.role_edges e ON g.GRANTEE = e.FROM_ROLE
WHERE g.OBJECT_DATABASE = 'your_database_name'
  AND e.TO_USER IS NOT NULL;
3. Check for users with Built-in Admin roles Built-in roles like 
db_admin
have access to all databases. You can check which users have these roles:
Copy code
sql
SELECT TO_USER 
FROM sys.role_edges 
WHERE FROM_ROLE = 'db_admin' 
  AND TO_USER IS NOT NULL;
Summary of Key Views * 
sys.grants_to_users
: Privileges granted directly to users. * 
sys.grants_to_roles
: Privileges granted to roles (only user-defined roles appear here). * 
sys.role_edges
: The mapping between roles and users (or roles and other roles). Note: To query these 
sys
views, you typically need the 
user_admin
or 
db_admin
role. If you want to check a specific user's total privileges manually, you can also use:
Copy code
sql
SHOW GRANTS FOR 'username'@'%';
References • sql-reference/sys/grants_to_users.md • sql-reference/sys/grants_to_roles.md • sql-reference/sys/role_edges.md • administration/user_privs/authorization/User_privilege.md References • sql-reference/sys/sys.md • sql-reference/sys/role_edges.md • sql-reference/sys/grants_to_roles.md • sql-reference/sys/grants_to_users.md • administration/user_privs/authorization/privilege_item.md
Open in Slack
PreviousNext
Powered by