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?
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)