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


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)


Post Answer