Automic Workload Automation

  • 1.  Find User Privileges in the Database

    Posted Dec 20, 2016 09:28 AM
    If you want to retrieve the information which privileges are assigned to a user in the database you can use the following SQL script:

    SELECT OH_Client, OH_Name, USR_Privilege
    from OH
    inner join USR on OH_IDnr = USR_OH_Idnr
    where OH_Client = 1
    and OH_Deleteflag = 0
    and OH_Name = 'MUI/SUP';

    The column usr_privilege contains a decimal representation of the binary values for each privilege. You have to read it from right to left.

    Let me explain for a Version 10 Automation Engine:

    A user has only the privilege "ECC: Access to Predictive Analytics" assigned.
    Value of usr_privilege column will be: 274877906944
    Converted to binary: 100000000000000000000000000000000000000

    A user with all privileges assigned will have a value of 549755679999
    Converted to binary: 111111111111111111111011111010011111111

    Here you can see that in V10 there are 4 positions which are not used: 9, 10, 12 and 18 (read from right to left)



  • 2.  Find User Privileges in the Database

    Posted Dec 20, 2016 01:29 PM
    Additional information (for version 10/MSSQL 2014 and up) - but this should give you an idea and play around with it - SQL to display the missing privileges instead of just binary.
    See https://community.automic.com/discussion/6860/sql-to-verify-user-privileges-v9