Database Permissions Bitmask
How SQL databases encode SELECT, INSERT, UPDATE, DELETE and other privileges into a single integer using binary flags — with MySQL and PostgreSQL examples.
CRUD Bit Positions
The most straightforward example of database permission bitmasks is the CRUD model — SELECT, INSERT, UPDATE, DELETE. Each of these four operations gets a distinct bit position in a permission mask. I have implemented this pattern in custom application-level permission systems countless times, and it works exactly like the bit flags we have discussed throughout this site.
| Permission | Bit | Decimal | Binary |
|---|---|---|---|
| SELECT | 0 | 1 | 0001 |
| INSERT | 1 | 2 | 0010 |
| UPDATE | 2 | 4 | 0100 |
| DELETE | 3 | 8 | 1000 |
The pattern is the same as Linux rwx but with different labels. Instead of r=4, w=2, x=1, we have SELECT=1, INSERT=2, UPDATE=4, DELETE=8. The bitwise combination rules are identical:
In a previous project I used a PostgreSQL integer column as a bitmask for user permissions. Querying who can export data became a simple WHERE permissions & 8 = 8 instead of a complex join across multiple permission tables.
Read + write apps: 1|2|4 = 7 = 0111
Full CRUD (no DDL): 1|2|4|8 = 15 = 1111
INSERT only (bulk load): 2 = 0010
UPDATE + SELECT (editors): 1|4 = 5 = 0101
Check if UPDATE is allowed: mask & 4 !== 0
In my application code, I define these as constants and combine them with bitwise OR at role creation time. This makes role management trivial — adding "can update" means ORing in the UPDATE bit, and revoking it means ANDing with the complement. The entire permission check becomes a single CPU instruction.
Bitmask Check in Application Code
In any language with bitwise operators: if (user_role.mask & PERMISSION_SELECT) { /* allow SELECT */ }. This is a single CPU-level AND instruction — far faster than comparing strings or looking up rows in a pivot table. Use our bitwise calculator to test different mask combinations.
MySQL Privilege Bit Positions
MySQL uses a more extensive set of privilege bits than the basic CRUD model. The MySQL privilege system assigns specific bit positions to each privilege level. Here is the complete table-level privilege mask I have compiled from MySQL source code analysis:
| Privilege | Decimal | Binary | SQL Keyword |
|---|---|---|---|
| SELECT | 1 | 00000000001 | SELECT |
| INSERT | 2 | 00000000010 | INSERT |
| UPDATE | 4 | 00000000100 | UPDATE |
| DELETE | 8 | 00000001000 | DELETE |
| CREATE | 16 | 00000010000 | CREATE |
| DROP | 32 | 00000100000 | DROP |
| REFERENCES | 64 | 00001000000 | REFERENCES |
| INDEX | 128 | 00010000000 | INDEX |
| ALTER | 256 | 00100000000 | ALTER |
| CREATE VIEW | 512 | 01000000000 | CREATE VIEW |
| SHOW VIEW | 1024 | 10000000000 | SHOW VIEW |
The pattern is obvious: each privilege is exactly one bit in an 11-bit mask. The total mask for "all table privileges" would be 1 + 2 + 4 + 8 + 16 + 32 + 64 + 128 + 256 + 512 + 1024 = 2047, which in binary is 11111111111 — all 11 bits set.
Internal bitmask = 2047 (0x7FF)
Binary: 11111111111
Bit positions: SELECT(1) INSERT(2) UPDATE(4) DELETE(8)
CREATE(16) DROP(32) REFERENCES(64)
INDEX(128) ALTER(256) CREATE VIEW(512) SHOW VIEW(1024)
MySQL Internal Privilege Bitmasks
MySQL version 8.0 and later uses a different approach internally. The mysql.user table stores privileges as SET columns (like Select_priv with values 'N' or 'Y'), but the internal access check converts these to a bitmask for performance. I have worked with MySQL internals during performance tuning, and understanding the bitmask conversion helped me write more efficient GRANT queries.
Here is how MySQL checks whether a user has a specific privilege:
#define SELECT_ACL (1UL << 0) // 1
#define INSERT_ACL (1UL << 1) // 2
#define UPDATE_ACL (1UL << 2) // 4
#define DELETE_ACL (1UL << 3) // 8
#define CREATE_ACL (1UL << 4) // 16
#define DROP_ACL (1UL << 5) // 32
ulong db_access = ... // bitmask from mysql.db table
if (db_access & SELECT_ACL) {
// User can SELECT from this database
}
MySQL also defines higher-level privilege masks for convenience. These combine multiple individual privileges into a single named mask:
| Named Mask | Decimal | Binary | Included Privileges |
|---|---|---|---|
| TABLE_ACLS | 2047 | 11111111111 | All 11 table-level privileges |
| DB_ACLS | 268435455 | 28-bit mask | All database-level privileges |
| GLOBAL_ACLS | 1073741823 | 30-bit mask | All global privileges |
The beauty of this system is that checking whether a user can perform an operation is a single bitwise AND instruction. When you run SELECT ... FROM mytable, MySQL checks user_access & SELECT_ACL in one CPU cycle, not a multi-join query across permission tables. This is why bitmask-based permission systems are so fast.
PostgreSQL ACL Bitmasks
PostgreSQL takes a different approach to the same core idea. Instead of storing a single bitmask integer, PostgreSQL stores ACL arrays — arrays of ACL items, where each item contains a grantee, a grantor, and a permissions bitmask. The bitmask inside each ACL item follows the same power-of-2 pattern.
| Permission | Char | Decimal | Binary |
|---|---|---|---|
| SELECT | r | 1 | 000000000001 |
| INSERT | a | 2 | 000000000010 |
| UPDATE | w | 4 | 000000000100 |
| DELETE | d | 8 | 000000001000 |
| TRUNCATE | D | 16 | 000000010000 |
| REFERENCES | x | 32 | 000000100000 |
| TRIGGER | t | 64 | 000001000000 |
| CREATE | C | 128 | 000010000000 |
| CONNECT | c | 256 | 000100000000 |
| TEMPORARY | T | 512 | 001000000000 |
| EXECUTE | X | 1024 | 010000000000 |
| USAGE | U | 2048 | 100000000000 |
In PostgreSQL, you can query the ACL directly using aclexplode() or read the relacl column from pg_class. The ACL items are stored in text format like {user=arwdDxt/postgres}. The letters between = and / are the permission bits that are set.
Grantee: app_user
Letters: a r w d
Meaning: INSERT(2) SELECT(1) UPDATE(4) DELETE(8)
Internal bitmask: 1 | 2 | 4 | 8 = 15 = 1111
Permissions: INSERT + SELECT + UPDATE + DELETE
In my experience, the PostgreSQL ACL text format is actually quite readable once you memorize the letter-to-bit mapping. The letters are case-sensitive: lowercase for table-level privileges, uppercase for database-level privileges. The bitmask inside the database is still a binary integer — the text representation is just for human readability in psql output.
Bitwise Queries in SQL
When you implement a custom permission bitmask in your own application database, you can use SQL's built-in bitwise operators to query permissions directly. This is far more efficient than string-based permission lookups.
SELECT username FROM users
WHERE permissions & 4 = 4;
-- Find all users with SELECT + INSERT
SELECT username FROM users
WHERE permissions & (1|2) = 3;
-- Grant DELETE permission to user 42
UPDATE users SET permissions = permissions | 8
WHERE id = 42;
-- Revoke INSERT permission from all users
UPDATE users SET permissions = permissions & ~2;
-- Find users with full CRUD (bits 0-3)
SELECT username FROM users
WHERE (permissions & 15) = 15;
-- Add CREATE permission (bit 4 = 16)
UPDATE users SET permissions = permissions | 16
WHERE role = 'editor';
-- Check a specific permission in a query
SELECT
CASE WHEN permissions & 1 > 0 THEN 'yes' ELSE 'no' END AS can_select
FROM users WHERE id = 42;
I strongly recommend using explicit bitmask constants in your schema rather than magic numbers. Define your permission values in a comment or an application enum, then use MySQL @variable or PostgreSQL CONSTANT to make the queries self-documenting. And always test the exact bit combination with our bitwise calculator before deploying permission changes.
Test Database Permission Masks
Use the bitwise calculator to combine SELECT, INSERT, UPDATE, DELETE and other privilege bits. See how different permission sets look in binary.
Frequently Asked Questions About Database Permissions Bitmask
How are database permissions stored as a bitmask?
Database permissions are stored as a bitmask integer where each permission is assigned a power-of-2 value. MySQL uses this pattern: SELECT=1 (binary 0001), INSERT=2 (0010), UPDATE=4 (0100), DELETE=8 (1000). Combining permissions is done with bitwise OR. For example, SELECT | INSERT | UPDATE = 1 | 2 | 4 = 7 (binary 0111). Checking a permission uses bitwise AND: if (mask & INSERT) checks if the INSERT bit is set.
How does MySQL store privileges as a bitmask?
MySQL stores user privileges in the mysql.user table as bitmask columns. Each privilege (Select_priv, Insert_priv, etc.) is stored as an ENUM('N','Y') which maps to a single bit in a larger bitmask used internally. The mysql.user table has individual Y/N columns, but internally MySQL converts these to a bitmask for fast checking. When you query information_schema, the privileges are shown as individual columns, but the internal access check uses a bitwise comparison of masks.
What is the binary representation of CRUD permissions combined?
Full CRUD permissions SELECT (1), INSERT (2), UPDATE (4), DELETE (8) combine via OR to 15 (binary 1111). Read-only (SELECT only) is 1 (0001). Read-write (SELECT+INSERT+UPDATE) is 7 (0111). Full CRUD (all four) is 15 (1111). Each additional permission like CREATE (16) or DROP (32) adds another bit. A user with SELECT, INSERT, UPDATE, DELETE, CREATE, DROP would have mask value 63 (111111).
What bits do MySQL table-level privileges use?
MySQL table-level privileges use these bit positions: SELECT=1 (bit 0), INSERT=2 (bit 1), UPDATE=4 (bit 2), DELETE=8 (bit 3), CREATE=16 (bit 4), DROP=32 (bit 5), REFERENCES=64 (bit 6), INDEX=128 (bit 7), ALTER=256 (bit 8), CREATE VIEW=512 (bit 9), SHOW VIEW=1024 (bit 10), TRIGGER=2048 (bit 11). These combine into a single integer mask using bitwise OR.
How do PostgreSQL ACL bitmasks work?
PostgreSQL uses ACL (Access Control List) item bitmasks with a similar structure. Each ACL item contains a 32-bit permissions bitmask where: SELECT=r (1), INSERT=a (2), UPDATE=w (4), DELETE=d (8), TRUNCATE=D (16), REFERENCES=x (32), TRIGGER=t (64), CREATE=C (128), CONNECT=c (256), TEMPORARY=T (512), EXECUTE=X (1024), USAGE=U (2048). These are stored as single-character identifiers in the ACL array but map to the same power-of-2 bit pattern.
Can I use bitwise operators in SQL queries?
Yes, both MySQL and PostgreSQL support bitwise operators (&, |, ~, <<, >>) directly in SQL. You can use them in WHERE clauses to filter by permission mask, in UPDATE statements to modify permissions, and in SELECT expressions to check individual bits. MySQL also provides the BIT_COUNT() function to count set bits, which is useful for determining the scope of permissions.
How many permissions can I store in a 32-bit integer?
A 32-bit integer can store 32 independent permission flags (bits 0 through 31). MySQL currently uses about 30 bits for global privileges and 11 for table privileges. PostgreSQL uses 12 defined permissions but has room for expansion in its 32-bit ACL mask. For most applications, a 32-bit integer is more than sufficient; if you need more than 32 permissions, you can use a BIGINT (64-bit) or multiple bitmask columns.