Lord OfTheRing February 2016

SAP Hana Revoke all Priviledge and roles from User

Anyone have written a stored procedure which can remove all assigned roles/privileges from given user ? I like to do this without looking up each role assigned or privilege assigned. Similar like below statement but without listing each role and privileges one by one. Removal all roles/privilege in one single statement regardless of what role assigned. This is for user termination process.

CALL REVOKE_ACTIVATED_ROLE('RoleName','username');

Answers


Lars Br. February 2016

There's no such standard functionality available and I recommend to be very careful with automatically revoking privileges. If the user himself granted privileges that he got granted with ADMIN OPTION or with GRANT OPTION to other users and these privileges get revoked, this leads to a recursive revocation of those granted privileges.

In short: this can become nasty.

For user termination, it's sufficient from a security point of view to disable the logon.

Other than that, it's pretty straight forward to query system view GRANTED_ROLES and GRANTED_PRIVILEGES to generate the REVOKE statements.

Post Status

Asked in February 2016
Viewed 1,437 times
Voted 10
Answered 1 times

Search




Leave an answer