hana security authorization definitive guide mastering core

2. Create a New Role

3. Assign Privileges to the Role

4. Assign the Role to Users or Other Roles

5. Validate and Activate the Role

SQLScript Syntax for Defining Authorization Objects

1. Creating Roles
Use `CREATE ROLE` to define roles with optional descriptions and inheritance:

CREATE ROLE "FINANCE_ANALYST_ROLE"
DESCRIPTION 'Role for financial analysts with read access to revenue data'
INHERIT FROM "DATA_ANALYST_ROLE";


- Best Practices:

2. Granting Privileges
Assign system, package, or analytic privileges using `GRANT`:

-- Grant system privilege
GRANT CREATE PROCEDURE TO "FINANCE_ANALYST_ROLE";

-- Grant package privilege (schema-level)
GRANT SELECT ON SCHEMA "FINANCE" TO "FINANCE_ANALYST_ROLE";

-- Grant analytic privilege (row-level security)
GRANT SELECT ON "FINANCE"."REVENUE_VIEW" TO "FINANCE_ANALYST_ROLE"
WHERE "DEPARTMENT" = 'FINANCE';


- Best Practices:

3. Revocating Privileges
Remove privileges using `REVOKE` to enforce least-privilege principles:

REVOKE SELECT ON "FINANCE"."REVENUE_VIEW" FROM "FINANCE_ANALYST_ROLE";
REVOKE CREATE PROCEDURE FROM "FINANCE_ANALYST_ROLE";


- Best Practices:

4. Assigning Roles to Users
Use `GRANT ROLE` to delegate role membership:

GRANT "FINANCE_ANALYST_ROLE" TO "FINANCE_ANALYST_USER";


- Best Practices:

Checklist of Critical Authorization Objects and Use Cases

- System Privileges

- Package Privileges

- Analytic Privileges

- Privilege Objects for XS Advanced

Validating Authorization Configurations with System Views

1. Audit Assigned System Privileges
Query the `SYSTEM_PRIVILEGES` view to list all granted system privileges:

SELECT GRANTEE_NAME, PRIVILEGE_NAME, GRANTOR_NAME
FROM SYSTEM_PRIVILEGES
WHERE GRANTEE_NAME = 'FINANCE_ANALYST_ROLE'
ORDER BY PRIVILEGE_NAME;


- Output Interpretation:

2. Audit Package and Analytic Privileges
Use the `PRIVILEGES` view to inspect object-level permissions:

Advanced Techniques for Fine-Grained Access Control in SAP HANA

Implementation of Row-Level Security Using Predicate-Based Policies

Key Components of RLS Policies:

Example: Filtering Sales Data by Region
```sql
CREATE ROW ACCESS POLICY "SALES_REGION_FILTER"
ON SCHEMA "SALES"
FOR TABLE "ORDERS"
USING (
SELECT
CASE
WHEN SESSION_CONTEXT('USER_REGION') = 'NA' THEN 'NA'
ELSE 'EMEA'
END AS FILTER_REGION
)
AS (
REGION = FILTER_REGION
);
```
Assumption: The `USER_REGION` attribute is populated via a custom authentication handler or SAP HANA XS Advanced role mapping.

Dynamic Predicates with Custom Attributes
For scenarios requiring runtime evaluation (e.g., time-based access), use expressions referencing session variables or external attribute tables:
```sql
CREATE ROW ACCESS POLICY "TIME_BASED_ACCESS"
ON SCHEMA "FINANCE"
FOR TABLE "TRANSACTIONS"
USING (
SELECT
CURRENT_TIMESTAMP - INTERVAL '1' DAY AS MAX_DATE
)
AS (
TRANSACTION_DATE <= MAX_DATE
);
```

Performance Impact: Column-Level Security vs. Row-Level Security

Methodology for Benchmarking:
1. Dataset Preparation: Use a 100GB+ table with uniform distribution of sensitive columns/rows.
2. Query Execution: Measure runtime for:

Observed Trade-offs:

Recommendation for Large Datasets:
Deploy RLS with indexed predicates and partition tables by natural keys (e.g., `REGION`) to localize predicate evaluation. For CLS, use columnar storage (e.g., `COLUMN STORE`) to optimize masked column retrieval.

Static privileges in SAP HANA (e.g., `GRANT SELECT ON TABLE TO ROLE`) offer simplicity but lack flexibility for dynamic contexts like multi-tenancy or time-bound access. Dynamic privileges (e.g., `GRANT SELECT ON SCHEMA TO ROLE WITH GRANT OPTION` or predicate-based policies) enable runtime evaluation, reducing administrative overhead for role management. Trade-offs include:

Static Privileges: Suitable for stable environments with infrequent access changes.

Dynamic Privileges: Ideal for scenarios requiring real-time attribute validation (e.g., ABAC) or tenant isolation in cloud deployments.

Performance Cost: Dynamic privileges may introduce predicate parsing overhead; mitigate with pre-compiled policies or cached session contexts.

Step-by-Step Guide to Implementing Attribute-Based Access Control (ABAC)

Prerequisites:

Step 1: Define Custom Authorization Attributes
Store attributes in a dedicated table (e.g., `SECURITY_ATTRIBUTES`) with columns for `USER_ID`, `ATTRIBUTE_NAME`, and `VALUE`:
```sql
CREATE TABLE "SECURITY_ATTRIBUTES" (
USER_ID NVARCHAR(255),
ATTRIBUTE_NAME NVARCHAR(255),
VALUE NVARCHAR(255),
PRIMARY KEY (USER_ID, ATTRIBUTE_NAME)
);
```

Step 2: Create a View to Expose Attributes for ABAC
Use a view to join user attributes with session context:
```sql
CREATE VIEW "USER_ABAC_ATTRIBUTES" AS
SELECT
u.USER_ID,
a.ATTRIBUTE_NAME,
a.VALUE AS ATTRIBUTE_VALUE,
SESSION_CONTEXT('CLIENT_HOST') AS CLIENT_IP
FROM "USERS" u
LEFT JOIN "SECURITY_ATTRIBUTES" a ON u.USER_ID = a.USER_ID;
```

Step 3: Implement ABAC in SQL Using the `AUTHORIZATION` Attribute
The `AUTHORIZATION` attribute in SQL enables runtime attribute checks. Example for a `GET_CUSTOMERS` procedure:
```sql
CREATE PROCEDURE "GET_CUSTOMERS" (
OUT customers TABLE (
ID INT,
NAME NVARCHAR(100),
DEPARTMENT NVARCHAR(50)
)
)
LANGUAGE SQLSCRIPT
AS
BEGIN
DECLARE v_user_dept NVARCHAR(50);
SELECT ATTRIBUTE_VALUE INTO v_user_dept
FROM "USER_ABAC_ATTRIBUTES"
WHERE USER_ID = CURRENT_USER AND ATTRIBUTE_NAME = 'DEPARTMENT';

SELECT
ID, NAME, DEPARTMENT
FROM "CUSTOMERS"
WHERE DEPARTMENT = v_user_dept
AND AUTHORIZATION CHECK (
SELECT 1 FROM "USER_ABAC_ATTRIBUTES"
WHERE USER_ID = CURRENT_USER
AND (ATTRIBUTE_NAME = 'ROLE' AND ATTRIBUTE_VALUE IN ('MANAGER', 'HR'))
);
END;
```

Step 4: Validate ABAC Policies with `AUTHORIZATION` in Views
For view-level enforcement, use the `AUTHORIZATION` clause:
```sql
CREATE VIEW "SAFE_CUSTOMER_DATA" AS
SELECT
ID, NAME
FROM "CUSTOMERS"
WHERE AUTHORIZATION CHECK (
EXISTS (
SELECT 1 FROM "USER_ABAC_ATTRIBUTES"
WHERE USER_ID = CURRENT_USER
AND ATTRIBUTE_NAME = 'ACCESS_LEVEL'
AND ATTRIBUTE_VALUE = 'HIGH'
)
);
```

Step 5: Automate Attribute Population
Use SAP HANA XS Advanced or custom scripts to sync attributes from:

Best Practices for ABAC in HANA: