Store and Query Bitwise Permission Flags

Pack multiple boolean flags into a single integer column and test individual bits with `column & mask = mask`, avoiding one boolean column per flag.

Created Jun 23, 2026 Last updated Jun 23, 2026 5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Find users who have the WRITE permission bit set

The WRITE flag has value 2 (binary 10). Testing permissions & 2 = 2 isolates bit position 1. Alice has permissions 3 (binary 011 = READ + WRITE): 3 & 2 = 2 ✔. Bob has permissions 5 (binary 101 = READ + DELETE): 5 & 2 = 0 ✘ — bit 1 is not set. Carol has permissions 7 (binary 111 = READ + WRITE + DELETE): 7 & 2 = 2 ✔. Dave has 0: all bits clear. To grant WRITE to bob: UPDATE users SET permissions = permissions | 2 WHERE username = 'bob'. To revoke DELETE (4) from carol: UPDATE users SET permissions = permissions & ~4 WHERE username = 'carol'. To check whether a user has all of READ (1) and WRITE (2), test permissions & 3 = 3 — any value where both bits are set satisfies this.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE users (username VARCHAR(20), permissions INT);

INSERT INTO
  users
VALUES
  ('alice', 3),
  ('bob', 5),
  ('carol', 7),
  ('dave', 0);

Validated query Shared across supported engines.

SQL

SELECT username, permissions FROM users WHERE permissions & 2 = 2 ORDER BY username;

Expected result Returned rows for the shared example.

username	permissions
alice	3
carol	7

Output is identical across all supported engines.

Useful when

Where this command helps.

storing user permissions (read, write, delete, admin) in a single integer column on a users table
querying which feature flags are enabled for a set of accounts without joining a separate flags table

Explanation

What the command is doing.

A bitmask stores many independent true/false flags as powers of two in a single integer column. Each flag occupies one bit position: READ = 1 (2⁰), WRITE = 2 (2¹), DELETE = 4 (2²), ADMIN = 8 (2³). A user with READ and DELETE has permissions = 5 (binary 101). To test whether a flag is set, apply bitwise AND: permissions & 4 = 4 is true only when bit 2 is set. To grant a flag: permissions | 4. To revoke a flag: permissions & ~4 (all bits except 4). This pattern is compact and fast — a single indexed INT column can represent 32 flags. The trade-off is readability: raw integers are opaque without documentation. All major SQL engines support bitwise AND (&) in WHERE clauses. SQL Server uses ~ for bitwise NOT; the others use ~ as well. Bitwise OR (|) and XOR (^) are also universally supported.

Store and Query Bitwise Permission Flags

Find users who have the WRITE permission bit set

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.