The BITAND function in Oracle is used to perform a bitwise AND operation between two integer values. It returns the bitwise logical AND of the two numbers.
🔹 Syntax:
BITAND(number1, number2)
number1,number2must be integers (or expressions that resolve to integers).- Oracle treats them as 32-bit signed integers.
🔹 How it Works:
Each number is converted to its binary form, and the AND operation is applied bit by bit.
🔹 Example:
SELECT BITAND(6, 3) AS result FROM dual;
- Binary of 6 =
110 - Binary of 3 =
011 - Bitwise AND =
010= 2
Output:
RESULT
-------
2
🔹 Use Cases:
- Permissions checking using bitmasks.
- Feature flags (on/off) stored as bits.
- Compact storage for multiple boolean values in one field.
🔹 Example with Bitmask:
Suppose permissions column has values like:
1= Read2= Write4= Execute
To find rows where Write permission (2) is set:
SELECT * FROM users
WHERE BITAND(permissions, 2) = 2;
Here's a detailed bitmask-based use case using BITAND in Oracle — perfect for storing and checking multiple permissions or flags within a single integer column.
🔹 Scenario: User Permissions Stored in Bitmask
| Permission | Bit Value |
|---|---|
| Read | 1 |
| Write | 2 |
| Execute | 4 |
| Delete | 8 |
A user who has Read + Write + Execute would have a permissions value of 1 + 2 + 4 = 7.
🔹 Step-by-step Example
1. Create Table
CREATE TABLE user_roles (
user_id NUMBER,
user_name VARCHAR2(50),
permissions NUMBER
);
2. Insert Data
INSERT INTO user_roles VALUES (1, 'Alice', 3); -- Read + Write
INSERT INTO user_roles VALUES (2, 'Bob', 5); -- Read + Execute
INSERT INTO user_roles VALUES (3, 'Charlie', 15); -- All permissions
INSERT INTO user_roles VALUES (4, 'David', 0); -- No permissions
🔹 3. Query Users with Specific Permissions
🟢 Find users with Write permission:
SELECT * FROM user_roles
WHERE BITAND(permissions, 2) = 2;
🟢 Find users with Execute permission:
SELECT * FROM user_roles
WHERE BITAND(permissions, 4) = 4;
🟢 Find users with No permissions:
SELECT * FROM user_roles
WHERE permissions = 0;
🟢 Find users with Read + Execute only (1 + 4 = 5):
SELECT * FROM user_roles
WHERE permissions = 5;
🔹 Optional: Decode Bitmask into Human-Readable Permissions
SELECT
user_name,
permissions,
CASE WHEN BITAND(permissions, 1) = 1 THEN 'Y' ELSE 'N' END AS can_read,
CASE WHEN BITAND(permissions, 2) = 2 THEN 'Y' ELSE 'N' END AS can_write,
CASE WHEN BITAND(permissions, 4) = 4 THEN 'Y' ELSE 'N' END AS can_execute,
CASE WHEN BITAND(permissions, 8) = 8 THEN 'Y' ELSE 'N' END AS can_delete
FROM user_roles;
PL/SQL procedure to manage and check user permissions using bitmask logic with BITAND in Oracle. This includes:
- Adding permissions
- Revoking permissions
- Checking if a user has a specific permission
🔧 Step 1: Setup
Let's continue using the same user_roles table:
-- Already created earlier:
-- CREATE TABLE user_roles (user_id NUMBER, user_name VARCHAR2(50), permissions NUMBER);
🔁 Step 2: Permission Constants (using a package)
CREATE OR REPLACE PACKAGE permission_constants AS
c_read CONSTANT NUMBER := 1;
c_write CONSTANT NUMBER := 2;
c_execute CONSTANT NUMBER := 4;
c_delete CONSTANT NUMBER := 8;
END permission_constants;
/
🔧 Step 3: Procedure to Update and Check Permissions
CREATE OR REPLACE PROCEDURE manage_permissions (
p_user_id IN NUMBER,
p_action IN VARCHAR2, -- 'ADD', 'REMOVE', 'CHECK'
p_permission IN NUMBER
) AS
v_perms NUMBER;
v_status VARCHAR2(20);
BEGIN
SELECT permissions INTO v_perms
FROM user_roles
WHERE user_id = p_user_id
FOR UPDATE;
IF UPPER(p_action) = 'ADD' THEN
v_perms := v_perms + p_permission;
UPDATE user_roles SET permissions = v_perms WHERE user_id = p_user_id;
DBMS_OUTPUT.PUT_LINE('Permission added.');
ELSIF UPPER(p_action) = 'REMOVE' THEN
IF BITAND(v_perms, p_permission) = p_permission THEN
v_perms := v_perms - p_permission;
UPDATE user_roles SET permissions = v_perms WHERE user_id = p_user_id;
DBMS_OUTPUT.PUT_LINE('Permission removed.');
ELSE
DBMS_OUTPUT.PUT_LINE('Permission not present, nothing to remove.');
END IF;
ELSIF UPPER(p_action) = 'CHECK' THEN
IF BITAND(v_perms, p_permission) = p_permission THEN
v_status := 'YES';
ELSE
v_status := 'NO';
END IF;
DBMS_OUTPUT.PUT_LINE('User has permission? ' || v_status);
ELSE
DBMS_OUTPUT.PUT_LINE('Invalid action. Use ADD, REMOVE, or CHECK.');
END IF;
END;
/
🧪 Usage Examples
-- Add WRITE permission to user 1
BEGIN
manage_permissions(1, 'ADD', permission_constants.c_write);
END;
/
-- Remove READ permission from user 2
BEGIN
manage_permissions(2, 'REMOVE', permission_constants.c_read);
END;
/
-- Check if user 3 has DELETE permission
BEGIN
manage_permissions(3, 'CHECK', permission_constants.c_delete);
END;
/
Here's a PL/SQL function that returns the list of human-readable permissions (like READ, WRITE, etc.) currently assigned to a user, based on a bitmask stored in the permissions column.
✅ Step: Permission Listing Function
CREATE OR REPLACE FUNCTION get_user_permissions(p_user_id IN NUMBER)
RETURN VARCHAR2
IS
v_perms NUMBER;
v_result VARCHAR2(4000) := '';
BEGIN
SELECT permissions INTO v_perms
FROM user_roles
WHERE user_id = p_user_id;
IF BITAND(v_perms, permission_constants.c_read) = permission_constants.c_read THEN
v_result := v_result || 'READ, ';
END IF;
IF BITAND(v_perms, permission_constants.c_write) = permission_constants.c_write THEN
v_result := v_result || 'WRITE, ';
END IF;
IF BITAND(v_perms, permission_constants.c_execute) = permission_constants.c_execute THEN
v_result := v_result || 'EXECUTE, ';
END IF;
IF BITAND(v_perms, permission_constants.c_delete) = permission_constants.c_delete THEN
v_result := v_result || 'DELETE, ';
END IF;
-- Remove the trailing comma and space
IF v_result IS NOT NULL THEN
v_result := RTRIM(v_result, ', ');
ELSE
v_result := 'NO PERMISSIONS';
END IF;
RETURN v_result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'USER NOT FOUND';
END;
/
🧪 Usage Example
SELECT get_user_permissions(1) FROM dual;
-- Output: 'READ, WRITE'
Tags:
Oracle