Hiding Schema of Specific Tables

Question

We are launching one of our products as Packaged Product or Commercial Off The Shelf (COTS) software. The software manages business data as well as application specific data into tables of SQL Server database.

When a client will install the project the schema will be copied to his machine's SQL Server, however i was wondering if i can prevent the client from viewing the schema and data of application specific tables.

These tables however should still be available to the application itself.

Answer 1

-- by default, User  can see only his schema

--Execute as specific user
SELECT * FROM sys.tables
GO
-- allow User to see all user tables

GRANT VIEW DEFINITION ON schema::dbo TO Username
GO

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

If a person owns the computer that installs your application, they can start SQL Server in minimum configuration mode and then connect to the instance of SQL Server as a member of the sysadmin fixed server role. That will give them access to the tables, etc. You can make snooping difficult for them by encrypting your views and procedures (and you probably should), but a determined and sophisticated user may be able to figure out what your application is up to. You can also encrypt specific content either inside SQL Server, or your application can encrypt something before the app passes the info to SQL Server. But that's probably only practical for small amounts of information, and it just changes what a malicious user is trying to hack. Your real protection is your software license.

Note that in a high security environment, you have more options. That is, if you are a bank and you have physical control of the computer, you have much more protection. And features like auditing, hardware security modules, and transparent data encryption will be part of your protection strategy.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 3

" can prevent the client from viewing the schema and data of application specific tables."

 

DENY VIEW DEFINITION
SCHEMA :: SchemaName 
TO db_username

VIEW DEFINITION granted at this schema scope allows the grantee to see all metadata for all objects that are contained in the specified schema unless the grantee is denied VIEW DEFINITION or CONTROL permissions for an individual entity in the schema

---

Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!

Answer 4

Quite right. But this only works until they log into SQL Server as a sysadmin. But of course, most users won't know how to do that.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 5

As far as I know, DbDefence (www.dbdefence.com) uses some undocumented tricks to hide objects and tables even from DBA.

Its not supported by Microsoft however.