Bitwise operators perform bit manipulations between two expressions of any of the data types of the integer data type category. Bitwise operators convert two integer values to binary bits, perform the AND, OR, or NOT operation on each bit, producing a result. Then converts the result to an integer.
AND
If bits at any location are both 1, the result is 1.
OR
If either bit at any location is 1, the result is 1.
NOT
Reverses the bit value at every bit location.
& (Bitwise AND)
| (Bitwise OR)
~ (Bitwise NOT)
/*
User Rights Permissions:
2 = Read Data
4 = Write data
8 = Update data
16 = Delete data
32 = Create User
64 = Update User
128 = Delete User
256 = SysAdmin
*/
Set NoCount On
DECLARE @UserRightsIndicator INT
DECLARE @ReadData bit,
@WriteData bit,
@UpdateData bit,
@DeleteData bit,
@CreateUser bit,
@UpdateUser bit,
@DeleteUser bit,
@SysAdmin bit
--Set Bit On or Off (On=1, Off=0)
Set @ReadData = 1 --1 means On, 0 means Off
Set @WriteData = 1
Set @UpdateData = 1
Set @DeleteData = 0
Set @CreateUser = 0
Set @UpdateUser = 0
Set @DeleteUser = 0
Set @SysAdmin = 0
SET @UserRightsIndicator =
POWER(2*@ReadData,1)
+ POWER(2*@WriteData,2)
+ POWER(2*@UpdateData,3)
+ POWER(2*@DeleteData,4)
+ POWER(2*@CreateUser,5)
+ POWER(2*@UpdateUser,6)
+ POWER(2*@DeleteUser,7)
+ POWER(2*@SysAdmin,8)
--Check if the User has any rights assigned:
PRINT '@UserRightsIndicator = ' + CONVERT(Varchar(10),@UserRightsIndicator);
IF ((2 & @UserRightsIndicator) = 2 ) PRINT 'Has ReadData'
IF ((4 & @UserRightsIndicator) = 4 ) PRINT 'Has WriteData'
IF ((8 & @UserRightsIndicator) = 8 ) PRINT 'Has UpdateData'
IF ((16 & @UserRightsIndicator) = 16 ) PRINT 'Has DeleteData'
IF ((32 & @UserRightsIndicator) = 32 ) PRINT 'Has CreateUser'
IF ((64 & @UserRightsIndicator) = 64 ) PRINT 'Has UpdateUser'
IF ((128 & @UserRightsIndicator) = 128 ) PRINT 'Has DeleteUser'
IF ((256 & @UserRightsIndicator) = 256 ) PRINT 'Has SysAdmin'
/*
00000010 (=2)
00001110 (=14) (14 means it 8 is on and 4 is on and 2 is on!!!!!)
--------
00000010 (=2)
*/
--Practical Example:
Create Table #UserRights (UserID INT, UserRights INT)
Insert into #UserRights Select 1, 14
--Extract the multiple User Rights from a single field int value by doing BitWise Operations on the value
SELECT
UserID,
UserRights as 'UserRightsIndicator',
CAST(UserRights & 2 AS bit) AS [Has Read Data],
CAST(UserRights & 4 AS bit) AS [Has Write Data],
CAST(UserRights & 8 AS bit) AS [Has Update Data],
CAST(UserRights & 16 AS bit) AS [Has Delete Data],
CAST(UserRights & 32 AS bit) AS [Has Create User],
CAST(UserRights & 64 AS bit) AS [Has Update User],
CAST(UserRights & 128 AS bit) AS [Has Delete User],
CAST(UserRights & 256 AS bit) AS [Has SysAdmin]
From
#UserRights
Where UserID = 1
Drop Table #UserRights
|
|
|
|
|
|