1

In reading @PerformanceDBA's answer to Historical / auditable database he made this statement:

In a real (standard ISO/IEC/ANSI SQL) database, we do not GRANT INSERT/UPDATE/DELETE permission to users. We GRANT SELECT and REFERENCES only (to chosen users) All INSERT/UPDATE/DELETEs are coded in Transactions, which means stored procs. Then we GRANT EXEC on each stored proc to selected users (use ROLES to reduce administration).

Is this true? How does that jive with ORM tools which generate INSERT/UPDATEs dynamically?

UPDATE

OK, so here's an example. I've got a web application with two interfaces, an Admin and a User. The admin side uses heavy ORM capable of generating hundreds if not thousands of distinct SQL commands dynamically.

The user interaction is far simpler, and I've got a dozen or so SPs that handle any UPDATE/INSERTs for a couple of voting buttons. Obviously the users the applications run under have very different permission sets. On the admin side the DB user for the ORM has full CRUD access to relevent tables, there are no SPs used at all for this application--and I wouldn't think of touching the data without going through the business logic in the domain model. Even bulk data imports get processed through the ORM. SPs on the user side I consider a small concession to this principle just because they are such a special case.

Now, I find the statement above in the original question somewhat disturbing, as I'd consider this to be something of a 'real' database, or at least close to it.

Community
  • 1
  • 1
Paul
  • 6,188
  • 1
  • 41
  • 63
  • Are you worried that PerformanceDBA might laugh in the face of your database and application design? Nothing wrong with what you describe here. – K. Bob Dec 28 '11 at 14:46
  • Well I try to ***always*** be worried that a design I'm using might not be best-practice just as a general frame of mind. – Paul Dec 28 '11 at 14:52
  • 1
    And rightly so, but best-practice and someone's view of what is 'real' (whatever that meant in the quote) depend on more than just one thing and you need to take a view of the whole application, not just the database to find best-practice for your application. If your app is using an ORM then SP Only via ORM will not allow you to apply best-practice in your ORM (lazy-loading etc). – K. Bob Dec 28 '11 at 15:15

2 Answers2

1

I would say that this is the best design, and what I typically like to practice. Because ad hoc queries from the application can be messy, hard to tune, and even harder to troubleshoot and trace, it is easiest to have the level of abstraction using stored procedures. The application can only make stored procedure calls. Think of stored procs as the API to the database.

So if the above is the design purpose then the application user need only SELECT and EXECUTE on the database. This is why:

create procedure MyTestProcedure
with execute as 'UserWithDMLRights'
as

    -- your CRUD code

go

If the typical application user only has SELECT and EXECUTE, then the permissions to execute the above stored procedure would be sufficient. The INSERT/UPDATE/DELETE within the stored procedure would be executed in the security contexted of UserWithDMLRights, and that database user would have to have the INSERT/DELETE/UPDATE permissions.

As for the ORMs, I am apt to agree with you. I beleive L2S just makes numerous ad hoc query calls with sp_executesql, so I believe you would run into an issue wtih that theory and using the above security practice.

1

It jives like your grampa at a rave. ORMs can utilize SPs but it doesn't get the best out of them.

SP Only was certainly the way life used to be, it was like the eleventh commandment, but as you point out ORMs don't really work like that. I used to think of the whole SP layer as a sort of prepubescent ORM in itself, you took your relational DB, made a bunch of joins and returned a set of data with the columns/properties needed to populate your objects with.

These days, with dynamic ORM type apps, permissions need to be specified on the table, it's no less secure if your DBA is doing their job, it's just a bit more work and there needs to be more communication regarding what is allowed on tables, if you don't need DELETE then your DBA needs to know not to give permissions for it.

Good DBA's know that a secured DB with table access is just as secure as a DB with SP only access. Convincing less confident DBA's of that is a much harder matter.

K. Bob
  • 2,668
  • 1
  • 18
  • 16
  • I can't completely agree with you. The "SP Only" method is not archaic like you convey, it's just a *different* way than the ORM way of doing it. And allowing only `SELECT` on tables and `EXECUTE` on stored procedures greatly keeps the sandbox as small as possible. –  Dec 28 '11 at 13:33
  • @Shark Sorry - I didn't mean it was archaic, what I meant was prior to ORM becoming mainstream it was the defacto standard and still should be for non-ORM apps. But for ORM-apps the SP only route reduces the benefits of the ORM. – K. Bob Dec 28 '11 at 13:44