March 20, 2018 at 1:00 pm
roger.plowman - Tuesday, March 20, 2018 12:10 PMmsbasssinger - 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!
It is useful, and good for a part (but not the whole) of security. Most production databases do not have application access from outside a secure network. You can also check IP address (to see if it is in an allowable subnet), manage allowed logons so the app is irrelevant, etc. Security design is system-wide, not just what the DB can do. And no security plan can keep an sa-level user from doing damage, I have used APP_NAME() as one of sev eral parts of an overall security design, and never had a breach. And that includes inviting outsiders to breach a dev database so we could test our security. As one part of an overall scheme, things like SSN, card numbers, etc. I never store in the DB as plain text, I only store them as encrypted values, so even if the DB was breached, or a backup was stolen, the data that sells and can be used for identity theft is useless. It works flawlessly.
September 27, 2022 at 10:13 am
Our developers are not trained in writing stored procedures but they are getting better in taming the ORM. Mostly inline SQL
September 27, 2022 at 6:30 pm
Just yesterday, I had to troubleshoot an issue where CPU was pegged at 99%, and I determined the bottleneck to be a high volume query executed by the eCommerce application whenever a user clicks on a product. The SKU input parameter was typed as NVARCHAR. I advised them to re-type as VARCHAR, and everything was resolved. That's like a classic issue for ORM frameworks.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 28, 2022 at 1:57 am
Just yesterday, I had to troubleshoot an issue where CPU was pegged at 99%, and I determined the bottleneck to be a high volume query executed by the eCommerce application whenever a user clicks on a product. The SKU input parameter was typed as NVARCHAR. I advised them to re-type as VARCHAR, and everything was resolved. That's like a classic issue for ORM frameworks.
The response I got from developers at a previous employer was "We are just comparing strings."
Trying to explain the difference between varchar and nvarchar seemed foreign to them.
September 28, 2022 at 3:20 pm
Eric M Russell wrote:Just yesterday, I had to troubleshoot an issue where CPU was pegged at 99%, and I determined the bottleneck to be a high volume query executed by the eCommerce application whenever a user clicks on a product. The SKU input parameter was typed as NVARCHAR. I advised them to re-type as VARCHAR, and everything was resolved. That's like a classic issue for ORM frameworks.
The response I got from developers at a previous employer was "We are just comparing strings."
Trying to explain the difference between varchar and nvarchar seemed foreign to them.
In all fairness that's because it is foreign to them. The difference between nvarchar and varchar is pretty low level and most developers never need to think about the difference between different code pages *especially english developers who genuinely might have never even needed to care about anything beyond ascii.
ORM's tend to do a very poor job in handling it or even making it apparent to developers that there is a difference.
September 28, 2022 at 4:13 pm
I am shocked by the number of developers who are resistant to learning SQL. It if was a musical instrument it would be a piano. Everyone can play chopsticks, many can manage The Beatles but only those who combine hard work with a rare gift can play Rachmaninov's No 2.
Rather than put SQL inline within the app the SQL that lives with the app can be put in files within the developer's solution. Doing so means that linters such as SQLFluff can inspect the file as part of the build pipeline. The app can load and use the files at runtime. It also allows the DBAs to see what the SQL looks like.
Where dynamic SQL is needed in the app then adopting a naming convention for place markers retains the usefulness of linters.
The area where SQL differs most between platforms is in the implementation of stored procs. What works in SQL Server might not in PostGres or MySQL.
That said, because I was a developer for a decade before SQL Server was even a thing, I have never understood why developers shouldn't be writing stored procs. I think there has been a false delineation between the disciplines that has caused more problems than it has solved.
September 28, 2022 at 10:54 pm
I agree, but the problem is most of the developers I have worked with still only look at the database as a data store. Doesn't matter if it is MS SQL Server, Oracle, PostgreSQL, MySQL. They don't want to take the time to understand how the database engines work and how to get the most out of each system. What works in one, may not work in another.
September 29, 2022 at 11:11 am
In my last 11year stint at a major corp I would say the developers were the best I ever worked with, but still their focus was not on the SQL. We had extremely little inline code, but as I recall well over 400 stored procedures related to several front end apps. There were always the locking/blocking errors, and occasional miscalculations but we worked pretty well together. The one thing that never did get fixed was a set of vendor reports a developer had written which all had the same proven SQL calculation error that no one seemed to care about. They never got fixed to the end-of-life of the application.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
Viewing 8 posts - 61 through 67 (of 67 total)
You must be logged in to reply to this topic. Login to reply