My Technical Notes

Thursday, 10 March 2016

SQL Server Security Model

Heirarchical Organisation

There are different levels of organisation within SQL Server. A Server contains databases, a database contains schemas and schemas contain tables, stored procedures, functions and so on. We can represent this by talking about the $[Things] that something contains. $[ServerThings] include $[Databases], $[DatabaseThings] include $[Schemas], $[SchemaThings] include $[Tables]. All things are a union of these three sets (levels of organisation) $$Things = ServerThings \cup DatabaseThings \cup SchemaThings$$ If we use $[*] to represent things at a level or below that level we can see: $$SchemaThings^* \subset DatabaseThings^* \subset ServerThings^*$$


Schemas are a $[DatabaseThing]. Given a $[Database\ d], we can take its schemas. Schemas include the familiar $[dbo]:

$$schemas(d) = \{ dbo, \ldots \}$$

We let $[Schemas] denote this set for the database under consideration:

$$Schemas \eqdef schemas(d)$$


Objects are contained within a schema, therefore they are $[SchemaThings]. Objects include things like tables, views, stored prodedures and so on. Given a $[Schema\ s] We can take its objects: $$objects(s) = tables(s) \cup views(s) \cup storedprocs(s) \cup \ldots$$

We use $[Objects] when the $[Schema\ s] is understood:

$$Objects \eqdef objects(s)$$

Given an $[o \in Objects], we can take it's schema:

$$schema(o) = s$$

Given an $[o \in objects(s)] we can say that $[o] is in $[s]'s scope.


Logins are $[ServerThings]. On installation, an $[sa] login will be created (System Administrator). Apparently, their correct name is Server Principles. Given a $[Server\ S], we can take its logins:

$$logins(S) = \{ sa, \ldots \}$$

We let $[Logins] denote this set when the server $[S] is understood:

$$Logins \eqdef logins(S)$$


Users are $[DatabaseThings]. However, a $[User\ u] is only valid if they exist in $[Logins].

$$validUser(u) \iff u \in Logins$$

In order to create a user for a database, we have to first create a login and then create the user.

In the case where we restore a database but a $[User\ u] does not exist in $[Logins] then $[\neg validUser(u)] holds. Such a user is called an orphaned user.

TODO: Complete this.


No comments: