March 16, 2018 at 8:32 pm
You folks should all enjoy this memory of years ago when I was responsible for a large payroll/personnel system for a industrial manufacturer. The package was entirely in Cobol ( anybody remember that? ), and we received the source code for the package and did our own compiles. Now, to make things even more complex, my company insisted that we customize the package in many ways. So we maintained a master copy of the source code, on which we then overlaid the vendor's source code modifications, on which we then overlaid our modifications.
Now here's the hooker. This was before the days of even dumb terminals, so it was all accomplished with decks of 80 column cards and the changes had to be hand coded on paper and submitted to the keypunch department to have the cards created so they could be manually inserted into the card decks that were stored in large card file drawers. Drop one of those babies and you were in for an all night sorting session. Eventually we got it moved to a mag tape library and could do a line-number match for updates. Still had to keep our customizations on a separate tape and apply them right before compiles.
Oh, for the good old days...
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
March 17, 2018 at 3:23 am
Using profiler, extended events etc reveals what is being thrown at the DB but not how it is assembled. How it is assembled is important because you need to know what could be thrown, not just what has been thrown.
If you don't know how it is assembled how do you know that it is invulnerable to SQL Injection Attacks? People still write susceptible code. Boy do they write susceptible code!
With very few exceptions the databases I have worked with have data with value to a wider audience than the originating app. That meant I had to understand the schemas but also the access patterns in order to extract the data safely
March 17, 2018 at 5:35 am
bkubicek - Thursday, March 15, 2018 9:04 PMComments posted to this topic are about the item Your Thoughts On In-line SQL
We use both in-line and sproc-based code. Just finished a .NET utility to selectively copy tables from one db to another -- often on different servers in different environments (DEV, SIT, PAT, PROD etc). doing it with sprocs would mean a lot of dynamic SQL and the procs installed on every participating server. Since the program is run ad-hoc, that is not a viable approach. But I admit this is a special case.
Others mentioned EF and ORMs in general. Did anyone say LINQ to SQL? Love it or loathe it, it does make development easy for .net gurus who are SQL noobs. Of course it can use stored procedures too, but that can be less convenient and does require extra knowledge or coordination with SQL specialists. Then you have double-promotions: one for the app another for the db. Sometimes with different deployment teams and approval cycles.
I think someone mentioned SSIS (and lets include SSRS, SSAS). Those can use procs too, of course, but it is less convenient than a simple source component to destination data flow
Gerald Britton, Pluralsight courses
March 19, 2018 at 8:22 am
I second by your thoughts.
In-line SQL, not in line with Best Practices.
March 19, 2018 at 11:02 am
I have been an application developer since the late 1970s. I have also been doing my own DB work since the late 1980s. I agree 100% that stored procedures (SPs) are the right way to access data. If a case can be made for a specific use for in-line SQL, then fine, but I see that as a case-by-case decision, not suitable for generic guidelines.
I also prefer my SPs to do little more than CRUD operations. I stay away from triggers as much as possible. Business logic belongs in the application (IMHO), not in the database, unless a solid case in the context or performance, scalability, and maintainability can be made for a specific use case. That also makes the DB more scalable and improves performance.
March 19, 2018 at 1:42 pm
msbasssinger - Monday, March 19, 2018 11:02 AMI have been an application developer since the late 1970s. I have also been doing my own DB work since the late 1980s. I agree 100% that stored procedures (SPs) are the right way to access data. If a case can be made for a specific use for in-line SQL, then fine, but I see that as a case-by-case decision, not suitable for generic guidelines.I also prefer my SPs to do little more than CRUD operations. I stay away from triggers as much as possible. Business logic belongs in the application (IMHO), not in the database, unless a solid case in the context or performance, scalability, and maintainability can be made for a specific use case. That also makes the DB more scalable and improves performance.
So, curious, when do you actually use triggers for your stuff? Also, do you agree that not allowing an "unknown" to occur in the database is better done by a "NOT NULL" constraint on a table or do you enforce that business logic only in the front end?
I do agree that doing business logic in the right environment will definitely add to performance, scalability, and maintainability but I'd never say that business logic belongs (only) in the application even if there were no notable exceptions.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2018 at 5:26 am
Jeff Moden - Monday, March 19, 2018 1:42 PMmsbasssinger - Monday, March 19, 2018 11:02 AMI have been an application developer since the late 1970s. I have also been doing my own DB work since the late 1980s. I agree 100% that stored procedures (SPs) are the right way to access data. If a case can be made for a specific use for in-line SQL, then fine, but I see that as a case-by-case decision, not suitable for generic guidelines.I also prefer my SPs to do little more than CRUD operations. I stay away from triggers as much as possible. Business logic belongs in the application (IMHO), not in the database, unless a solid case in the context or performance, scalability, and maintainability can be made for a specific use case. That also makes the DB more scalable and improves performance.
So, curious, when do you actually use triggers for your stuff? Also, do you agree that not allowing an "unknown" to occur in the database is better done by a "NOT NULL" constraint on a table or do you enforce that business logic only in the front end?
I do agree that doing business logic in the right environment will definitely add to performance, scalability, and maintainability but I'd never say that business logic belongs (only) in the application even if there were no notable exceptions.
Personally I prefer to think of constraints, unique indexes, etc. as the data model and thus not part of the business logic. How you transform the information to conform to it is another matter entirely.
March 20, 2018 at 7:09 am
Jeff Moden - Monday, March 19, 2018 1:42 PMmsbasssinger - Monday, March 19, 2018 11:02 AMI have been an application developer since the late 1970s. I have also been doing my own DB work since the late 1980s. I agree 100% that stored procedures (SPs) are the right way to access data. If a case can be made for a specific use for in-line SQL, then fine, but I see that as a case-by-case decision, not suitable for generic guidelines.I also prefer my SPs to do little more than CRUD operations. I stay away from triggers as much as possible. Business logic belongs in the application (IMHO), not in the database, unless a solid case in the context or performance, scalability, and maintainability can be made for a specific use case. That also makes the DB more scalable and improves performance.
So, curious, when do you actually use triggers for your stuff? Also, do you agree that not allowing an "unknown" to occur in the database is better done by a "NOT NULL" constraint on a table or do you enforce that business logic only in the front end?
I do agree that doing business logic in the right environment will definitely add to performance, scalability, and maintainability but I'd never say that business logic belongs (only) in the application even if there were no notable exceptions.
When I use a trigger, it is to enforce some odd constraint that must be enforced under certain conditions or only at runtime, that is only suited to DB application instead of app application . I use constraints for whether a field can be null, default values, etc. Those are design elements that work well, My goal is to keep DB calls quick, short, and simple. When necessary,, I can control the transaction via the application, but only when necessary. I did not say business logic cannot be in the database, but that when you look at the any given use case for that, it is quite difficult from a value engineering perspective to justify it.
March 20, 2018 at 7:29 am
msbasssinger - Tuesday, March 20, 2018 7:09 AMJeff Moden - Monday, March 19, 2018 1:42 PMmsbasssinger - Monday, March 19, 2018 11:02 AMI have been an application developer since the late 1970s. I have also been doing my own DB work since the late 1980s. I agree 100% that stored procedures (SPs) are the right way to access data. If a case can be made for a specific use for in-line SQL, then fine, but I see that as a case-by-case decision, not suitable for generic guidelines.I also prefer my SPs to do little more than CRUD operations. I stay away from triggers as much as possible. Business logic belongs in the application (IMHO), not in the database, unless a solid case in the context or performance, scalability, and maintainability can be made for a specific use case. That also makes the DB more scalable and improves performance.
So, curious, when do you actually use triggers for your stuff? Also, do you agree that not allowing an "unknown" to occur in the database is better done by a "NOT NULL" constraint on a table or do you enforce that business logic only in the front end?
I do agree that doing business logic in the right environment will definitely add to performance, scalability, and maintainability but I'd never say that business logic belongs (only) in the application even if there were no notable exceptions.
When I use a trigger, it is to enforce some odd constraint that must be enforced under certain conditions or only at runtime, that is only suited to DB application instead of app application . I use constraints for whether a field can be null, default values, etc. Those are design elements that work well, My goal is to keep DB calls quick, short, and simple. When necessary,, I can control the transaction via the application, but only when necessary. I did not say business logic cannot be in the database, but that when you look at the any given use case for that, it is quite difficult from a value engineering perspective to justify it.
An interesting POV. I have to say I take the completely opposite view. SPs are a wonderful place to put business logic in addition to simple constraints, especially when you need transactions.
I certainly do agree that the server use should be minimized but in many cases keeping the logic inside SPs does exactly that, especially if the logic involves a lot of data from different tables. Security is also better since an SP is a black box with only known inputs and outputs. Having to make several calls to the DB to collect information required to implement complex logic in the app would seem to be counter productive. Sure, each call may involve only one small DB operation, but how many calls does a single business logic rule require? That also requires either A) direct table access or B) a loosening of the principle of least privilege, neither of which is (IMO) a good idea.
Also, there's the convenience factor. When BL is in SPs it's a lot easier to modify it without disturbing the application. Also, putting BL in the DB side means a (different) application can't freely ignore the business rules.
March 20, 2018 at 8:43 am
roger.plowman - Tuesday, March 20, 2018 7:29 AMmsbasssinger - Tuesday, March 20, 2018 7:09 AMJeff Moden - Monday, March 19, 2018 1:42 PMmsbasssinger - Monday, March 19, 2018 11:02 AMI have been an application developer since the late 1970s. I have also been doing my own DB work since the late 1980s. I agree 100% that stored procedures (SPs) are the right way to access data. If a case can be made for a specific use for in-line SQL, then fine, but I see that as a case-by-case decision, not suitable for generic guidelines.I also prefer my SPs to do little more than CRUD operations. I stay away from triggers as much as possible. Business logic belongs in the application (IMHO), not in the database, unless a solid case in the context or performance, scalability, and maintainability can be made for a specific use case. That also makes the DB more scalable and improves performance.
So, curious, when do you actually use triggers for your stuff? Also, do you agree that not allowing an "unknown" to occur in the database is better done by a "NOT NULL" constraint on a table or do you enforce that business logic only in the front end?
I do agree that doing business logic in the right environment will definitely add to performance, scalability, and maintainability but I'd never say that business logic belongs (only) in the application even if there were no notable exceptions.
When I use a trigger, it is to enforce some odd constraint that must be enforced under certain conditions or only at runtime, that is only suited to DB application instead of app application . I use constraints for whether a field can be null, default values, etc. Those are design elements that work well, My goal is to keep DB calls quick, short, and simple. When necessary,, I can control the transaction via the application, but only when necessary. I did not say business logic cannot be in the database, but that when you look at the any given use case for that, it is quite difficult from a value engineering perspective to justify it.
An interesting POV. I have to say I take the completely opposite view. SPs are a wonderful place to put business logic in addition to simple constraints, especially when you need transactions.
I certainly do agree that the server use should be minimized but in many cases keeping the logic inside SPs does exactly that, especially if the logic involves a lot of data from different tables. Security is also better since an SP is a black box with only known inputs and outputs. Having to make several calls to the DB to collect information required to implement complex logic in the app would seem to be counter productive. Sure, each call may involve only one small DB operation, but how many calls does a single business logic rule require? That also requires either A) direct table access or B) a loosening of the principle of least privilege, neither of which is (IMO) a good idea.
Also, there's the convenience factor. When BL is in SPs it's a lot easier to modify it without disturbing the application. Also, putting BL in the DB side means a (different) application can't freely ignore the business rules.
I would say that that is only the case when the application is only loosely tied to the database to the point where you can easily just slap another application on top of it. On the other hand a complex application that is tightly tied to the database should be just as secure as the database and changing logic at the DB level is just as likely to break the application as applying any changes directly in the application.
March 20, 2018 at 9:19 am
ZZartin - Tuesday, March 20, 2018 8:43 AMroger.plowman - Tuesday, March 20, 2018 7:29 AMmsbasssinger - Tuesday, March 20, 2018 7:09 AMJeff Moden - Monday, March 19, 2018 1:42 PMmsbasssinger - Monday, March 19, 2018 11:02 AMI have been an application developer since the late 1970s. I have also been doing my own DB work since the late 1980s. I agree 100% that stored procedures (SPs) are the right way to access data. If a case can be made for a specific use for in-line SQL, then fine, but I see that as a case-by-case decision, not suitable for generic guidelines.I also prefer my SPs to do little more than CRUD operations. I stay away from triggers as much as possible. Business logic belongs in the application (IMHO), not in the database, unless a solid case in the context or performance, scalability, and maintainability can be made for a specific use case. That also makes the DB more scalable and improves performance.
So, curious, when do you actually use triggers for your stuff? Also, do you agree that not allowing an "unknown" to occur in the database is better done by a "NOT NULL" constraint on a table or do you enforce that business logic only in the front end?
I do agree that doing business logic in the right environment will definitely add to performance, scalability, and maintainability but I'd never say that business logic belongs (only) in the application even if there were no notable exceptions.
When I use a trigger, it is to enforce some odd constraint that must be enforced under certain conditions or only at runtime, that is only suited to DB application instead of app application . I use constraints for whether a field can be null, default values, etc. Those are design elements that work well, My goal is to keep DB calls quick, short, and simple. When necessary,, I can control the transaction via the application, but only when necessary. I did not say business logic cannot be in the database, but that when you look at the any given use case for that, it is quite difficult from a value engineering perspective to justify it.
An interesting POV. I have to say I take the completely opposite view. SPs are a wonderful place to put business logic in addition to simple constraints, especially when you need transactions.
I certainly do agree that the server use should be minimized but in many cases keeping the logic inside SPs does exactly that, especially if the logic involves a lot of data from different tables. Security is also better since an SP is a black box with only known inputs and outputs. Having to make several calls to the DB to collect information required to implement complex logic in the app would seem to be counter productive. Sure, each call may involve only one small DB operation, but how many calls does a single business logic rule require? That also requires either A) direct table access or B) a loosening of the principle of least privilege, neither of which is (IMO) a good idea.
Also, there's the convenience factor. When BL is in SPs it's a lot easier to modify it without disturbing the application. Also, putting BL in the DB side means a (different) application can't freely ignore the business rules.
I would say that that is only the case when the application is only loosely tied to the database to the point where you can easily just slap another application on top of it. On the other hand a complex application that is tightly tied to the database should be just as secure as the database and changing logic at the DB level is just as likely to break the application as applying any changes directly in the application.
Perhaps that's true, but what if the new application is spelled 'SSMS'? 🙂
If you tie business rules to the application then any user with SSMS can execute any CRUD operation they have permissions for. With direct table access they can avoid every single business rule the application would apply. Without audit logging tied to the database (using triggers, for instance) those changes made outside the application are untraceable.
If using SPs for CRUD without direct table access and all auditing built into the application any SSMS changes made by running SPs is likewise untraceable.
In my opinion, anything that touches the database has to be done by the database, checked by the database, and audited by the database. Otherwise you get big gaping holes in both security and data integrity/robustness.
Finally, any application by definition is only loosely coupled to the database in any event. The database may supply persistence to the application, but that should be the only assumption the app makes. When you move ultimate responsibility for accepting or rejecting data from the application to the database you relieve the app of the responsibility. That's not to say the app can't do common-sense validations and the like, nor does it mean those validations don't have to be synced to the database, but it DOES mean loosening of rules to accept new values are still backwardly compatible. By which I mean the application might not let you choose a new value for a combo box but all old values are still available.
Handling the new value is a bit of a puzzle but developers should treat the database as an unreliable resource anyway. At any time communication between the app and the database could fail. Every single message to and from the DB could generate an error.
Of course this is just another example of loose coupling. Developers have been building loose coupling into designs of their applications for decades. Loosely coupling to the DB is just more of the same.
March 20, 2018 at 9:30 am
roger.plowman - Tuesday, March 20, 2018 9:19 AMZZartin - Tuesday, March 20, 2018 8:43 AMroger.plowman - Tuesday, March 20, 2018 7:29 AMmsbasssinger - Tuesday, March 20, 2018 7:09 AMJeff Moden - Monday, March 19, 2018 1:42 PMmsbasssinger - Monday, March 19, 2018 11:02 AMI have been an application developer since the late 1970s. I have also been doing my own DB work since the late 1980s. I agree 100% that stored procedures (SPs) are the right way to access data. If a case can be made for a specific use for in-line SQL, then fine, but I see that as a case-by-case decision, not suitable for generic guidelines.I also prefer my SPs to do little more than CRUD operations. I stay away from triggers as much as possible. Business logic belongs in the application (IMHO), not in the database, unless a solid case in the context or performance, scalability, and maintainability can be made for a specific use case. That also makes the DB more scalable and improves performance.
So, curious, when do you actually use triggers for your stuff? Also, do you agree that not allowing an "unknown" to occur in the database is better done by a "NOT NULL" constraint on a table or do you enforce that business logic only in the front end?
I do agree that doing business logic in the right environment will definitely add to performance, scalability, and maintainability but I'd never say that business logic belongs (only) in the application even if there were no notable exceptions.
When I use a trigger, it is to enforce some odd constraint that must be enforced under certain conditions or only at runtime, that is only suited to DB application instead of app application . I use constraints for whether a field can be null, default values, etc. Those are design elements that work well, My goal is to keep DB calls quick, short, and simple. When necessary,, I can control the transaction via the application, but only when necessary. I did not say business logic cannot be in the database, but that when you look at the any given use case for that, it is quite difficult from a value engineering perspective to justify it.
An interesting POV. I have to say I take the completely opposite view. SPs are a wonderful place to put business logic in addition to simple constraints, especially when you need transactions.
I certainly do agree that the server use should be minimized but in many cases keeping the logic inside SPs does exactly that, especially if the logic involves a lot of data from different tables. Security is also better since an SP is a black box with only known inputs and outputs. Having to make several calls to the DB to collect information required to implement complex logic in the app would seem to be counter productive. Sure, each call may involve only one small DB operation, but how many calls does a single business logic rule require? That also requires either A) direct table access or B) a loosening of the principle of least privilege, neither of which is (IMO) a good idea.
Also, there's the convenience factor. When BL is in SPs it's a lot easier to modify it without disturbing the application. Also, putting BL in the DB side means a (different) application can't freely ignore the business rules.
I would say that that is only the case when the application is only loosely tied to the database to the point where you can easily just slap another application on top of it. On the other hand a complex application that is tightly tied to the database should be just as secure as the database and changing logic at the DB level is just as likely to break the application as applying any changes directly in the application.
Perhaps that's true, but what if the new application is spelled 'SSMS'? 🙂
If you tie business rules to the application then any user with SSMS can execute any CRUD operation they have permissions for. With direct table access they can avoid every single business rule the application would apply. Without audit logging tied to the database (using triggers, for instance) those changes made outside the application are untraceable.
If using SPs for CRUD without direct table access and all auditing built into the application any SSMS changes made by running SPs is likewise untraceable.
In my opinion, anything that touches the database has to be done by the database, checked by the database, and audited by the database. Otherwise you get big gaping holes in both security and data integrity/robustness.
Finally, any application by definition is only loosely coupled to the database in any event. The database may supply persistence to the application, but that should be the only assumption the app makes. When you move ultimate responsibility for accepting or rejecting data from the application to the database you relieve the app of the responsibility. That's not to say the app can't do common-sense validations and the like, nor does it mean those validations don't have to be synced to the database, but it DOES mean loosening of rules to accept new values are still backwardly compatible. By which I mean the application might not let you choose a new value for a combo box but all old values are still available.
Handling the new value is a bit of a puzzle but developers should treat the database as an unreliable resource anyway. At any time communication between the app and the database could fail. Every single message to and from the DB could generate an error.
Of course this is just another example of loose coupling. Developers have been building loose coupling into designs of their applications for decades. Loosely coupling to the DB is just more of the same.
Even with SSMS, security can easily be controlled to prevent such operations. Besides, in your scenario, triggers and constraints can be dropped to do anything the malicious or misinformed SSMS user wants to do. Plus scalability and performance go down the tubes when usage increases.
I have seen multiple small SP CRUD operations in multiple calls go faster with less locking conflicts than a single SP. Especially on DBs with lots of concurrent transactions.
Each case has to be evaluated on its own merit.
March 20, 2018 at 9:39 am
msbasssinger - Tuesday, March 20, 2018 9:30 AMroger.plowman - Tuesday, March 20, 2018 9:19 AMZZartin - Tuesday, March 20, 2018 8:43 AMroger.plowman - Tuesday, March 20, 2018 7:29 AMmsbasssinger - Tuesday, March 20, 2018 7:09 AMJeff Moden - Monday, March 19, 2018 1:42 PMmsbasssinger - Monday, March 19, 2018 11:02 AMI have been an application developer since the late 1970s. I have also been doing my own DB work since the late 1980s. I agree 100% that stored procedures (SPs) are the right way to access data. If a case can be made for a specific use for in-line SQL, then fine, but I see that as a case-by-case decision, not suitable for generic guidelines.I also prefer my SPs to do little more than CRUD operations. I stay away from triggers as much as possible. Business logic belongs in the application (IMHO), not in the database, unless a solid case in the context or performance, scalability, and maintainability can be made for a specific use case. That also makes the DB more scalable and improves performance.
So, curious, when do you actually use triggers for your stuff? Also, do you agree that not allowing an "unknown" to occur in the database is better done by a "NOT NULL" constraint on a table or do you enforce that business logic only in the front end?
I do agree that doing business logic in the right environment will definitely add to performance, scalability, and maintainability but I'd never say that business logic belongs (only) in the application even if there were no notable exceptions.
When I use a trigger, it is to enforce some odd constraint that must be enforced under certain conditions or only at runtime, that is only suited to DB application instead of app application . I use constraints for whether a field can be null, default values, etc. Those are design elements that work well, My goal is to keep DB calls quick, short, and simple. When necessary,, I can control the transaction via the application, but only when necessary. I did not say business logic cannot be in the database, but that when you look at the any given use case for that, it is quite difficult from a value engineering perspective to justify it.
An interesting POV. I have to say I take the completely opposite view. SPs are a wonderful place to put business logic in addition to simple constraints, especially when you need transactions.
I certainly do agree that the server use should be minimized but in many cases keeping the logic inside SPs does exactly that, especially if the logic involves a lot of data from different tables. Security is also better since an SP is a black box with only known inputs and outputs. Having to make several calls to the DB to collect information required to implement complex logic in the app would seem to be counter productive. Sure, each call may involve only one small DB operation, but how many calls does a single business logic rule require? That also requires either A) direct table access or B) a loosening of the principle of least privilege, neither of which is (IMO) a good idea.
Also, there's the convenience factor. When BL is in SPs it's a lot easier to modify it without disturbing the application. Also, putting BL in the DB side means a (different) application can't freely ignore the business rules.
I would say that that is only the case when the application is only loosely tied to the database to the point where you can easily just slap another application on top of it. On the other hand a complex application that is tightly tied to the database should be just as secure as the database and changing logic at the DB level is just as likely to break the application as applying any changes directly in the application.
Perhaps that's true, but what if the new application is spelled 'SSMS'? 🙂
If you tie business rules to the application then any user with SSMS can execute any CRUD operation they have permissions for. With direct table access they can avoid every single business rule the application would apply. Without audit logging tied to the database (using triggers, for instance) those changes made outside the application are untraceable.
If using SPs for CRUD without direct table access and all auditing built into the application any SSMS changes made by running SPs is likewise untraceable.
In my opinion, anything that touches the database has to be done by the database, checked by the database, and audited by the database. Otherwise you get big gaping holes in both security and data integrity/robustness.
Finally, any application by definition is only loosely coupled to the database in any event. The database may supply persistence to the application, but that should be the only assumption the app makes. When you move ultimate responsibility for accepting or rejecting data from the application to the database you relieve the app of the responsibility. That's not to say the app can't do common-sense validations and the like, nor does it mean those validations don't have to be synced to the database, but it DOES mean loosening of rules to accept new values are still backwardly compatible. By which I mean the application might not let you choose a new value for a combo box but all old values are still available.
Handling the new value is a bit of a puzzle but developers should treat the database as an unreliable resource anyway. At any time communication between the app and the database could fail. Every single message to and from the DB could generate an error.
Of course this is just another example of loose coupling. Developers have been building loose coupling into designs of their applications for decades. Loosely coupling to the DB is just more of the same.
Even with SSMS, security can easily be controlled to prevent such operations. Besides, in your scenario, triggers and constraints can be dropped to do anything the malicious or misinformed SSMS user wants to do. Plus scalability and performance go down the tubes when usage increases.
I have seen multiple small SP CRUD operations in multiple calls go faster with less locking conflicts than a single SP. Especially on DBs with lots of concurrent transactions.
Each case has to be evaluated on its own merit.
Triggers can only be disabled if the user has DDL permissions (i.e. owner, admin, etc.). Which needs security configuration on the DB side rather than the app side. 🙂
How would you stop a user that had permissions (using the app) from connecting to the database with SSMS instead? As far as I know the DB can't tell which application (app or SSMS) connects to it. About the only way I could think to do that would be have the app impersonate another user for every communication. Of course an SSMS user could do exactly the same thing...
March 20, 2018 at 9:57 am
roger.plowman - Tuesday, March 20, 2018 9:39 AMmsbasssinger - Tuesday, March 20, 2018 9:30 AMroger.plowman - Tuesday, March 20, 2018 9:19 AMZZartin - Tuesday, March 20, 2018 8:43 AMroger.plowman - Tuesday, March 20, 2018 7:29 AMmsbasssinger - Tuesday, March 20, 2018 7:09 AMJeff Moden - Monday, March 19, 2018 1:42 PMmsbasssinger - Monday, March 19, 2018 11:02 AMI have been an application developer since the late 1970s. I have also been doing my own DB work since the late 1980s. I agree 100% that stored procedures (SPs) are the right way to access data. If a case can be made for a specific use for in-line SQL, then fine, but I see that as a case-by-case decision, not suitable for generic guidelines.I also prefer my SPs to do little more than CRUD operations. I stay away from triggers as much as possible. Business logic belongs in the application (IMHO), not in the database, unless a solid case in the context or performance, scalability, and maintainability can be made for a specific use case. That also makes the DB more scalable and improves performance.
So, curious, when do you actually use triggers for your stuff? Also, do you agree that not allowing an "unknown" to occur in the database is better done by a "NOT NULL" constraint on a table or do you enforce that business logic only in the front end?
I do agree that doing business logic in the right environment will definitely add to performance, scalability, and maintainability but I'd never say that business logic belongs (only) in the application even if there were no notable exceptions.
When I use a trigger, it is to enforce some odd constraint that must be enforced under certain conditions or only at runtime, that is only suited to DB application instead of app application . I use constraints for whether a field can be null, default values, etc. Those are design elements that work well, My goal is to keep DB calls quick, short, and simple. When necessary,, I can control the transaction via the application, but only when necessary. I did not say business logic cannot be in the database, but that when you look at the any given use case for that, it is quite difficult from a value engineering perspective to justify it.
An interesting POV. I have to say I take the completely opposite view. SPs are a wonderful place to put business logic in addition to simple constraints, especially when you need transactions.
I certainly do agree that the server use should be minimized but in many cases keeping the logic inside SPs does exactly that, especially if the logic involves a lot of data from different tables. Security is also better since an SP is a black box with only known inputs and outputs. Having to make several calls to the DB to collect information required to implement complex logic in the app would seem to be counter productive. Sure, each call may involve only one small DB operation, but how many calls does a single business logic rule require? That also requires either A) direct table access or B) a loosening of the principle of least privilege, neither of which is (IMO) a good idea.
Also, there's the convenience factor. When BL is in SPs it's a lot easier to modify it without disturbing the application. Also, putting BL in the DB side means a (different) application can't freely ignore the business rules.
I would say that that is only the case when the application is only loosely tied to the database to the point where you can easily just slap another application on top of it. On the other hand a complex application that is tightly tied to the database should be just as secure as the database and changing logic at the DB level is just as likely to break the application as applying any changes directly in the application.
Perhaps that's true, but what if the new application is spelled 'SSMS'? 🙂
If you tie business rules to the application then any user with SSMS can execute any CRUD operation they have permissions for. With direct table access they can avoid every single business rule the application would apply. Without audit logging tied to the database (using triggers, for instance) those changes made outside the application are untraceable.
If using SPs for CRUD without direct table access and all auditing built into the application any SSMS changes made by running SPs is likewise untraceable.
In my opinion, anything that touches the database has to be done by the database, checked by the database, and audited by the database. Otherwise you get big gaping holes in both security and data integrity/robustness.
Finally, any application by definition is only loosely coupled to the database in any event. The database may supply persistence to the application, but that should be the only assumption the app makes. When you move ultimate responsibility for accepting or rejecting data from the application to the database you relieve the app of the responsibility. That's not to say the app can't do common-sense validations and the like, nor does it mean those validations don't have to be synced to the database, but it DOES mean loosening of rules to accept new values are still backwardly compatible. By which I mean the application might not let you choose a new value for a combo box but all old values are still available.
Handling the new value is a bit of a puzzle but developers should treat the database as an unreliable resource anyway. At any time communication between the app and the database could fail. Every single message to and from the DB could generate an error.
Of course this is just another example of loose coupling. Developers have been building loose coupling into designs of their applications for decades. Loosely coupling to the DB is just more of the same.
Even with SSMS, security can easily be controlled to prevent such operations. Besides, in your scenario, triggers and constraints can be dropped to do anything the malicious or misinformed SSMS user wants to do. Plus scalability and performance go down the tubes when usage increases.
I have seen multiple small SP CRUD operations in multiple calls go faster with less locking conflicts than a single SP. Especially on DBs with lots of concurrent transactions.
Each case has to be evaluated on its own merit.
Triggers can only be disabled if the user has DDL permissions (i.e. owner, admin, etc.). Which needs security configuration on the DB side rather than the app side. 🙂
How would you stop a user that had permissions (using the app) from connecting to the database with SSMS instead? As far as I know the DB can't tell which application (app or SSMS) connects to it. About the only way I could think to do that would be have the app impersonate another user for every communication. Of course an SSMS user could do exactly the same thing...
I rarely mix user permissions with app permissions. The apps I design have their own SQL Server account, which is not published outside the developers who work on it.
Further, I can almost always know the application name with a simple TSQL function:
DECLARE @AppName nvarchar(128);
SET @AppName = APP_NAME();
SELECT @@AppName AS AppName
Lock down user access to the DB, and malicious or uninformed usage of SSMS (or other utilities) will rarely ever be the problem. A good software engineer looks at the whole system, not just the DB.
Any DB user with sa-equivalent access can defeat any DB security.
March 20, 2018 at 12:10 pm
msbasssinger - Tuesday, March 20, 2018 9:57 AMroger.plowman - Tuesday, March 20, 2018 9:39 AMmsbasssinger - Tuesday, March 20, 2018 9:30 AMroger.plowman - Tuesday, March 20, 2018 9:19 AMZZartin - Tuesday, March 20, 2018 8:43 AMroger.plowman - Tuesday, March 20, 2018 7:29 AMmsbasssinger - Tuesday, March 20, 2018 7:09 AMJeff Moden - Monday, March 19, 2018 1:42 PMmsbasssinger - Monday, March 19, 2018 11:02 AMI have been an application developer since the late 1970s. I have also been doing my own DB work since the late 1980s. I agree 100% that stored procedures (SPs) are the right way to access data. If a case can be made for a specific use for in-line SQL, then fine, but I see that as a case-by-case decision, not suitable for generic guidelines.I also prefer my SPs to do little more than CRUD operations. I stay away from triggers as much as possible. Business logic belongs in the application (IMHO), not in the database, unless a solid case in the context or performance, scalability, and maintainability can be made for a specific use case. That also makes the DB more scalable and improves performance.
So, curious, when do you actually use triggers for your stuff? Also, do you agree that not allowing an "unknown" to occur in the database is better done by a "NOT NULL" constraint on a table or do you enforce that business logic only in the front end?
I do agree that doing business logic in the right environment will definitely add to performance, scalability, and maintainability but I'd never say that business logic belongs (only) in the application even if there were no notable exceptions.
When I use a trigger, it is to enforce some odd constraint that must be enforced under certain conditions or only at runtime, that is only suited to DB application instead of app application . I use constraints for whether a field can be null, default values, etc. Those are design elements that work well, My goal is to keep DB calls quick, short, and simple. When necessary,, I can control the transaction via the application, but only when necessary. I did not say business logic cannot be in the database, but that when you look at the any given use case for that, it is quite difficult from a value engineering perspective to justify it.
An interesting POV. I have to say I take the completely opposite view. SPs are a wonderful place to put business logic in addition to simple constraints, especially when you need transactions.
I certainly do agree that the server use should be minimized but in many cases keeping the logic inside SPs does exactly that, especially if the logic involves a lot of data from different tables. Security is also better since an SP is a black box with only known inputs and outputs. Having to make several calls to the DB to collect information required to implement complex logic in the app would seem to be counter productive. Sure, each call may involve only one small DB operation, but how many calls does a single business logic rule require? That also requires either A) direct table access or B) a loosening of the principle of least privilege, neither of which is (IMO) a good idea.
Also, there's the convenience factor. When BL is in SPs it's a lot easier to modify it without disturbing the application. Also, putting BL in the DB side means a (different) application can't freely ignore the business rules.
I would say that that is only the case when the application is only loosely tied to the database to the point where you can easily just slap another application on top of it. On the other hand a complex application that is tightly tied to the database should be just as secure as the database and changing logic at the DB level is just as likely to break the application as applying any changes directly in the application.
Perhaps that's true, but what if the new application is spelled 'SSMS'? 🙂
If you tie business rules to the application then any user with SSMS can execute any CRUD operation they have permissions for. With direct table access they can avoid every single business rule the application would apply. Without audit logging tied to the database (using triggers, for instance) those changes made outside the application are untraceable.
If using SPs for CRUD without direct table access and all auditing built into the application any SSMS changes made by running SPs is likewise untraceable.
In my opinion, anything that touches the database has to be done by the database, checked by the database, and audited by the database. Otherwise you get big gaping holes in both security and data integrity/robustness.
Finally, any application by definition is only loosely coupled to the database in any event. The database may supply persistence to the application, but that should be the only assumption the app makes. When you move ultimate responsibility for accepting or rejecting data from the application to the database you relieve the app of the responsibility. That's not to say the app can't do common-sense validations and the like, nor does it mean those validations don't have to be synced to the database, but it DOES mean loosening of rules to accept new values are still backwardly compatible. By which I mean the application might not let you choose a new value for a combo box but all old values are still available.
Handling the new value is a bit of a puzzle but developers should treat the database as an unreliable resource anyway. At any time communication between the app and the database could fail. Every single message to and from the DB could generate an error.
Of course this is just another example of loose coupling. Developers have been building loose coupling into designs of their applications for decades. Loosely coupling to the DB is just more of the same.
Even with SSMS, security can easily be controlled to prevent such operations. Besides, in your scenario, triggers and constraints can be dropped to do anything the malicious or misinformed SSMS user wants to do. Plus scalability and performance go down the tubes when usage increases.
I have seen multiple small SP CRUD operations in multiple calls go faster with less locking conflicts than a single SP. Especially on DBs with lots of concurrent transactions.
Each case has to be evaluated on its own merit.
Triggers can only be disabled if the user has DDL permissions (i.e. owner, admin, etc.). Which needs security configuration on the DB side rather than the app side. 🙂
How would you stop a user that had permissions (using the app) from connecting to the database with SSMS instead? As far as I know the DB can't tell which application (app or SSMS) connects to it. About the only way I could think to do that would be have the app impersonate another user for every communication. Of course an SSMS user could do exactly the same thing...
I rarely mix user permissions with app permissions. The apps I design have their own SQL Server account, which is not published outside the developers who work on it.
Further, I can almost always know the application name with a simple TSQL function:
DECLARE @AppName nvarchar(128);
SET @AppName = APP_NAME();
SELECT @@AppName AS AppNameLock down user access to the DB, and malicious or uninformed usage of SSMS (or other utilities) will rarely ever be the problem. A good software engineer looks at the whole system, not just the DB.
Any DB user with sa-equivalent access can defeat any DB security.
You might want to check this link concerning APP_NAME()
https://docs.microsoft.com/en-us/sql/t-sql/functions/app-name-transact-sql
Specifically, the Important Note:
The application name is provided by the client and is not verified in any way. Do not use APP_NAME as part of a security check.
It's apparently easy to spoof. Which is a pity, I thought you'd shown me a new security trick!
Viewing 15 posts - 46 through 60 (of 67 total)
You must be logged in to reply to this topic. Login to reply