Design Views

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

Microsoft’s Measured Skill description: This objective may include but is not limited to: ensure code non regression by keeping consistent signature for procedure, views, and function (interfaces); security implications

What I see:

·         code non-regression

·         security through views

Code Non-Regression

                The basic gist of non-regression is simple:  don’t make your data calls dependent on the underlying table schemas.  In other words, the layer of views/procedures/functions should break the dependence on the tables.  Underlying tables can change without breaking the code that calls them, and this is through the use of the middle data layer (the “API”) that can be altered to work with the underlying data, yet the call will remain the same.

Security Through Views

                Views are a way to implement both column and row security.  A user can be granted permissions on the view, but denied on the base table.  The below example shows how we can restrict a user (or group of users) from accessing specific table rows that we don’t want them to.  This is the heart of view security:

use MeasureMySkills;

go

create table VerySecureTable

(

       id int identity(1, 1) not null,

       SecurityLevel int not null

              default 1, -- default to not very secure

       SecurityString nvarchar(24) not null

              default 'Some Secure String'

);

go

insert into VerySecureTable

(

       SecurityLevel,

       SecurityString

)

values

(

       1,

       default

);

go 10

insert into VerySecureTable

(

       SecurityLevel,

       SecurityString

)

values

(

       2,

       default

);

go 10

insert into VerySecureTable

(

       SecurityLevel,

       SecurityString

)

values

(

       3,

       default

);

go 10

-- implement row-based security through a view

create view NotSecureData

as

       select *

       from VerySecureTable

       where SecurityLevel <= 2;

go

-- test out our role security

create login NotSecureLogin

with

       password = 'password',

       check_policy = off;

go

use MeasureMySkills;

go

create user NotSecureUser

for login NotSecureLogin;

go

grant select

on object::NotSecureData

to NotSecureUser;

go

-- this won't work (no permissions)

execute as user = 'NotSecureUser';

go

select *

from VerySecureTable;

revert;

go

-- this will work (row-based security)

execute as user = 'NotSecureUser';

go

select *

from NotSecureData;

revert;

go

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.