Skip to main content
Information Security

Return to Answer

added 42 characters in body
Source Link
schroeder ♦
schroeder ♦
  • 132.1k
  • 55
  • 307
  • 348

There are pros and cons, but IF you are going to expose the database to your client, make it a small attack surface by only giving them access to a particular schema. This schema would only contain stored procedures that you will allow them to run. This will mitigate against SQL injection attacks, the authorization the user has depends on the SQL authorization. 

If you want different customers to only be able to access their own records, and have different authorizations for different people in the same customer organization, you can do all of that inside larger and larger stored procsprocedures. In essence, you are building your own API inside the stored procprocedure, and if you are about to do this, you are better off maintainability-wise, having your own API layer in the middle tier. If you have complex business logic, both performance-wise and maintenance wise, it is better in a middle tier than in a sprocstored procedure. So

So in summary, you can put everything in the SQL database and sprocsstored procedures if you want to. Both function-wise and security-wise you can get it to work with a small attack surface. But if you have a complex system, and you understand what is involved, most of the time you won't want to.

There are pros and cons, but IF you are going to expose the database to your client, make it a small attack surface by only giving them access to a particular schema. This schema would only contain stored procedures that you will allow them to run. This will mitigate against SQL injection attacks, the authorization the user has depends on the SQL authorization. If you want different customers to only be able to access their own records, and have different authorizations for different people in the same customer organization, you can do all of that inside larger and larger stored procs. In essence you are building your own API inside the stored proc, and you are about to do this, you are better off maintainability-wise, having your own API layer in the middle tier. If you have complex business logic, both performance-wise and maintenance wise, it is better in a middle tier than in a sproc. So in summary, you can put everything in the SQL database and sprocs if you want to. Both function-wise and security-wise you can get it to work with a small attack surface. But if you have a complex system, and you understand what is involved, most of the time you won't want to.

There are pros and cons, but IF you are going to expose the database to your client, make it a small attack surface by only giving them access to a particular schema. This schema would only contain stored procedures that you will allow them to run. This will mitigate against SQL injection attacks, the authorization the user has depends on the SQL authorization. 

If you want different customers to only be able to access their own records, and have different authorizations for different people in the same customer organization, you can do all of that inside larger and larger stored procedures. In essence, you are building your own API inside the stored procedure, and if you are about to do this, you are better off maintainability-wise, having your own API layer in the middle tier. If you have complex business logic, both performance-wise and maintenance wise, it is better in a middle tier than in a stored procedure.

So in summary, you can put everything in the SQL database and stored procedures if you want to. Both function-wise and security-wise you can get it to work with a small attack surface. But if you have a complex system, and you understand what is involved, most of the time you won't want to.

Source Link
Steve Morrison
Steve Morrison
  • 11
  • 1

There are pros and cons, but IF you are going to expose the database to your client, make it a small attack surface by only giving them access to a particular schema. This schema would only contain stored procedures that you will allow them to run. This will mitigate against SQL injection attacks, the authorization the user has depends on the SQL authorization. If you want different customers to only be able to access their own records, and have different authorizations for different people in the same customer organization, you can do all of that inside larger and larger stored procs. In essence you are building your own API inside the stored proc, and you are about to do this, you are better off maintainability-wise, having your own API layer in the middle tier. If you have complex business logic, both performance-wise and maintenance wise, it is better in a middle tier than in a sproc. So in summary, you can put everything in the SQL database and sprocs if you want to. Both function-wise and security-wise you can get it to work with a small attack surface. But if you have a complex system, and you understand what is involved, most of the time you won't want to.