LLI Wiki — My Digital Knowledge Base

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>';
Mastodon LLIMastodon SocialFosstodon