Access denied for calling a sequrity-type DEFINER procedure

[DavidLokison]

I am currently trying to set up a robust user system for my project where specific users are only granted exactly the permissions they need for executing their function, where I stumbled over this error: Seems the permission cascading only uses the permissions of the invoker of a procedure, even if explicitly specified as SQL SECURITY DEFINER (which is also the default in mysql).

$ mysql -u root -D dolt_testing
> CREATE TABLE t (id BINARY(2));
> CREATE PROCEDURE p () SQL SECURITY DEFINER SELECT id FROM t;
> CREATE USER testuser@localhost;
> GRANT EXECUTE ON PROCEDURE dolt_testing.p TO testuser@localhost;
> CALL p();
Empty set (0.00 sec)
>^Z
Bye
$ mysql -u testuser -D dolt_testing
> CALL p();
ERROR 1105 (HY000): Access denied for user 'testuser'@'localhost' to table 't'

[elianddb]

Taking a look at this one

[DavidLokison]

Just a heads-up: same thing applies to views too. If I create a view that I grant access to a user, but the user lacks the privileges for SELECTing the table itself, it will fail too. (strangely with a ERROR 1105 (HY000): command denied to user 'testuser'@'localhost' and not an access denied one...)

[DavidLokison]

Thank you very much for looking into this ❤️

[elianddb]

Thank you very much for looking into this ❤️

Of course, yw! Do you have a certain preference in the order I complete each? As @timsehn described this touches a couple of paths, so it'll take longer to create a framework. But once we have that base down I can see an easier implementation.

Also, from a high level glance, it looks like this security context hasn't been persisted/implemented, except for procedures slightly. So, when loading in older routines I'll default to an invoker context and offer a warning like 'missing security context definition, defaulting to invoker'. However other stored objects, like events and triggers, don't even allow a security context, so they may just need to be updated manually by the user (perhaps we can include a helper)?

If you have any suggestions, please let me know! I'll have to conversate with my team a bit more too.

I'm including views in the above*

[DavidLokison]

I'll default to an invoker context

Is there a particular reason why? Cause the mysql docs say that without explicitly declaring a security context, definer is used.

Do you have a certain preference in the order I complete each?

I don't have a specific preference cause all of them are basically used in tandem on my project anyways. So I'll just have some broader permissions entirely until the framework is settled in.

[elianddb]

I'll default to an invoker context

Is there a particular reason why? Cause the mysql docs say that without explicitly declaring a security context, definer is used.

This is mainly to not break other existing customers' workflows. To be clear, the default will be definer for new objects, but old objects that already relied on invoker will stay the same. The exception is events and triggers, they will be a breaking change because they don't allow the invoker security context.

I don't have a specific preference cause all of them are basically used in tandem on my project anyways. So I'll just have some broader permissions entirely until the framework is settled in.

Alright sounds good, I'll start working on this right away 🫡