SQL Statement Layer Filter Queries Limit Access

Question

Goal: Want to create a so called "SQL Statement Layer" which checks/filters all incoming queries. If incoming query is invalid, should be denied.

Use-case: Limit database access and avoid unrevertable queries. No duplicate code in multiple applications about the same "triggers or procedures" (centralized).

Research so far:

• GRANTS/Privileges
• limit to Stored Procedures

Description: I am standing before a new project and we want to limit and minimize the access to the database. For example: The developer can create a DROP query inside the Application layer but the database will deny this query because DROPqueries aren't allowed.

I worked some years ago with Laravel and did most of the "procedure and trigger" work inside the Application Layer like the Controllers instead of using a Mutator or Accessor. Now we want to cut it down to the lowest possible level to avoid duplicate code like Mutators and Accessors in different Applications.

Question: Are Grants and stored procedure the only way? If not, which ones?

Answer 1

I am not quite sure if using the built-in privilege system in combination with encapsulation of some or all of the SQL code in stored procedures is the only way, but it certainly is the sane way of going about restricting access to the database. You are free to introduce your own system, of course, but from what I have learnt this far, there is no need to reinvent the wheel.

Take your time deciding what different levels of access you may need, and create database roles accordingly, granting different kinds of privileges to different objects according to the role definitions. Create the user accounts and assign them to the roles. Have your application provide a dialogue box requesting login credentials and use those to invoke whatever SQL instruction needs to be executed, be it a stored procedure call or an ad-hoc query. The instruction will be executed – or not – based on the privileges decided at the server level.

Again, this is the usual way of managing database access. Unusual scenarios may well call for unusual approaches, but if the reason for not using the built-in security is, for instance, simply because it makes the developer's life easier, my advice would be to reconsider.