HANA: create a backup user and manage privileges
Create a HANA user for a backup tool, grant its privileges and list them.
How to create a dedicated SAP HANA user for a backup tool, grant it the system privileges it needs, and inspect the privileges of any user.
Create the user
Run as an administrator in the system database (hdbsql; \pa off disables paging):
\pa off
CREATE USER BACKUP_USER PASSWORD "<password>" NO FORCE_FIRST_PASSWORD_CHANGE;
SELECT USER_NAME FROM USERS;
SELECT SCHEMA_NAME FROM SCHEMAS;
Grant system privileges
GRANT "AUDIT ADMIN" TO BACKUP_USER;
GRANT "AUDIT OPERATOR" TO BACKUP_USER;
GRANT "BACKUP ADMIN" TO BACKUP_USER;
GRANT "BACKUP OPERATOR" TO BACKUP_USER;
GRANT "CATALOG READ" TO BACKUP_USER;
GRANT "DATABASE ADMIN" TO BACKUP_USER;
GRANT "DATA ADMIN" TO BACKUP_USER;
GRANT "INIFILE ADMIN" TO BACKUP_USER;
GRANT "LOG ADMIN" TO BACKUP_USER;
GRANT "MONITOR ADMIN" TO BACKUP_USER;
GRANT "RESOURCE ADMIN" TO BACKUP_USER;
GRANT "ROLE ADMIN" TO BACKUP_USER;
GRANT "SERVICE ADMIN" TO BACKUP_USER;
GRANT "SESSION ADMIN" TO BACKUP_USER;
GRANT "SYSTEM REPLICATION ADMIN" TO BACKUP_USER;
GRANT "TRACE ADMIN" TO BACKUP_USER;
GRANT "USER ADMIN" TO BACKUP_USER;
-- schema access
GRANT SELECT ON SCHEMA _SYS_SECURITY TO BACKUP_USER;
GRANT SELECT ON SCHEMA _SYS_STATISTICS TO BACKUP_USER;
This list is broad. Start from the privileges your backup tool actually documents and remove the others where possible.
Diagnose a missing privilege
When a statement fails with an insufficient privilege error, the error gives an identifier that can be decoded:
CALL SYS.GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS ('<error-guid>', ?)
List the system privilege names and the effective system privileges:
SELECT DISTINCT PRIVILEGE_NAME FROM SYSTEM.PRIVILEGES WHERE OBJECT_TYPE = 'SYSTEM PRIVILEGE';
SELECT DISTINCT PRIVILEGE FROM SYSTEM.EFFECTIVE_PRIVILEGES WHERE OBJECT_TYPE = 'SYSTEM';
Roles and object privileges
CREATE ROLE "APP_ROLE";
GRANT SELECT, UPDATE, INSERT ON TABLE <table_name> TO APP_ROLE;
GRANT APP_ROLE TO "<user_name>";
List everything granted to a user
SELECT * FROM "PUBLIC"."EFFECTIVE_PRIVILEGES" WHERE USER_NAME = '<user_name>';
SELECT * FROM "PUBLIC"."EFFECTIVE_ROLES" WHERE USER_NAME = '<user_name>';