How to setup user access limitation on a timely basis in SAP HANA

Setting up user access limitation on a timely basis in SAP HANA is an uncommon but feasible scenario. This approach allows you to automatically activate and deactivate user permissions based on scheduled time intervals.

Implementation Steps

Follow these steps to implement time-based user access control ?

• Create a stored procedure with the entire responsibility of activating/deactivating users or authorizing and de-authorizing user access
• Create a batch user with sufficient privileges to execute the above-created procedures
• Set up a scheduled job to run the procedures automatically using hdbsql

Example Stored Procedure

Here's a sample stored procedure to manage user access ?

CREATE PROCEDURE MANAGE_USER_ACCESS (
   IN USERNAME NVARCHAR(256),
   IN ACTION NVARCHAR(10)  -- 'ACTIVATE' or 'DEACTIVATE'
)
LANGUAGE SQLSCRIPT
AS
BEGIN
   IF :ACTION = 'ACTIVATE' THEN
      ALTER USER :USERNAME ACTIVATE USER NOW;
   ELSEIF :ACTION = 'DEACTIVATE' THEN  
      ALTER USER :USERNAME DEACTIVATE USER NOW;
   END IF;
END;

Batch Job Setup

Use hdbsql to execute the procedure in a scheduled manner ?

hdbsql -n <hostname:port> -u <batch_user> -p <password> -d <database> \
-c "CALL MANAGE_USER_ACCESS('TARGET_USER', 'DEACTIVATE')"

Schedule this command using your operating system's job scheduler (cron on Linux/Unix or Task Scheduler on Windows) to run at specific times.

Conclusion

Time-based user access control in SAP HANA can be achieved through stored procedures, batch users, and scheduled jobs using hdbsql. This solution provides automated user permission management based on your specific timing requirements.