search cancel

How to adjust user privileges with a simple SQL Statement


Article ID: 145505


Updated On:


CA Automic Workload Automation - Automation Engine


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:


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;