845
Chapter 33: Authorizing Securables
33
■ (^) DRI (References): The right to create foreign keys with DRI.
■ Execute: The right to execute stored procedures or user-defi ned functions.
Object permissions are assigned with the SQL DCL commands GRANT, REVOKE, and
DENY. The permissions in SQL Server work like they do in the operating system. SQL
Server aggregates all the permissions a given user might have whether directly assigned
against the user or through the roles. Then SQL Server gives the MAXIMUM of what
has been granted. DENY is an exception. DENY functions as a trump. If anywhere a
DENY has been issued, then just like in Windows, the user is blocked. For instance, if
a user can SELECT against a table directly assigned but a role the user is a member of
has a DENY for SELECT, the user is blocked from issuing a SELECT against the table.
Whether security is managed from Management Studio or from code, you must under-
stand these three commands.
Granting object permission interacts with the server and database roles. Here’s the overall
hierarchy of roles and grants, with 1 overriding 2, and so on:
- The sysadmin server role. (A Windows login that owns a database will be mapped to
dbo; because it maps to dbo, it ignores all security on the database.) - Deny object permission, the db_denydatareader database role, or the
db_denydatawriter database role. - Grant object permission or object ownership, the db_datareader database role, or
the db_datewriter database role.
Best Practice
An easy way to test security is to confi gure the server for Mixed mode and create a SQL Server
Login test user. Using Management Studio, it’s easy to create additional connections as different
users — much easier than it is to change the server registration and log in to Management Studio
as someone else.Since SQL Server 2005, it has been possible to create a database principal that does not map to
a server principal using the CREATE USER command and specifying WITHOUT LOGIN. Then, using
EXECUTE AS USER = '<USERNAME>' to switch security contexts, the security can be tested. REVERT,
of course, switches the context back.If your environment prohibits Mixed-mode security, the easiest way to check security is to
right-click Management Studio or Query Analyzer and use the Run As command to run as
a different user. But this entails creating dummy users in the Windows domain. Generally
speaking, in a “production” Windows domain, most auditors would fl ag dummy users as an
audit point. Because workstations belonging to DBAs tend to belong in production domains,
this recommendation wouldn’t work where the auditors are diligent.c33.indd 845c33.indd 845 7/31/2012 10:01:24 AM7/31/2012 10:01:24 AM
http://www.it-ebooks.info