CODEWITHSUNDEEP
mysqlsecuritypermissionsmariadbfivem
Stephen York
Stephen York

Reputation: 1429

Restricting commands to procedure only in MySQL / MariaDB

I've put together a FiveM server using alot of public code and discovered there are cheat systems out there that allow the user to corrupt or delete the underlying database. The reason is because they can inject Lua scripts which can contain DROP, DELETE INSERT and UPDATE and if they know the schema potentially could do whatever they like.

My intention is to deny access to every command except for SELECT and move all the other logic to stored procedures. The thing is that the user executing the proc will be the game user account which if locked out would also be blocked server side? Am I able to deny access from calling applications but allow access from within a stored procedure or have the procs execute as a different account vs the normal SELECT statements? Are there any other considerations or designs that could work? I'll be using parameters across all calls to help guard against injection, but I'm fairly new to MySQL so wondering what other steps people take for these scenarios.

Upvotes: 1

Views: 484

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562358

Yes, you can give the MySQL user privilege to call procedures only. Then the procedures execute with the privileges of the user who defined the procedure.

Read https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html the parts about SQL SECURITY which has choices DEFINER or INVOKER. The default is DEFINER, which is what you want.

However, you would also need to deny SELECT privilege to the app user. A malicious user can cause problems with nothing but SELECT privilege. They can't change data, but they can overload the database server.

So you'd need to implement every database query, both reads and writes, in a collection of stored procedures.

Here's an alternative suggestion: Allow the app to work as it does today, where the app connects using its username and does SQL queries directly.

But if the user wants to invoke their Lua script, only allow that on a separate database connection, using a different MySQL user with limited privileges. Basically only the EXECUTE privilege on a specific schema. You can implement a set of stored procedures that the Lua script is allowed to run, and put them in that schema. Then Lua scripts cannot do other tasks that the app does, a Lua script can only run the finite set of procedures you want to allow them to run.

Upvotes: 1

Related Questions