MariaDB "EXECUTE AS" or User Impersonation

Question

With MariaDB, is there a way to impersonate a user execution context when connected with a higher privileged account?

For instance, when connected with a "MasterUser" execute a SELECT using the more limited privileges of a "ClientUser".

The classic idea is to avoid the server application to perform thousands of connections to MariaDB -- if most requests could be handled through a "broker" user which would connect once and operate under the appropriate privilege level for each given transaction.

Thank you!

Answer 1

You can create a number of different roles and SET ROLE to switch between them.

Answer 2

It is indeed appropriate to avoid making thousands of connections to a database, but the solution to this particular problem is a connection pool, not an impersonating broker. By using a single connected broker to perform operations on other clients' behalf you severely restrict concurrency, because one connection (session) in MySQL (MariaDB), and the majority of other DBMSes, is serviced by a single thread. As a result, when using a single broker all clients' requests will have to be executed serially.

There are cases where you do want to use impersonation within a connection (while still having multiple concurrent connections), e.g. when different parts of your application business logic must access the database using different security contexts, and that can be implemented using database roles, as explained in another answer.

Another way to implement impersonation is by using routines (stored procedures and functions) that execute within their definer context. Consider

CREATE DEFINER = some_role PROCEDURE sp_name
...
SQL SECURITY DEFINER
BEGIN
  -- do something
END

The procedure above will execute SQL statements within it using the some_role authorization, regardless of what user invokes it. You will still need to define appropriate roles, but don't need to rely on the correct role selection in a session to enforce the role's authority.

Answer 3

The GRANT PROXY capability in MySQL and MariaDb are probably suitable - see https://mariadb.com/kb/en/grant/#proxy-privileges

The PROXY privilege allows one user to proxy as another user, which means their privileges change to that of the proxy user, and the CURRENT_USER() function returns the user name of the proxy user.

It doesn't look as simple as the SQL Server API though, and probably not the same thing - https://learn.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-ver15