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.
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