How to adjust user privileges with a simple SQL Statement

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

User priviliges are managed as a binary mask. The values is stored in a decimal format in the USR_PRIVILEGE field in the USR table. They can be modified by a simple update statement.

When forging the SQL Statement it must be ensured that no negative USR_PRIVILEGE is produced and NO deleted users are updated:

Resolution

For example the query to remove the privilege 'FileEvents: Start without Login object specified' (hexadecimal mask value 1099511627775) is the following one

UPDATE USR SET USR_PRIVILEGE = USR_PRIVILEGE - 1099511627776 from USR inner join OH on OH_IDNR = USR_OH_IDNR where USR_OH_IDNR >= 100000 and OH_DELETEFLAG = 0 and USR_Privilege >1099511627775;