What REVOKE really revokes — clickhouse.guru

The question came from a multi-tenant setup: one account holds server-wide rights, and one database must stay out of its reach. The obvious statements are:

GRANT SELECT ON *.* TO admin_u;
REVOKE SELECT ON secret.* FROM admin_u;

Does the second line punch a hole in the first, or does the last statement win, or does ClickHouse reject the pair? A colleague had checked on a throwaway single node and found it works. The job was to confirm it on a production-shaped cluster, where there are roles, replicated or per-node access storage, and ON CLUSTER. What follows is the semantics, reproduced on clickhouse local 26.4 against system.grants, plus the places where a single node does not tell you the truth.

The model: grants are rows, revokes are rows too

ClickHouse does not store “effective permissions”. It stores grant rows, and a revoke that cuts a hole in a wider grant is stored as a negative row. You can see both in system.grants; the column that distinguishes them is is_partial_revoke.

CREATE USER u1 IDENTIFIED WITH no_password;
GRANT SELECT, INSERT ON *.* TO u1;
REVOKE SELECT ON secret.* FROM u1;
SHOW GRANTS FOR u1;
SELECT access_type, database, is_partial_revoke FROM system.grants WHERE user_name = 'u1';

GRANT SELECT, INSERT ON *.* TO u1
REVOKE SELECT ON secret.* FROM u1
SELECT  \N      0
INSERT  \N      0
SELECT  secret  1

SHOW GRANTS prints a GRANT line and a REVOKE line together, which is the first thing that surprises people who expect the statements to cancel. They do not cancel; they are both kept, and evaluation takes the narrowest matching scope. The user can SELECT everywhere except secret, and can still INSERT into secret, because the revoke named only SELECT.

That last point is the common mistake in the other direction. The fence is per access type. REVOKE SELECT does not fence INSERT, ALTER, DROP or SHOW. If the aim is “this account must not touch the database”, say REVOKE ALL ON secret.*, which cuts every privilege that was actually granted. Revoking ALL from an account that only holds SELECT produced exactly one negative row, for SELECT; ALL expands to whatever is there.

Revoking a subset of a wildcard works at any level

The hole does not have to be a database. The same rule applies to tables inside a database grant and to columns inside a table grant:

GRANT SELECT ON app.* TO u2;
REVOKE SELECT ON app.audit FROM u2;

GRANT SELECT ON app.t TO u3;
REVOKE SELECT(x) ON app.t FROM u3;

SELECT user_name, access_type, database, table, column, is_partial_revoke
FROM system.grants WHERE user_name IN ('u2', 'u3') ORDER BY user_name, is_partial_revoke;

u2  SELECT  app  \N     \N  0
u2  SELECT  app  audit  \N  1
u3  SELECT  app  t      \N  0
u3  SELECT  app  t      x   1

Each level is just a row with a more specific scope, and a negative row only has meaning relative to a positive row above it. A revoke with nothing above it to cut writes nothing: revoking INSERT ON app.* from an account that never had INSERT returned success and left SHOW GRANTS empty. That is correct, and it is also why a typo in a revoke is silent.

The fence survives, and it is not a lock

Three properties that matter if you are using a partial revoke as a security boundary:

It survives the object. I dropped the database and recreated it with the same name; SHOW GRANTS still printed the REVOKE ... ON secret.*. The row is keyed by the name, so a recreated secret is fenced from the start, which is what you want.

It is erased by a wider GRANT. This is the one to remember:

GRANT SELECT ON *.* TO v1;
REVOKE SELECT ON secret.* FROM v1;
GRANT SELECT ON *.* TO v1;     -- the same wide grant, issued again
SHOW GRANTS FOR v1;

GRANT SELECT ON *.* TO v1

The second GRANT was not a no-op. It subsumed the negative row and the fence is gone, with no warning. Anything that re-applies grants from a script on every deploy (a declarative RBAC sync, a provisioning job) will quietly remove partial revokes unless the script also re-issues the revokes after the grants. The same thing happens in finer grain: granting SELECT ON secret.* back to a fenced account folds into the wide grant instead of creating a second positive row.

It does not need WITH GRANT OPTION anywhere to hold, but it interacts with it. An account with WITH GRANT OPTION on the wide grant can, in principle, hand out what it has:

GRANT SELECT ON *.* TO v4 WITH GRANT OPTION;
REVOKE SELECT ON secret.* FROM v4;
SELECT access_type, database, grant_option, is_partial_revoke FROM system.grants WHERE user_name = 'v4' ORDER BY is_partial_revoke;

SELECT  \N      1  0
SELECT  secret  0  1

The positive row carries grant_option = 1; the negative row has grant_option = 0. Inside secret the account holds no privilege and no grant option, so it cannot re-grant itself access there. The fence therefore resists self-service undo, which is what makes it useful against an account that can otherwise administer grants. Two things still break it, and neither shows up in a one-node test: any other account that can GRANT on secret.* (a second administrator, or the original GRANT ... ON *.* WITH GRANT OPTION holder if it is not the fenced account) can undo the fence for that account, and so can an account that can drop and recreate the user. A fence is a rule against the fenced account, not against your own administration.

To remove only the ability to delegate while keeping the privilege, use REVOKE GRANT OPTION FOR:

REVOKE GRANT OPTION FOR SELECT ON *.* FROM u;
-- result: GRANT SELECT ON *.* TO u

I ran that on clickhouse local and the WITH GRANT OPTION tail was gone from SHOW GRANTS.

Roles: where REVOKE becomes a silent no-op

The trap is that REVOKE acts on the grantee you name, and only on that grantee’s own rows. A user who receives a privilege through a role has no row of their own to cut.

CREATE ROLE reader;
GRANT SELECT ON *.* TO reader;
GRANT reader TO v2;
REVOKE SELECT ON secret.* FROM v2;     -- succeeds
SHOW GRANTS FOR v2;

GRANT reader TO v2

The REVOKE returned without error, and SHOW GRANTS FOR v2 is unchanged. Nothing in system.grants was written for v2; the only SELECT row in the picture still belongs to reader, and v2 still has everything reader has. On a real cluster the check that matters is a read of the fenced database as that user afterwards, and there the read still succeeded. The statement is not an error because from the access system’s side it is valid and has nothing to cut, the same as revoking something never granted.

The fix is to revoke where the privilege lives, on the role:

REVOKE SELECT ON secret.* FROM reader;
SHOW GRANTS FOR reader;

GRANT SELECT ON *.* TO reader
REVOKE SELECT ON secret.* FROM reader

That fences every member, which may be too many. Two variants: split the role (a reader_all and a reader_but_secret) and move members between them, or give the single account a role-free, direct set of grants. A direct grant can also paper over a role fence: I gave a user a role with a fence and then a direct GRANT SELECT ON *.* TO beside it, and SHOW GRANTS listed both. The direct row is unfenced, so the effective access is the union. Denials do not compose across sources; grants do.

A practical audit for this class of mistake is to look for users who hold a privilege only through a role, and then check that the revokes you think you have actually exist where you think:

SELECT user_name, role_name, access_type, database, table, is_partial_revoke
FROM system.grants
WHERE is_partial_revoke
ORDER BY coalesce(user_name, role_name);

If the fence you need is not in this list under the right grantee, it is not there.

What a single node cannot tell you

These parts need a server or more than one node. clickhouse local could not run them here: EXECUTE AS (impersonation) is off unless the server config enables access_control_improvements.allow_impersonate_user, and the local binary did not pick the setting up from a config file, so every statement above was checked by reading system.grants and SHOW GRANTS, not by watching a read fail. The statements parse; clickhouse format accepted all of them:

REVOKE SELECT ON secret.* FROM u ON CLUSTER my_cluster;
GRANT reader TO u WITH ADMIN OPTION;
GRANT CURRENT GRANTS(ALL ON *.*) TO u;
GRANT ROLE ADMIN ON *.* TO u;
EXECUTE AS u SELECT 1;
CHECK GRANT SELECT ON secret.t;

What the documentation says, and what to check on your own server before relying on it:

Access storage is per node unless it is replicated. Users and grants created with plain SQL live in a local directory on the node that ran the statement. On a cluster without a replicated access storage, run it with ON CLUSTER or repeat it on every node. A fence applied to one node only is enforced on whichever node a load balancer happens to pick. Users defined in users.xml are read-only, so you cannot revoke from them with SQL at all; check system.users.storage for where an account actually lives.
ROLE ADMIN is a privilege, not a role. It lets the holder grant or revoke roles that they do not hold with ADMIN OPTION. It carries no data privilege on its own, but if any powerful role exists on the server, an account with ROLE ADMIN is one GRANT away from it. A partial-revoke fence on a user does not stop the user from being given another role that is not fenced. Keep an eye on who has it; ALL ON *.* folds it in, so system.grants will not show a separate ROLE ADMIN row for such accounts.
GRANT CURRENT GRANTS copies the fence. Cloning a user’s grants to another account, which is the way an administrator can hand out exactly what they hold, carries the negative rows with it. The fence is inherited rather than escaped.
GRANT ALL ON *.* needs the granter to hold everything. On clickhouse local the built-in user lacks SHOW NAMED COLLECTIONS SECRETS, and GRANT ALL ON *.* to another account failed with ACCESS_DENIED for that reason; I used explicit privileges in the examples. On a server where the administrator is an ALL ON *.* WITH GRANT OPTION account this is not an issue, and the error message points at GRANT CURRENT GRANTS as the way to hand out a subset.

The grant and revoke syntax, including partial revokes, wildcards and WITH GRANT OPTION, is in the reference pages for GRANT and REVOKE.

The short version

GRANT ... ON *.* followed by REVOKE ... ON db.* keeps both rows. The narrower scope wins. system.grants.is_partial_revoke tells them apart.
The fence is per privilege type. Use REVOKE ALL if you mean “nothing in this database”.
It works at database, table and column granularity, and a revoke with nothing above it to cut is a silent no-op.
Re-issuing the wide GRANT erases the fence. Order your provisioning scripts: grants first, revokes last, every time.
Revoke from the grantee that holds the privilege. A role member’s REVOKE does nothing; revoke on the role, split the role, or change membership.
Direct grants are additive with role grants, so denials on one source do not remove access that arrives from another.
On a cluster, apply it everywhere (ON CLUSTER or a replicated access storage), and verify with a read as the fenced account, which is the one check this note could not do on a single local process.

For the surrounding setup see the users, settings and security runbook. One adjacent limit: GRANT has no TO ALL form that tracks every user, unlike row policies and quotas; clickhouse format renders GRANT SELECT ON *.* TO ALL with ALL quoted, as a role name. That is the subject of the feature request on GRANT ... TO ALL.