How to revoke all priviledges and roles from the user?

Question

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');

Answer 1

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.

Answer 2

Well if you really want the procedure, I'm using this one and it works.

It removes all the granted roles from the user. Run it if you know what you are doing.

create procedure myCleanupUser(in i_user varchar(20)) as
begin
    declare cursor Roles for
        SELECT ROLE_NAME FROM "PUBLIC"."EFFECTIVE_ROLES" 
        where USER_NAME=i_user AND GRANTEE_TYPE='USER' AND ROLE_NAME <> 'PUBLIC';

    for role as Roles do
        call REVOKE_ACTIVATED_ROLE(role.ROLE_NAME,i_user);
    end for;
end;