Monday, January 12, 2009

Ownership Chaining

Ownership chaining is the classical way of giving users access to objects through stored procedures in SQL Server. And while SQL Server provides two new methods, ownership chaining is what you will use 95% of the time. Certificates and impersonation is something you only have reason to use when ownership chaining does not do the job.

How does ownership chaining work? Say that you have a procedure sp1 owned by user A. sp1 performs a SELECT from tbl1 and tbl2. tbl1 is owned by A, whereas tbl2 is owned B. User C has permission to execute sp1. To be able run this procedure successfully, C needs SELECT permission on tbl2 but not on tbl1. This is because sp1 and tbl1 have the same owner, and this is ownership chaining. Ownership chaining can also be achieved through triggers, user-defined functions and views.

Now, this may seem a little complex to grasp, but in real life it is often a lot simpler. In my experience, having several object owners in a database is not very common. In very many cases, dbo, the database owner, owns all objects in a database. A common way to implement security in a database application is to perform all access through stored procedures that validates input parameters, enforces business rules etc. When dbo owns all procedures and tables, users only need permissions to execute the stored procedures. Thanks to ownership chaining, they do not need any direct permissions on the tables.

The permissions on views also follow the same rules.


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

No comments:

Elevating LLM Deployment with FastAPI and React: A Step-By-Step Guide

  In a   previous exploration , I delved into creating a Retrieval-Augmented-Generation (RAG) demo, utilising Google’s gemma model, Hugging ...