MariaDB 13.1 Feature in Focus: DENY / Negative Grants

MariaDB 13.1 Preview is full of nice things.

Some are immediately visible to developers, like the new JSON operators. Some are very useful to DBAs, such as configuration validation. And some are about making security and access control easier to manage.

Today, let’s look at one of those: DENY, also known as negative grants.

Yes, negative grants.

It sounds a bit strange the first time you hear it, but the idea is very simple:

Sometimes it is easier to say “you can access everything here, except this”.

And until now, in MariaDB, that was not easy.

The Problem

MariaDB already has a very powerful privilege system.

You can grant privileges globally, per database, per table, per column, for routines, and so on. You can also use roles, which makes privilege management much nicer than assigning everything directly to every user.

But there is a classic problem.

Imagine you have a reporting user who should be able to read almost everything in a database:

GRANT SELECT ON shop.* TO 'reporting'@'%';

Easy.

But now imagine that the same database contains one table that should not be visible to that reporting user:

shop.customer_secrets

Oops.

With only positive grants, you cannot really say:

GRANT SELECT ON shop.* TO 'reporting'@'%'
EXCEPT shop.customer_secrets;

That syntax does not exist.

So what do you do?

You have to grant access table by table, and just omit the sensitive one:

GRANT SELECT ON shop.orders TO 'reporting'@'%';
GRANT SELECT ON shop.products TO 'reporting'@'%';
GRANT SELECT ON shop.categories TO 'reporting'@'%';
GRANT SELECT ON shop.customers TO 'reporting'@'%';
-- and many more...

That works, but it is painful.

It is also fragile.

Every time a new table is created, somebody must remember to grant access to it. If there are many tables, many environments, or many roles, this becomes boring very quickly.

And boring security administration is dangerous security administration.

DENY to the Rescue

With MariaDB 13.1, we now have the DENY clause for access control.

The idea is that a privilege can be explicitly denied. If a privilege is denied, the user cannot use it, even if that privilege would otherwise be available through another grant.

So the previous example becomes much easier to express:

GRANT SELECT ON shop.* TO 'reporting'@'%';

DENY SELECT ON shop.customer_secrets TO 'reporting'@'%';

Now the intention is clear:

• the reporting user can read the shop database
• but not the customer_secrets table

That’s it.

The denied resource stays denied until the deny is explicitly removed.

This is why the feature is also called negative grants. A normal grant says “yes”. A deny says “no”. And sometimes, having a clear “no” is exactly what we need.

Why This Matters

At first glance, DENY may look like a small SQL syntax improvement.

It is not.

It changes how we can model access.

Without DENY, administrators often need to choose between two imperfect options:

1. grant broadly and accept that access is too wide
2. grant very narrowly and maintain a long list of permissions manually

The first option is risky.

The second option is boring, error-prone, and difficult to maintain.

With DENY, we get a third option:

3. grant broadly where it makes sense, then explicitly block the exceptions

That is often much closer to how real organizations work.

Roles Become More Flexible Too

DENY becomes even more interesting when roles are involved.

Let’s say we have a role for analysts:

CREATE ROLE analyst;
GRANT SELECT ON app.* TO analyst;

Then we assign the role to users:

GRANT analyst TO 'alice'@'%';
GRANT analyst TO 'bob'@'%';

Now, imagine Bob should have the analyst role, but should not access one specific table.

Without negative grants, this is annoying. You may need a separate role, a separate list of table grants, or some other workaround.

With DENY, the intent is much clearer:

DENY SELECT ON app.audit_log TO 'bob'@'%';

Bob can still be an analyst.

Bob still gets the privileges from the role.

But Bob cannot read app.audit_log.

This is exactly the kind of exception that appears in real systems. Most people follow the standard access model, but there is always somebody, somewhere, who needs a small difference.

Now we can express that difference directly.

A Practical Example

Let’s create a simple situation.

We have an application database:

CREATE DATABASE app;

Inside it, we have normal application tables:

CREATE TABLE app.products (
  id int primary key,
  name varchar(100),
  price decimal(10,2)
);

CREATE TABLE app.orders (
  id int primary key,
  product_id int,
  quantity int
);

And we also have a more sensitive table:

CREATE TABLE app.audit_log (
  id int primary key,
  username varchar(100),
  action varchar(255),
  created_at timestamp
);

Now we create a reporting user:

CREATE USER 'reporting'@'%' IDENTIFIED BY 'strong_password_here';

The reporting user should read the application data:

GRANT SELECT ON app.* TO 'reporting'@'%';

But we don’t want this user to read the audit log:

DENY SELECT ON app.audit_log TO 'reporting'@'%';

Now the user can query:

SELECT * FROM app.products;
SELECT * FROM app.orders;

But not:

SELECT * FROM app.audit_log;

That is much easier than listing every allowed table one by one.

Roles Become More Flexible Too

DENY becomes even more interesting when roles are involved.

Let’s say we have a role for analysts:

CREATE ROLE analyst;
GRANT SELECT ON app.* TO analyst;

Then we assign the role to users:

GRANT analyst TO 'alice'@'%';
GRANT analyst TO 'bob'@'%';

Now, imagine Bob should have the analyst role, but should not access one specific table.

Without negative grants, this is annoying. You may need a separate role, a separate list of table grants, or some other workaround.

With DENY, the intent is much clearer:

DENY SELECT ON app.audit_log TO 'bob'@'%';

Bob can still be an analyst.

Bob still gets the privileges from the role.

But Bob cannot read app.audit_log.

This is exactly the kind of exception that appears in real systems. Most people follow the standard access model, but there is always somebody, somewhere, who needs a small difference.

Now we can express that difference directly.

A DBA-Friendly Feature

I really like features that make database administration less surprising.

DENY helps because it makes exceptions visible.

When we review privileges, a deny is explicit. It is not hidden in the absence of one grant among hundreds of other grants. It says:

This access is intentionally blocked.

That is useful for audits.

It is useful for security reviews.

It is useful for the next DBA who needs to understand why a user can read 99 tables but not the 100th one.

And of course, that “next DBA” may be you, six months later, after you have completely forgotten why you did it (it always happens to me, especially when I was writing Perl :P)

We have all been there.

Not a Replacement for Good Privilege Design

Of course, DENY does not mean we should grant everything everywhere and then try to fix it with denies.

Please don’t do that.

The principle of least privilege still applies. Users should receive only the privileges they need. Roles should still be designed carefully. Sensitive data should still be separated when appropriate.

DENY is not an excuse for messy privilege management.

It is a tool for cases where exceptions are cleaner than long allow-lists.

Used properly, it can make privilege management more readable, more maintainable, and less error-prone.

Preview Reminder

MariaDB 13.1 is a preview release.

That means this is the right time to test the feature, try your real-world access-control scenarios, and give feedback to the engineering team. This feature is developed by MDEV-14443.

Please don’t just read about it. Test it. Try the cases that are annoying in your environment today.

This is exactly why preview releases exist.

Conclusion

DENY is one of those features that looks simple but solves a very real problem.

It allows us to express something that DBAs and security teams often need:

Yes, but not that.

That may not look as flashy as a new storage engine or a major performance improvement, but for people managing database access in production, it can make their lives much easier.

MariaDB 13.1 Preview includes many community-powered improvements, and DENY is definitely one of the features I recommend testing.

Enjoy MariaDB and happy granting!

And denying.