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!

  • 1
    There's really nothing wrong with 1000s of concurrent connections. There's usually no need for them to be persistent. The ability to switch roles means that its effectively the highest privilege. What are you trying to achieve? – danblack Nov 13 '18 at 22:56

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
  -- do something

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.

  • Your answer is quite appreciated Mustaccio. The approach you propose will work well for my needs. I am impressed that you also mentioned about concurrency because that was part of my concerns as multi-threading is important to my project. Very complete answer. Thank you much! – Jeff Reeves Nov 14 '18 at 0:35
  • Multithreading (along with locking and transactions) are things database engines do well. Avoid trying to implement those yourself highly recommended. – danblack Nov 14 '18 at 0:47

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

  • Thanks danblack. Indeed, it seems that switching between roles would allow me to do what I am looking for. – Jeff Reeves Nov 14 '18 at 0:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.