How many DBAs have you dealt with that only give you SELECT and EXECUTE? That seems somewhat crazy. It doesn't cut down the surface area for bugs at all (the same amount of SQL is still required and it'd be the same code as the app was going to execute anyway as a normal query). What scenarios are they worried about here?
I have seen many actually.
Imagine having a huge amount of applications all using the same database, which is, like I said, often enough the main integration point in a company. The applications you are maintaining are written in, let’s say Java, C#, Python and C++. They are too important, too large and a rewrite is way too risky.
How would you start to get this under your control? By moving common logic in a shared C++ DLL probably? Rewrite all those applications at once, in a heroic effort? I for one take the pragmatic approach and extract common business logic shared between all them into a Stored Procedure.
The Stored Procedures are invoked by all applications, so instead of maintaining 10 implementations, I maintain 1. Suddenly you have a common data access path! The 1 implementation will be drowned in a shitload integration tests, because this is what really matters.
Of course, a Stored Procedure doesn’t prevent you from shooting yourself in the foot! But in my experience, it’s way harder to shoot yourself in the foot using a Stored Procedure, that you can inspect the Query Planner for… than hunting down Monster-Queries for weeks, generated by a LINQ provider.
As for the INSERT, UPDATE and DELETE: With SPs, you’ll have fine-grained control, which columns are updated instead of needing to expose all properties in your application code and praying for an ORM to not accidentally update “columns I don’t own”, because of some convention applied by the ORM.
If you are in the lucky position of owning the database and don’t have to maintain legacy applications, I see resistance to Stored Procedures. Then go ahead and build an API all applications are going to use.
But if you don’t own the database and need to maintain dozens of mission-critical applications, Stored Procedures are a tool worth knowing.
Same here, usually no direct access to tables, read access only via views, write access only via stored procedures, to ensure data is always validated in the same way. The stored procedures are basically just database deployed APIs, don’t see the big problem with this, forces you to consider your data as schema as a data service.
Amending data obviously. Building a dashboard using payroll data and having the power to change it are two wildly different things, and no sane large corporation would allow a dashboard's user account to change that data .
You're coming at this from the idea that one user has full access to everything when that is likely the biggest security lapse you'll find in modern apps that use databases: a Web app that has full ddl and even dml access when it shouldn't have.
Yeah, that was a very confused question from me. I thought for a moment EXECUTE permissions were a roundabout way of granting restricted write access. Let me try again: how is "How am I going to work here without Stored Procedures?" supposed to be interpreted in that comment? The application could just run the queries without using stored procedures. Stored procedures don't change the permission requirements; they're a moderate performance optimisation. They're almost always optional.
bytefish's comment seems to be linking stored procedures and writing to columns, which doesn't make sense because the scenario doesn't have any write permissions.
Stored procedures can execute with different permissions to the caller, like a setuid binary. That's a common way to use them.
We only give select and execute permissions. This has saved our asses a lot of times. Do not underestimate the damage a development team with a high turnover can do.