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
go
create table dbo.PermTable
(
       id int identity(1, 1) not null,
       SomeData nvarchar(100) not null
              default replicate(‘a’, 100)
)
go
insert into dbo.PermTable
values(default)
go
— *** </DB OBJECT SETUP>   ***
create procedure dbo.PermProc
as
       select 1
go
create login PermLogin
with password = ‘password’,
check_policy =off
go
use MeasureMySkills
go
create user PermUser
for login PermLogin
go
grant select
on PermTable
to PermUser
go
— this will work
execute as user = ‘PermUser’
go
select *
from PermTable
revert
go
— this will not work
deny select
on schema::dbo
to PermUser
go
execute as user = ‘PermUser’
go
select *
from PermTable
revert
go
revoke select
on schema::dbo
to PermUser
go
— this will not work
execute as user = ‘PermUser’
go
exec dbo.PermProc
revert
go
— this will work
grant execute
on PermProc
to PermUser
go
execute as user = ‘PermUser’
go
exec dbo.PermProc
revert
go
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
go
— add PermUser to db_owner role
exec sp_addrolemember ‘db_owner’, ‘PermUser’
go
create procedure ExecAsDBO
with execute as ‘dbo’
as
       select user_name()
go
create procedure ExecAsCaller
with execute as caller
as
       select user_name()
go
execute as user = ‘PermUser’
go
exec ExecAsDBO
go
exec ExecAsCaller
go
revert
go
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
go
create certificate CertTest
encryption by password = ‘certpassword’
with
       subject = ‘Test Certificate’,
       expiry_date = ‘2012-6-15’
go
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
go
create user LoginlessUser1
without login
go
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
go
create role db_testrole
go
alter role db_testrole
add member PermUser
go
grant execute
on schema::dbo
to db_testrole
go
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
go
alter authorization
on PermTable
to dbo
go
select
       o.name,
       case
              when o.principal_id is null then ‘SCHEMA OWNER’
              else p.name
       end as object_owner
from sys.objects o
left join sys.database_principals p
on o.principal_id = p.principal_id
where o.name =‘permtable’
alter authorization
on PermTable
to schema owner
go
select
       o.name,
       case
              when o.principal_id is null then ‘SCHEMA OWNER’
              else p.name
       end as object_owner
from sys.objects o
left join sys.database_principals p
on o.principal_id = p.principal_id
where o.name =‘permtable’
References
·         BOL reference on EXECUTE AS
·         BOL reference on CREATE USER
·         BOL reference on CREATE ROLE
·         BOL reference on ALTER ROLE
Version-Controlled Code (GitHub)
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s