Using SQL to analyze Active Directory UserAccountControl bits to determine if account is DISABLED or ENABLED

0

I have a dump of Users data from Active Directory into Oracle table and need to create a view to analyze Active Directory data using SQL. One of the columns in that view needs to be a flag that would contain 'Y' if this user account is disabled in Active Directory, and 'N' if user account is active and user can log in. I have a numeric column called user_account_control that basically contains a value from UserAccountControl AD attribute. I know that there is a bit in that numeric value that tells you if this account is active or not. How should I perform that bitwise operation on a given bit in that field and produce that flag column in Oracle SQL?

1 Answer

Use this SQL code:

select case when bitand(user_account_control, 2) = 0 then 'N' else 'Y' end AS account_disabled
from active_directory_user_table

Here, decimal value 2 used as a parameter to BitAnd function represents the ACCOUNTDISABLE bit that you want to analyze. This SQL expression returns 'N' if account is active (not disabled), and 'Y' if not active (disabled)

1

Post Answer