70-433 - With Encryption Question

  • 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]

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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]

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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply