March 6, 2020 at 4:23 pm
In today's cloud world vendor lock-in is an issue with most stored procedures being DB platform specific (MSSQL vs PLSQL vs MySQL). Having to deploy an additional server just to support your product's platform is a real expense that can drive customers to your competitor...
I'm not sure I understand your point about "having to deploy an additional server". I don't think this article is about if there should be a database or not, it is if the use of stored procedures is good. There will still be a database, not using stored procedures does not reduce your server footprint.
March 6, 2020 at 4:40 pm
austin.mcguire wrote:In today's cloud world vendor lock-in is an issue with most stored procedures being DB platform specific (MSSQL vs PLSQL vs MySQL). Having to deploy an additional server just to support your product's platform is a real expense that can drive customers to your competitor...
I'm not sure I understand your point about "having to deploy an additional server". I don't think this article is about if there should be a database or not, it is if the use of stored procedures is good. There will still be a database, not using stored procedures does not reduce your server footprint.
I think he means to support the database server, (SQL Server, Oracle, etc.) if your customer doesn't already have the platform.
March 6, 2020 at 4:45 pm
I agree with other people's comments on encapsulation and security. Here are my additional comments on why I believe it's better to use stored procedures than code queries directly within application code:
If business logic changes, or a bug is found, it would be quite simple to find all the access points to the data if it's in stored procedures. If done right, the changes would need to be done in fewer places to. I've seen countless times where queries are hidden inside application code, or in some third party reporting tool, and get missed when changes are made, because people were focused on the 20 other places they needed to change something.
The proper use of inline-table-valued functions, views, and stored procedures, will lead to an overall system with less code, that is easier to maintain.
While at first sight it may seem easier to scale out application servers that don't depend on data state, putting too much data logic within the application layers is more likely to require you to scale out sooner. Some of the abstraction layers people use to access databases also produce poor quality queries that will hurt database performance.
March 6, 2020 at 5:25 pm
Many excellent points being made here. It seems that the main points in favor of embedded SQL / ORM are:
I am rather dismissive of the "don't hide business logic in the DB argument" as a not-well-thought-out argument due to:
That being said, I still prefer using a stored procedure approach for the following reasons:
TRUNCATE TABLE, SET IDENTITY INSERT, etc), or there's a need for a high-level permission such as VIEW SERVER STATE (a common need, yet a far-reaching permission), then you are just making it easier and easier for hackers because the app logs in with enough permissions to do most anything. Sometimes, when folks can't figure out which permission to grant, or what other configuration is needed to get past a permission problem, just assign the login to the "sysadmin" fixed server role. Yet, with stored procedures, the ONLY permission you ever need to grant the app login is execute on the modules (stored procedures, functions, etc) needed. Module Signing allows for any other permission to be granted to the modules, not to any logins (and removes any need to use EXECUTE AS or enable TRUSTWORTHY on the DB)One thing nobody has mentioned yet (as far as I have seen) is the issue of scalability. This falls into the "performance tuning" benefit of stored procs area, but to be fair, is not an issue for all projects. Working on small projects might never expose the need for the ability to do performance tuning (the definition of "small" varies based on hardware, but let's just where no tables ever have more than a 2 million or so rows). On the other hand, I have worked on projects where OLTP tables had a few hundred million rows in them. That needed to be handled via stored procedures.
Someone did mention that ORMs (at least Entity Framework -- EF) allows for using stored procedures. Excellent point. This does allow for a hybrid scenario. I remember the early stages of a project years ago where the dev was pushing really hard for EF and I was very resistant to that. Still, it was just the two of us and he didn't want to wait on me to write procs, etc. So, we compromised and agreed to use EF such that all initial development would rely on EF to dynamically generate the SQL, but any time we identified an area that was inefficient, if an index wouldn't solve the problem, then we would convert that operation into a stored procedure.
Still, the hybrid approach does nothing to help security, and this was long-ago enough where I didn't know nearly as much about security as I do now, but I suppose the same agreement can be made: use EF to auto-gen T-SQL for simple operations, but any elevated permissions need to be handled via modules.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
March 6, 2020 at 5:45 pm
Using embedded SQL / ORMs means that the DB login needs to be given all necessary rights to perform all operations.
How is that different than not using an ORM? Your application needs to be granted appropriate permissions whether or not it's using an ORM doesn't it?
Someone did mention that ORMs (at least Entity Framework -- EF) allows for using stored procedures. Excellent point. This does allow for a hybrid scenario.
Yeah, that was me, but I wasn't talking about a "hybrid" scenario. When I'm using Entity Framework, I do all my access through views and stored procedures, and I don't allow my application access to anything but those views and procs. Nothing "hybrid" about that.
March 6, 2020 at 5:52 pm
I'm a developer and at least in the past a DBA. And I love using stored procedures. However, where I now work there's a prejudice against them. Coincidentally, the topic came up today in our staff meeting. My boss's problem with stored procs is, "Stored procedures are blind to the environment invoking them."
Kindest Regards, Rod Connect with me on LinkedIn.
March 6, 2020 at 6:00 pm
My boss's problem with stored procs is, "Stored procedures are blind to the environment invoking them."
Isn't that true of any method? I thought that was the whole point of encapsulation? In what way is it considered better to run the same code from outside the database?
I've interviewed a number of developers and some will say that they are quite fluent in SQL Server, only to discover that they use linq or some other ORM and SQL Server is relegated to being simply a data storage mechanism. Some have gone so far as to not include any referential constraints, foreign keys, default values, or anything of the nature. It's treated as no more than a dumb data dump.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
March 6, 2020 at 6:17 pm
Solomon Rutzky wrote:Using embedded SQL / ORMs means that the DB login needs to be given all necessary rights to perform all operations.
How is that different than not using an ORM? Your application needs to be granted appropriate permissions whether or not it's using an ORM doesn't it?
Here I was assuming the more common usage of ORMs in terms of letting them dynamically general the SQL (which is why I was saying that as well as non-ORM embedded SQL). Calling stored procedures (like you are doing) from an ORM is no different. The issue isn't ORM vs not-ORM, it's module stored in SQL Server vs SQL submitted from app (whether embedded by the developer or generated by an ORM, whether using prepared statements or not). Submitting a batch of T-SQL to be parsed and executed by SQL Server requires that ALL permissions needed for the operation be granted to the app login (unless using an App Role, but that merely shifts the mess to the App Role itself). If someone either manages to log in as the app login, OR if someone manages to inject their own SQL, then they have all of those same permissions.
On the other hand, when putting the SQL into modules and using Module Signing, the farthest a hacker can get (at least in those two scenarios of attacking via the app login) is just calling the stored procedures that the app login has execute permission to. There are no database roles (db_datareader, db_datawriter, db_owner) being used. there are no SELECT or DML permissions granted to the app login. There is no VIEW SERVER STATE or any instance-level permissions granted to the app login. All the app login can do is:
EXECUTE permission to.That's it. Nothing more. The hacker can use the given data API. And yes, someone can still do damage with even that much access, but they won't be able to do a single thing that is not part of that API.
To illustrate: granting the app login the instance-level permission of VIEW SERVER STATE just so that they can see their own connection info in sys.dm_exec_connections, or see all sessions using sys.dm_exec_sessions, means that there are not restricted from ANY benefit or usage of that permission. BUT, using Module Signing to grant VIEW SERVER STATE to the proc means that only those who can execute that proc can make use of that permission, but not make use of it outside of what the code within the proc is doing with it. Meaning, if the proc is selecting from sys.dm_exec_connections WHERE [session_id] = @@SPID then that is as far as they will ever get with the VIEW SERVER STATE permission, because the login doesn't have that permission, only the code does, and the code only uses it for that one very limited purpose. Hence, highly granular permissions 🙂
Solomon Rutzky wrote:Someone did mention that ORMs (at least Entity Framework -- EF) allows for using stored procedures. Excellent point. This does allow for a hybrid scenario.
Yeah, that was me, but I wasn't talking about a "hybrid" scenario. When I'm using Entity Framework, I do all my access through views and stored procedures, and I don't allow my application access to anything but those views and procs. Nothing "hybrid" about that.
Yes, I understand. I wasn't meaning to imply anything about your particular usage, sorry if it came off that way. I was merely wanting to not take credit for info already provided. I appreciate that someone (you) mentioned a lesser-known capability. And, I was using that ability as a jumping-off point for the specific usage that my coworker and I came up with.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
March 6, 2020 at 6:23 pm
It seems so many vendor applications just demand dbo access to the database, if not SA rights to the instance. I'd expect more for a commercial application but there is a popular commercial application that not only creates all of the SQL dynamically, but it also creates tables and its own objects as configurations are changed in the app.
The developers only deal with the API and are totally ignorant of the TSQL code being passed to the database until we show them the captured SQL from the performance monitoring app.
March 6, 2020 at 6:28 pm
It seems so many vendor applications just demand dbo access to the database, if not SA rights to the instance.
I've dealt with applications like that. The developers are basically lazy and do not want to sort out proper security roles. Not only have I had databases requiring all users to use the SA account, but they wanted full admin rights to the server as well. Guess what didn't happen? :O
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
March 6, 2020 at 6:39 pm
It seems so many vendor applications just demand dbo access to the database, if not SA rights to the instance.
Yes, this is exactly what I am talking about. SQL Server is a large and complex system, and thus even people who have been working with SQL Server for many years don't understand this security stuff. And there is very little info out there on this particular topic. This is why I started consolidating info on this topic into a Module Signing-specific site, and written several posts on the topic (and several more on the way), and even present about it at SQL Saturday (when possible). But, if SQL Server folks (in general) don't really understand how to not log in as dbo / sa / sysadmin, then there isn't much hope for non-SQL Server folks.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
March 6, 2020 at 8:35 pm
"...from a developer point of view..." Why is it "more difficult" to build with stored procedures:
- It makes your entire project dependent on the DBA. Not as satisfying as being the hero dev who does it all.
- If you work with a bad DBA your projects are sunk.
- It means you'll probably be given deprivileged logins (as is it should be) which means you're hierarchically underneath whoever issues the credentials (probably the DBA)
- Using SP's imposes workflow and technical choices on the developer. In order for a C# dev to use a procedure they have to either use ORM (to partially automate) or manually translate and re-code each input/output variable, create a method (1) to call Sql, create a method (2) to evaluate the result from sql and then translate the output into a format usable by the application. It's more work.
- The manual option for running code ADO.NET is a mishmash of alternate methods some of which are not type safe. The whole database/C# connection is not thread safe. So there's specific knowledge about exception handling it's would be good to know. Resilience and error logging are DIY. For Sql to C# error logging there are no standard interfaces afaik.
- The appropriate C# SQL type libraries are relatively new and have little functionality compared to the base CLR types. None of the ORM's including EF use the type libraries appropriately afaik.
- From a C# perspective it's not unit testable because there are persistence constraints. "Non testable" means less manageable and less confidence to migrate. Doesn't easily integrate with "modern" CI/CD pipelines.
- It can be very difficult to debug. It sometimes tricky to know whether a bug is caused by SQL, ADO.NET, or C# or some combination. "Linting" or console sink logging is DIY.
- To be really effective you have to know Sql well
This may certainly be your experience, but this isn't necessary.
March 6, 2020 at 8:45 pm
In today's cloud world vendor lock-in is an issue with most stored procedures being DB platform specific (MSSQL vs PLSQL vs MySQL). Having to deploy an additional server just to support your product's platform is a real expense that can drive customers to your competitor. Using DB abstraction tools like EF make your code more DB platform agnostic, but they typically do not directly support SPs. Like David Poole's Points 6 and 7, you have to know the requirements and if DB Platform Diversity is a requirement, that pretty much rules out SPs.
I've worked in software across platforms. You can easily astract programmability modules across most platforms. Maybe not all, but most. Write once, suck everywhere isn't a good strategy, and too many vendors adopt this.
Use procs, get abstraction, hire the SQL Server, Oracle, PostgreSQL people you need.
March 6, 2020 at 8:48 pm
When working with SP inside a .net project feels like Blackbox development. The Code you write looks like it assumes too much and needs a magic trust to what data an SP will return.
With Linq2sql you get compiler warnings when you cast types wrong or misspell fields etc. That is much faster to develop with Linq2Sql and less error prone.
I'd argue this is something you should be able to deal with. There aren't that many types and you can scaffold this out easily. I would also argue that your speed of development is overwhelmed by the limitations of tight coupling. We use methods to abstract and enable refactoring. Views and procedures are part of this in RDBMS development.
March 6, 2020 at 8:52 pm
I am loving these excellent comments.
I will point out that developers have a very short and predictable list of persistence requirements: load a grid, populate a form, save on click.
If DBAs take care to lay down an architecture that facilitates standard functions, developers will use it.
So I guess the upshot is: If you are cracking the whip on developers to implement standard procs, you are cracking on the wrong team.
I gave all developers a proc that writes procs. Build a table, run the proc and you have insert/update/delete versions of access for your table, with security permissions attached.
Viewing 15 posts - 31 through 45 (of 71 total)
You must be logged in to reply to this topic. Login to reply