0

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?

4
  • 1
    Why write any custom code instead of using SQL Server's built-in security to grant/revoke various permissions? By doing this in the application, you will almost certainly miss edge cases, cause unintended side effects, and completely miss the scenario in which a malicious actor bypasses the application altogether. – alroc Feb 10 '20 at 13:57
  • I'd take a look at building an API, then have people send their requests through that. You may also need to move your application to Oracle - it gives you much finer control over what your users can see and use. – James Feb 10 '20 at 14:58
  • @alroc I don't want to do this in my application. Where did you read this misunderstanding in my text? – liqSTAR Feb 11 '20 at 6:22
  • @James an API is in planning. But if we would filter inside the API we need to this in every API or we write an API-Facade. – liqSTAR Feb 11 '20 at 6:23
6

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.

1
  • Thanks for you answer. YOu confirmed my thoughts and I needed an third opinion. Maybe I was missing a way. – liqSTAR Feb 11 '20 at 6:25

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.