2

I have an encrypted view definition in SQL Server that has some information that I want to protect. The definition looks something like this:

CREATE VIEW myview WITH ENCRYPTION AS
SELECT name, age FROM customer WHERE c_city = 'NY'

Now I don't want to expose the fact that the view has a predicate on c_city with the value 'NY'. But when I run the query and see the execution plan for the following query:

SELECT * FROM myview

I can see the predicate on the underlying table with the constant value as well. So anybody can reverse engineer my view logic this way. How can I prevent this?

0

2 Answers 2

4

Bad news: check out dbForge SQL Decryptor. Anyone can decrypt your encrypted objects.

3
  • LOL Perfect! What is the point of such an encryption feature in that case??
    – Karthik
    Mar 8, 2017 at 19:25
  • It can be used to secure the view structure from mid-level users who may have direct access to the database to issue queries. But that's the extent. It is definitely not to protect any sort of intellectual property. At most, it's a stumbling block. Mar 8, 2017 at 19:30
  • Right, and users just Google for how to decrypt stuff, and they get a trial of SQL Decryptor for free, and get the job done in sixty seconds, max.
    – Brent Ozar
    Mar 8, 2017 at 20:31
3

General Permissions

I guess the answer is to ...
- ...not have all users in the sysadmins SQL Server role.
- ...not have all users in the db_owner database role.
- ...not have all users in the showplan SQL Server role.

Show exection plan

Here a bit of information regarding execution plan visibility:

Users who have SHOWPLAN, ALTER TRACE, or VIEW SERVER STATE permission can view queries that are captured in Showplan output. These queries may contain sensitive information such as passwords. Therefore, we recommend that you only grant these permissions to users who are authorized to view sensitive information, such as members of the db_owner fixed database role, or members of the sysadmin fixed server role. We also recommend that you only save Showplan files or trace files that contain Showplan-related events to a location that uses the NTFS file system, and that you restrict access to users who are authorized to view sensitive information.

Reference: Showplan Security (TechNet)


Deny Permissions

If somebody still has access to the execution plan revoke the permission with

REVOKE VIEW SERVER STATE to [user_name]

...for the SERVER STATE permissions and ...

REVOKE SHOWPLAN to [user_name]

...for the SHOWPLAN permissions and ...

REVOKE ALTER TRACE to [user_name]

...and really test the [user_name] that will be logging in to the SQL Server instance. This would apply a small layer of protection against reverse engineering.

References

GRANT Server Permissions (Transact-SQL)
Syntax for Granting, Denying, and Revoking the SHOWPLAN Permission
Showplan Security (TechNet)
REVOKE Server Permissions (Transact-SQL)

3
  • 1
    Right, but then when someone gets your backup...
    – Brent Ozar
    Mar 19, 2017 at 23:22
  • I agree. But there is always an IF or a THEN. I always thought it best to reduce the possibilities of IFs and THENs down to a tolerable and accountable amount. In the end there is always the HUMAN factor.
    – John K. N.
    Mar 20, 2017 at 10:46
  • This whole question is about the human factor. It's not a matter of if.
    – Brent Ozar
    Mar 20, 2017 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.