locked
Protecting proprietary schema and sps RRS feed

  • Question

  • I am looking at potentially doing an on site installation of a SQL/.NET based solution for various customers. Once in place, the database could contain private information that the customer is not willing to release or share, requiring the install to be self contained and maintained on site. At the same time, we do not want to give our customers full access to our proprietary database schema and stored procedures code. I know we can encrypt things within the database, but this is easy to get around. What is the best way to protect the sensitive information for both parties in a situation like this? I don't see any way that we can give the customer admin access to the server. I am concerned they will have a problem with this.  
    Wednesday, April 27, 2011 4:43 PM

Answers

  • --Schemas and metadata permissions

    -- Create a dbo table
    CREATE TABLE dbo.t (id int)
    GO
    -- new user
    CREATE LOGIN JoeStrummer WITH password='password'
    CREATE USER Joe FOR LOGIN JoeStrummer
    GRANT CREATE TABLE TO Joe
    GO
    -- this fails, because Joe does not have permissions on schema 'dbo'
    SETUSER 'Joe'
    GO
    CREATE TABLE t1 (id int)
    GO
    -- schema for Joe
    SETUSER
    GO
    CREATE SCHEMA JoeSchema AUTHORIZATION Joe
    GO
    -- this still fails, because Joe does not have permissions on schema 'dbo'
    SETUSER 'Joe'
    GO
    CREATE TABLE t1 (id int)
    GO
    -- this works
    CREATE TABLE JoeSchema.t1 (id int)
    GO
    SETUSER
    GO
    -- hide metadata
    -- by default, Joe can see only his schema
    SETUSER 'Joe'
    SELECT * FROM sys.tables
    GO
    -- allow Joe to see all user tables
    SETUSER
    GRANT VIEW DEFINITION ON schema::dbo TO Joe
    GO
    SETUSER 'Joe'
    SELECT * FROM sys.tables
    GO
    -- revoke Joe to see all user tables
    SETUSER
    REVOKE VIEW DEFINITION ON schema::dbo TO Joe
    GO
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by WeiLin Qiao Monday, May 9, 2011 1:58 AM
    Thursday, April 28, 2011 6:46 AM
  • Hi davenexrf,

     

    Thank you for your post.

     

    Based on your description, I understand that you want to secure two parts of your SQL Server Database: User's private data and the structure of your database objects such as the Stored procedure, if there are any misunderstanding, please do not hesitate to let me know.

     

    It is risky to give the user admin right in SQL Server. For protect the data stored in SQL Server, first we could use the GRANT and DENY commend to control who can read data within SQL Server, we just give the user appropriate permission to access the database. Then consider to use the secret key to encrypt the sensitive data, for more detailed information, please refer the below links.

     

    For the structure of Database Objects, SQL Server allow us encrypt object definitions when we create an object. For instance, to encrypt a stored procedure, use the CREATE PROCEDURE with the ENCRYPTION option, users who have no access to system tables or database files cannot retrieve the obfuscated text.

     

    Encryption 101 (http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/19/637529.aspx)

    How secure is your data?( http://blogs.msdn.com/b/yukondoit/archive/2005/10/14/480854.aspx)

     

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    • Marked as answer by WeiLin Qiao Monday, May 9, 2011 1:58 AM
    Thursday, April 28, 2011 6:54 AM

All replies

  • --Schemas and metadata permissions

    -- Create a dbo table
    CREATE TABLE dbo.t (id int)
    GO
    -- new user
    CREATE LOGIN JoeStrummer WITH password='password'
    CREATE USER Joe FOR LOGIN JoeStrummer
    GRANT CREATE TABLE TO Joe
    GO
    -- this fails, because Joe does not have permissions on schema 'dbo'
    SETUSER 'Joe'
    GO
    CREATE TABLE t1 (id int)
    GO
    -- schema for Joe
    SETUSER
    GO
    CREATE SCHEMA JoeSchema AUTHORIZATION Joe
    GO
    -- this still fails, because Joe does not have permissions on schema 'dbo'
    SETUSER 'Joe'
    GO
    CREATE TABLE t1 (id int)
    GO
    -- this works
    CREATE TABLE JoeSchema.t1 (id int)
    GO
    SETUSER
    GO
    -- hide metadata
    -- by default, Joe can see only his schema
    SETUSER 'Joe'
    SELECT * FROM sys.tables
    GO
    -- allow Joe to see all user tables
    SETUSER
    GRANT VIEW DEFINITION ON schema::dbo TO Joe
    GO
    SETUSER 'Joe'
    SELECT * FROM sys.tables
    GO
    -- revoke Joe to see all user tables
    SETUSER
    REVOKE VIEW DEFINITION ON schema::dbo TO Joe
    GO
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, April 28, 2011 6:46 AM
  • --Schemas and metadata permissions

    -- Create a dbo table
    CREATE TABLE dbo.t (id int)
    GO
    -- new user
    CREATE LOGIN JoeStrummer WITH password='password'
    CREATE USER Joe FOR LOGIN JoeStrummer
    GRANT CREATE TABLE TO Joe
    GO
    -- this fails, because Joe does not have permissions on schema 'dbo'
    SETUSER 'Joe'
    GO
    CREATE TABLE t1 (id int)
    GO
    -- schema for Joe
    SETUSER
    GO
    CREATE SCHEMA JoeSchema AUTHORIZATION Joe
    GO
    -- this still fails, because Joe does not have permissions on schema 'dbo'
    SETUSER 'Joe'
    GO
    CREATE TABLE t1 (id int)
    GO
    -- this works
    CREATE TABLE JoeSchema.t1 (id int)
    GO
    SETUSER
    GO
    -- hide metadata
    -- by default, Joe can see only his schema
    SETUSER 'Joe'
    SELECT * FROM sys.tables
    GO
    -- allow Joe to see all user tables
    SETUSER
    GRANT VIEW DEFINITION ON schema::dbo TO Joe
    GO
    SETUSER 'Joe'
    SELECT * FROM sys.tables
    GO
    -- revoke Joe to see all user tables
    SETUSER
    REVOKE VIEW DEFINITION ON schema::dbo TO Joe
    GO
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by WeiLin Qiao Monday, May 9, 2011 1:58 AM
    Thursday, April 28, 2011 6:46 AM
  • Hi davenexrf,

     

    Thank you for your post.

     

    Based on your description, I understand that you want to secure two parts of your SQL Server Database: User's private data and the structure of your database objects such as the Stored procedure, if there are any misunderstanding, please do not hesitate to let me know.

     

    It is risky to give the user admin right in SQL Server. For protect the data stored in SQL Server, first we could use the GRANT and DENY commend to control who can read data within SQL Server, we just give the user appropriate permission to access the database. Then consider to use the secret key to encrypt the sensitive data, for more detailed information, please refer the below links.

     

    For the structure of Database Objects, SQL Server allow us encrypt object definitions when we create an object. For instance, to encrypt a stored procedure, use the CREATE PROCEDURE with the ENCRYPTION option, users who have no access to system tables or database files cannot retrieve the obfuscated text.

     

    Encryption 101 (http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/19/637529.aspx)

    How secure is your data?( http://blogs.msdn.com/b/yukondoit/archive/2005/10/14/480854.aspx)

     

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    • Marked as answer by WeiLin Qiao Monday, May 9, 2011 1:58 AM
    Thursday, April 28, 2011 6:54 AM