Loading a Single Account or Group

Loading a single account or group is helpful when you want to check the status of an account or entitlement.

The normal SQL syntax is:

Select * from <TableName> where NativeIdentity = '$(identity)'

For example:

Select * from Users where UserID = '$(identity)'

  • You configure the SQL query for accounts in the Single Account SQL Query field in Account Query Settings.

  • You configure the SQL query for groups in the Single Group SQL Query field in Group Query Settings.

Account Status

For the JDBC source, to set an account as enabled or disabled based on the values inside a specific column, you can add the IIQDisabled (string) attribute in the source account schema. For more information, refer to Adding Attributes to a Source Schema.

The following SQL query can be used to set up the state for the account:

select <Column1>, <Column2>, <Column...n>, case when <conditional Column for enable disable> then 'true' else 'false' end as IIQDisabled from <table_name>;

For example:

select EmployeeId, FirstName, LastName, IsActive, Groups, case when IsActive=0 then 'true' else 'false' end as IIQDisabled from Employee;

If you are using a stored procedure, the stored procedure needs to be updated to set a status.

For example:

CREATE PROCEDURE empstatus AS select empid, FirstName, LastName,isactive, Groups, case when isactive=0 then 'true' else 'false' end as IIQDisabled from master.dbo.emp GO;

Note
In cases where the stored procedure can not be modified, use the JDBC BuildMap Rule to set the account status. For more information, refer to Identity Security Cloud Rule Guide - BuildMap Rule.