Security Architecture: Users, Privileges, Catalog vs HDI Roles & SSFS
Master SAP HANA security governance: Standard vs. Restricted users, the 5 privilege types, Catalog Roles vs. HDI Container Roles, Secure Storage in the File System (SSFS), secure client key management with hdbuserstore, and offline SYSTEM user password recovery.
IntermediateSecurity~18 min studyHANA 2.0 (All SPS)
Core Learning Objectives
- 1.Differentiate Standard Users from Restricted Users and their security footprints.
- 2.Master the 5 privilege categories in SAP HANA.
- 3.Understand why Catalog Roles are discouraged in production compared to design-time HDI Roles.
- 4.Manage credentials securely via hdbuserstore without plaintext scripts.
- 5.Recover a locked or forgotten SYSTEM user password offline using hdbnameserver.
Prerequisites:hana-architecture-multitenant-mdc
Definition & Architectural Rationale
Technical Definition:
SAP HANA Security encompasses authentication, fine-grained role-based access control (RBAC), data encryption at rest and in transit, audit logging, and cryptographic key management via the Secure Storage in the File System (SSFS).
Why this component exists:
As the centralized repository for an enterprise's financial, customer, and transactional data, SAP HANA is the highest-value security target. Implementing strict least-privilege policies and robust cryptographic key management prevents unauthorized data extraction and satisfies regulatory compliance.
Internal Working & Mechanics
1. Standard Users vs. Restricted Users:
- Standard User: Can create schemas, create tables, connect via ODBC/JDBC, and read system catalogs. Designed for internal technical DBAs and business application connections.
- Restricted User: Created with CREATE RESTRICTED USER. CANNOT create objects, cannot create schemas, has NO default catalog read privileges, and CANNOT connect via standard ODBC/JDBC unless explicitly granted. Ideal for external reporting tools or contractors.
2. Catalog Roles vs. HDI (Repository) Roles:
- Catalog Roles: Created via SQL (CREATE ROLE "MY_ROLE"). Owned by the creating user. If that DBA leaves the company and their user account is deleted with DROP USER ... CASCADE, the role and its privileges are automatically DESTROYED, breaking production applications!
- HDI Container Roles: Created as design-time artifacts (.hdbrole) inside Git repositories. Deployed via the SAP HANA Deployment Infrastructure (HDI). Owned by the technical container user (_SYS_DI), decoupled from individual DBA identities.
3. Secure Storage in the File System (SSFS):
- SSFS protects internal database encryption keys (data volume encryption, log encryption, root keys).
- Stored in /usr/sap/<SID>/SYS/global/hdb/security/ssfs/data/SSFS_<SID>.DAT and key file SSFS_<SID>.KEY.
- Must be backed up and secured with chmod 600.
4. hdbuserstore (Secure Client Storage):
- Stores encrypted connection strings and user passwords on the client/application OS.
- Prevents hardcoding passwords in bash scripts or SAP application connection profiles:
hdbuserstore SET <KEY> <host>:<port> <user> <password>
hdbsql -U <KEY> "SELECT * FROM DUMMY"
Architecture Flow Diagram
Mermaid DiagramSecurity Architecture: Users, Privileges, Catalog vs HDI Roles & SSFS
Architectural Flow: Standard users possess default capabilities while Restricted users require explicit grants. Privileges span 5 distinct categories, and HDI roles protect role definitions from being deleted when DBA accounts are dropped.
Production SQL Diagnostics
Audit User Roles, Granted Privileges & Account StatusSafe (Read-Only)
Audits effective roles and system privileges granted to a database user.
Both
Privileges: CATALOG READ or USER ADMIN
Target Engine: HANA 2.0 (All SPS)
SELECT
U.USER_NAME,
U.USER_DEACTIVATED,
U.IS_RESTRICTED,
U.PASSWORD_CHANGE_TIME,
P.PRIVILEGE,
P.IS_GRANTABLE
FROM SYS.USERS U
LEFT JOIN SYS.GRANTED_PRIVILEGES P ON U.USER_NAME = P.GRANTEE
WHERE U.USER_NAME = 'SAPABAP1'
ORDER BY P.PRIVILEGE;
Key Diagnostic Columns
USER_DEACTIVATED:TRUE if locked or deactivatedIS_RESTRICTED:TRUE for restricted userPRIVILEGE:Specific privilege granted (e.g. MONITORING)Expected Diagnostic Output
USER_NAME | USER_DEACTIVATED | IS_RESTRICTED | PASSWORD_CHANGE_TIME | PRIVILEGE | IS_GRANTABLE -------------------------------------------------------------------------------------------------- SAPABAP1 | FALSE | FALSE | 2026-09-01 10:00:00 | MONITORING | FALSE SAPABAP1 | FALSE | FALSE | 2026-09-01 10:00:00 | TABLE ADMIN | FALSE
DBA Interpretation: User SAPABAP1 is active, standard (not restricted), with necessary administrative privileges.
Linux Terminal Diagnostics & Operations
Configure and Test Encrypted hdbuserstore Key
user: <sid>adm
Stores encrypted login credentials so scripts can invoke hdbsql without embedding plaintext passwords.
hdbadm@host $ hdbuserstore SET DEFAULT site-primary:30015 SYSTEM "MasterSecret_2026!" && hdbuserstore LIST && hdbsql -U DEFAULT "SELECT CURRENT_USER FROM DUMMY;"
Terminal Standard Output (stdout):
KEY DEFAULT ENV : site-primary:30015 USER: SYSTEM CURRENT_USER SYSTEM
Output Analysis: The key DEFAULT was registered and successfully authenticated against the database without plaintext exposure.
Emergency Offline Password Reset for SYSTEM User in SystemDB
user: <sid>adm
Recovers access when the SystemDB SYSTEM password is lost or locked and no other administrator account exists.
hdbadm@host $ HDB stop && hdbnameserver -resetUserPassword SYSTEM && HDB start
Terminal Standard Output (stdout):
nameserver reset password utility... Enter new password for SYSTEM: ******** Re-enter new password for SYSTEM: ******** Password for user SYSTEM successfully reset.
Output Analysis: Password was updated directly in the persistence layer while nameserver was offline. Upon HDB start, the new password is live.
Troubleshooting & Root Cause Analysis
Production Best Practices & Operational Risks
Mandatory Best Practices
- Deactivate the default SYSTEM user in production after creating dedicated individual DBA accounts with appropriate roles.
- Always use hdbuserstore in cron jobs and backup scripts to avoid logging plaintext passwords in ps or bash history.