SQL Salt: Implement Database Objects: Design, implement, and troubleshoot security

(This is part of the Study Guide series, 70-464)

Microsoft’s Measured Skill description: This objective may include but is not limited to: grant, deny, revoke; connection issues; execute as; certificates; loginless user; database roles and permissions; contained users; change permission chains

What I see:

· grant, deny, and revoke permissions

· execute as

· certificates

· loginless users

· database roles

· contained users

· ownership chaining (and changing object owner)

GRANT, DENY, and REVOKE Permissions

GRANT, DENY, and REVOKE do their respective action on a securable for a given principal (whether it be a login, user, role, etc.). GRANT gives the permission, DENY prevents the principal from inheriting the permission, and REVOKE removes any GRANT or DENY on that permission for the given principal. There are a large amount of securables that simply can’t be listed here, so see the BOL reference to skim these.

Example

-- *** <DB OBJECT SETUP> ***

use MeasureMySkills

create table dbo.PermTable

(

id int identity(1, 1) not null,

SomeData nvarchar(100) not null

default replicate('a', 100)

)

insert into dbo.PermTable

values(default)

-- *** </DB OBJECT SETUP> ***

create procedure dbo.PermProc

select 1

create login PermLogin

with password = 'password',

check_policy = off

use MeasureMySkills

create user PermUser

for login PermLogin

grant select

on PermTable

to PermUser

-- this will work

execute as user = 'PermUser'

select *

from PermTable

revert

-- this will not work

deny select

on schema::dbo

to PermUser

execute as user = 'PermUser'

select *

from PermTable

revert

revoke select

on schema::dbo

to PermUser

-- this will not work

execute as user = 'PermUser'

exec dbo.PermProc

revert

-- this will work

grant execute

on PermProc

to PermUser

execute as user = 'PermUser'

exec dbo.PermProc

revert

EXECUTE AS

EXECUTE AS is a clause that executes T-SQL in the specified security context. It can also be used for functions, stored procedures, and triggers. In the above example, to test out my security code I use the inline EXECUTE AS.

Example

use MeasureMySkills

-- add PermUser to db_owner role

exec sp_addrolemember 'db_owner', 'PermUser'

create procedure ExecAsDBO

with execute as 'dbo'

select user_name()

create procedure ExecAsCaller

with execute as caller

select user_name()

execute as user = 'PermUser'

exec ExecAsDBO

exec ExecAsCaller

revert

Notes

· options for the EXECUTE AS clause can be CALLER (the caller of the code), SELF (the creator or last alter-er of the module), OWNER (current owner of the module), ‘user_name’, or ‘login_name’ (only for server DDL triggers or logon triggers)

Certificates

Certificates are a database-level securable, as per BOL. We will see later on their extensive use, but for this section we will just cover how to create them and a few nuances.

Example

use MeasureMySkills

create certificate CertTest

encryption by password = 'certpassword'

with

subject = 'Test Certificate',

expiry_date = '2012-6-15'

Notes

· a certificate is either created and encrypted off of a password, or the database master key. If there is no database master key then an encryption password must be specified

· START_DATE is the starting valid date and is optional, and if not supplied will be the current date

· EXPIRY_DATE is how long the certificate will be valid for. If unspecified it will default to a year after the START_DATE

Loginless Users

Loginless users are just that: database users that are created and not tied to an instance login. This can be used as a security measure to impersonate based off of.

Example

use MeasureMySkills

create user LoginlessUser1

without login

Notes

· this user is not mapped to a login, and therefore cannot connect to any other database

Database roles

Database roles are database-level securables that can have a set of permissions and members. They are used to group security structure off of, as opposed to granting/denying specific permissions to database users.

Example

use MeasureMySkills

create role db_testrole

alter role db_testrole

add member PermUser

grant execute

on schema::dbo

to db_testrole

Contained users

Partially contained databases are one of the cool new features of SQL Server 2012. Contained users are simply the users in a contained database. There are two types (as per BOL):

· Contained database user with password – authenticated by the database

· Windows principals – trusted authentication by the database for Windows users of authorized Windows groups

Ownership chaining

Ownership chaining is a very interesting security measure, but can be a bit hard to understand. For an in depth look and a great example, take a look at the BOL reference here. In short, an unbroken ownership chain is when the owner of a calling object is the same; therefore permission checks are not executed. But when the owner of a calling object is different from the called object, a permission check will be performed based off of the calling user. There is also Cross-Database Ownership Chaining, which is an instance-level configuration (disabled by default), that enables or disables ownership chaining between databases. It poses a security hole, which is why it is disabled by default. To change an object’s owner, simply run an ALTER AUTHORIZATION on the object.

Example

use MeasureMySkills

alter authorization

on PermTable

to dbo

select

o.name,

case

when o.principal_id is null then 'SCHEMA OWNER'

else p.name

end as object_owner

from sys.objects o