Alonside existing GRANT and REVOKE privilege add DENY privilage

Microsoft SQL Server has three options: a) GRANT privilege, b) REVOKE privilege and c) DENY privilege. But IBM Db2 only has two: a) GRANT privilege and b) REVOKE privilege.

How it should work? When ever there are two privileges specified GRANT and DENY, deny always has precedence over grant.

Sample 1:

GRANT SELECTIN ON SCHEMA myschema TO USER user1;

DENY SELECT ON TABLE myschema.mytable TO USER user1;

means user1 can access all objects (including tables) on myschema, except it can not access table myschema.mytable.

Sample 2:

GRANT DATAACCESS ON DATABASE TO USER user1;
DENY SELECTIN ON SCHEMA myschema TO USER user1;

means user1 can access SELECT/INSERT/UPDATE/DELETE/etc on all objects in database, except objects in schema myschema.

Sample 3:

GRANT SELECT ON TABLE myschema.mytable TO USER user1;
DENY SELECTIN ON SCHEMA myschema TO USER user1;

because deny always has precedence over grant, above grant has no effect, because deny restricts the privilege.

When this is useful:

a) There are plenty of tables in myschema, but there is also myschema.secrets table, that user must not have access to. Currently the only way to do this is to obtain list of tables and exclude the table myschema.mytable and execute GRANT on list of tables. Obviously if new table is created in myschema schema, then manual GRANT is required on this new table.

b) When there are plenty of GRANT privileges on different levels and for testing purposes we want to e.g. temporally DENY privilege to some table (e.g. data are corrupted in that table) and DENY would be very handy.

c) When we don't know if some database object is still in use, we could DENY access on testing environment to the object and see if some error appears in unit tests. I know we could configure audit, but this is overkill for simple to see if some tests fail.