June 13, 2011 at 10:08 am
I'm studying for the 70-433, and I've run across an option that I've never used before: With Encryption. I've been able to figure out how to make it work (see below), but I'm left with the why to use it question. What does this option actually do? When would it be a good idea to put it into a system?
What I've read on it so far says that if encrypting script is needed in a system where some users shouldn't see the script, then those users should have their permissions adjusted at the user level. I've tested this in basic queries (no stored procs yet), but I still don't see it impacting anything I'm running.
If you can explain this further I would GREATLY appreciate it.
And to show that I've worked on this myself already (see the Create View step towards the bottom):
If Object_ID('dbo.Authors') Is Not Null
Drop Table dbo.Authors
Go
----------
If Object_ID('dbo.BookTitle') Is Not Null
Drop Table dbo.BookTitle
Go
--------------------
Create Table dbo.Authors
(Authors Varchar(100),
ID_Field Int
Identity(1,1)
Primary Key)
Go
----------
Create Table dbo.BookTitle
(BookTitle Varchar(100),
ID_Field Int
Identity(1,1)
Primary Key,
Auth_ID_Field Int)
Go
------------------------------
Insert Into dbo.Authors
(Authors)
Select 'John Smith'
Union All
Select 'Jane Smith'
Union All
Select 'Stephen King'
Union All
Select 'Robert Jordan'
Union All
Select 'Russ Martin'
Union All
Select 'Grace Smith'
Union All
Select 'Bill Gates'
Union All
Select 'Adam Smith'
Union All
Select 'Jane Hurt'
Union All
Select 'William Shakespeare'
Go
----------
Insert Into dbo.BookTitle
(BookTitle,
Auth_ID_Field)
Select 'Romeo and Juliet',10
Union All
Select 'The Stand',3
Union All
Select 'Crown of Swords',4
Union All
Select 'Why I Love Microsoft',7
Union All
Select 'The Plain Jane',2
Union All
Select 'The Plain John',1
Union All
Select 'The Plain John 2',1
Union All
Select 'Eye of the World',4
Union All
Select 'The Great Hunt',4
Union All
Select 'White Trash Parties 101',5
Union All
Select 'The Dragon Returns',4
Union All
Select 'The Wealth of Natitions',8
Union All
Select 'IT',3
Union All
Select 'Path of Daggers',4
Go
------------------------------
--Select *
--From dbo.Authors
------------
--Select *
--From dbo.BookTitle
--Go
----------------------
--Select BT.BookTitle,
-- A.Authors
--From dbo.Authors A Inner Join dbo.BookTitle BT
-- On BT.Auth_ID_Field=A.ID_Field
--Order By A.Authors,
-- BT.BookTitle
--Go
----------------------
--Select BT.BookTitle,
-- A.Authors
--From dbo.Authors A Left Join dbo.BookTitle BT
-- On BT.Auth_ID_Field=A.ID_Field
--Order By A.Authors,
-- BT.BookTitle
--Go
------------------------------
If Object_ID('dbo.TestView') Is Not Null
Drop View dbo.TestView
Go
--------------------
Create View dbo.TestView
With Encryption
As
Select BT.BookTitle,
A.Authors
From dbo.Authors A Left Join dbo.BookTitle BT
On BT.Auth_ID_Field=A.ID_Field
Go
--------------------
Select *
From dbo.TestView
Go
------------------------------
Drop Table dbo.Authors
Go
----------
Drop Table dbo.BookTitle
Go
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
June 13, 2011 at 10:12 am
It obfuscates the storage of the definition in the system tables (it is not encryption, despite the name). It's pretty easy to undo, so it's not going to stop someone determined to look at the definitions of your procedures, just slow them down a bit.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2011 at 10:23 am
GilaMonster (6/13/2011)
It obfuscates the storage of the definition in the system tables
I'm not sure I follow you completely. Are you saying that other users would not be able to see the list of field names, data types, etc. for a view used in With Encryption? Would I still be able to see this information (I ask because I just tried it and I could)?
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
June 13, 2011 at 10:41 am
If I create this view:
CREATE VIEW mytest WITH ENCRYPTION
AS
SELECT e.EmployeeID ,
e.Gender ,
e.HireDate ,
e.CurrentFlag ,
e.LoginID ,
e.MaritalStatus ,
e.ModifiedDate
FROM HumanResources.Employee e
WHERE e.CurrentFlag = 1
I can run
select * from MyTest
and get the data. I see the columns. However I can't script this in SSMS anymore as a new view. The code is encrypted, or more accurately as Gail mentioned, obfuscated.
If I look in Syscomments, I see the "text" is null. If I create the view without encryption, I get the text of the view.
It's not as useful for views as it is for procs/functions, but in all cases, it's not really encrypted.
June 13, 2011 at 11:19 am
jarid.lawson (6/13/2011)
I'm not sure I follow you completely. Are you saying that other users would not be able to see the list of field names, data types, etc. for a view used in With Encryption?
They'd be able to see that, they would not be able to get the view's definition (the select that the view is based on). It'll be null in the catalog views (sys.sql_modules) and if you tried to Script As -> Create or Alter from SSMS it would not be able to.
Would I still be able to see this information (I ask because I just tried it and I could)?
The WITH ENCRYPTION does not track who ran it. Once it's done, no one can see the view's definition. As I said, it's easy to reverse though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply