0

I've got a payroll-related schema that I need to secure. I figure if I prevent all users from directly modifying the tables, and create a set of procedures to handle all data modifications, we should be in good shape. I can have the procedures handle all the audit logging, wrap things in transactions as needed, etc.

But to do this, I need to deny direct DML statements to everybody on this particular schema - even the sysadmin server role. Is there any simple way to do this without resorting to INSTEAD OF triggers (which wouldn't be quite as bulletproof)? I don't see any syntax like "DENY INSERT, UPDATE, DELETE ON SCHEMA::schemaname TO ALL", and denying permissions to the "public" role doesn't seem to have the desired effect.

Improve this question

1 Answer 1

Reset to default
2

If all data access is via stored procedures, then permissions on the table are not checked of the same user (dbo probably) owns both procedure and tables. This is called ownership chaining

This also means the no permissions are needed on the tables at all. You don't need to deny or grant anything on the tables because they won't be checked. Lack of GRANT means no rights anyway, so you don't need DENY

Now, this means nothing to folk with elevated privileges: sysadmin, db_owner etc. These will always have rights. You can only use triggers to block these: but they can disable or drop triggers of course.

I assume that your "end users" are not running as db_owner or sysadmin...

Improve this answer
5
  • It's entirely possible there will be web applications connecting with dbo privileges, and you never know what a developer/administrator with sysadmin rights might do to "clean up"/"fix" the data. I might just have to take the trigger route if there's no way to deny those outright. With any luck, anybody that doesn't understand the importance of data integrity in this scenario also won't know how to find/disable the triggers. :)
    – db2
    Dec 23, 2011 at 14:00
  • There shouldn't be a web app connecting with dbo privilege. Anyone with sysadmin rights should not be casually changing data: especially a proper sysadmin. Developers should not have prod rights like that. Sounds like you have a company problem, rather than a code problem
    – gbn
    Dec 23, 2011 at 14:05
  • It's a small department, and not a particularly huge company, so red tape is minimal. I can likely take dbo privileges off this database for the web service account, and tell everybody to suck it up and fix the access rights on the other stuff in it, but revoking sysadmin arbitrarily isn't a realistic option since this is "the database server". Anyway, is it possible to determine the schema/name of the executing procedure from within a DML trigger without a bunch of sys.dm_exec_requests ugliness? Somehow I doubt it, but you never know.
    – db2
    Dec 23, 2011 at 14:13
  • You can't find out the schema+name of a calling proc in a trigger cleanly. You'd have to use the DMVs
    – gbn
    Dec 23, 2011 at 14:18
  • Didn't think so. I'll probably have to settle for tightening up permissions to the web application, then make a DML trigger to silently email notifications/pink slips to the appropriate parties if the login is a member of sysadmin.
    – db2
    Dec 23, 2011 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.