locked
How to encrypt Sql server table name and its column name & data inside for security pupose RRS feed

  • Question

  • when we open any table then we can see table column name & data. after seeing column name & data we can understand the use & purpose of table. if i do not want to give any chance to other to assume what is the pupose of table then i need to encrypt Sql server table name and its column name & data. i can do it manually but the problem is when i like to issue any select statement then i need to give encrypted name of table & column and that may be very problemetic.

    so just guide me how can i secure my table/column name & data too. thanks

    • Moved by Kalman Toth Monday, October 21, 2013 3:04 PM not db design
    Saturday, October 19, 2013 8:57 PM

Answers

  • u said " I have seen many products with databases that have tables are named like T0005, T0006,.... and columns like COL001, COL002"

    can u tell me how could i give dynamic name to column & table instead of encryption.

    manually i can do it like that i could write a name like TrpSlrmsh and save in excel file

    like

    DynaName      Actual Name

    -------------      ---------------------

    TrpSlrmsh      Person

    so the same way i can store all table column name and their dynamic name and at last

    rename all table & column name with dynamic name. the problem start when i will issue a sql select statement.

    i have to see the excel book and construct the sql like this way

    select col1 as "ID", col2 as "Name", col3 as "Salary" from TrpSlrmsh     

    so guide me how could i do this but very easily & programmatically . thanks

    • Marked as answer by Mou_kolkata Thursday, October 24, 2013 2:25 PM
    Monday, October 21, 2013 11:48 AM
  • Yes, maintenance cost goes up with it, not only that, complexity too.

    I have seen one product that holds all meta data in a table in encrypted form. Queries are built using another tool specifically created for generating queries. It hides the IP but, in a way, it adds a complexity for querying without the tool.

    And some companies keep the data dictionary (yes, Excel can be a way) for meta data and they refer it when querying. Cost is high but if the return is good, it is not an issue.

    With what I have seen, query like you have written are never used. Application uses encrypted files that contains info on meta data for reading and writing data. Application sets names when required.

    Whatever the way you use, making object names unreadable increases the cost, at least initial cost if you plan to have a tool like the one I mentioned above. My suggestion is, if the ROI is high, no harm of implementing this.

    Anyone has faced similar before?

    ----------------------------------------

    Dinesh Priyankara
    http://dinesql.blogspot.com/

    • Marked as answer by Mou_kolkata Thursday, October 24, 2013 2:25 PM
    Monday, October 21, 2013 12:22 PM
  • I am moving it to security.

    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    • Marked as answer by Mou_kolkata Thursday, October 24, 2013 2:25 PM
    Monday, October 21, 2013 3:03 PM
  • You could create generic table and column names.  Then create an encrypted table with "tablename", "physicalcol", "displaycolname".

    However as you said, your queries all reference the physical column name and has to be remapped every time you use it.  This is extremely irritiating to everyone using the database. 

    The best thing to do is abandon this entire idea and is encrypt the database, then only authorized users with the encryption key would even be able to access the data.

    http://technet.microsoft.com/en-us/library/bb510663.aspx

    • Marked as answer by Mou_kolkata Thursday, October 24, 2013 2:25 PM
    Monday, October 21, 2013 3:36 PM
  • I agree. This is an unnecessary complexity. Only users with some sort of permission on a table can see the table definition. And they have to in order to receive meaningful data. Additional information can be changed or hidden by accessing the table through a view, or a stored procedure. In fact, typically all access to data should be through a stored procedure, and only information that you choose to reveal will be returned.

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

    • Marked as answer by Mou_kolkata Thursday, October 24, 2013 2:25 PM
    Monday, October 21, 2013 4:54 PM

All replies

  • Hi,

    As per my understanding, there is no facility given with SQL Server for encrypting object names. However it can be achieved by using names encrypted via a different technique, like encrypting and decryption via an application. My suggestion is, not to encrypt object names (table names, columns names), but make then unreadable in terms of its meaning. I have seen many products with databases that have tables are named like T0005, T0006,.... and columns like COL001, COL002... Smart person can figure it out entities by looking at transactions but still it is not straight forward. 

    Content of a table can be encrypted using built-in functions. Again, it is not recommended to encrypt all columns in all table because there is a cost with it. In addition to that, it hinders the performance of queries as all reads need decryption and all writes need encryption.

    This link gives you all details you need for understanding encryption techniques related to SQL Server: http://technet.microsoft.com/en-us/library/bb510663.aspx.

    You can protect your database by tightening the security on it too. If you handle all security from authentication to authorization, and you manage the instance of SQL Server as the admin, implementing security strategies could be another option too.

    ---------------------------------------

    Dinesh Priyankara
    http://dinesql.blogspot.com/

    • Proposed as answer by Harsh Kumar Sunday, October 20, 2013 6:49 AM
    Sunday, October 20, 2013 6:19 AM
  • Hi Mou_Kolkata,

    As suggested by Dinesh there is no way the object names can be encrypted in sql Server, But Data inside table can be encrypted. 

    If you really bother about security about object name, then you can hide them using sql server security from unauthenticated accounts. (only allowed account should have access to view definition of the objects).

    To know more, read here -> http://msdn.microsoft.com/en-us/library/bb669074.aspx


    Regards Harsh

    • Proposed as answer by Fanny Liu Monday, October 21, 2013 8:19 AM
    Sunday, October 20, 2013 6:53 AM
  • u said " I have seen many products with databases that have tables are named like T0005, T0006,.... and columns like COL001, COL002"

    can u tell me how could i give dynamic name to column & table instead of encryption.

    manually i can do it like that i could write a name like TrpSlrmsh and save in excel file

    like

    DynaName      Actual Name

    -------------      ---------------------

    TrpSlrmsh      Person

    so the same way i can store all table column name and their dynamic name and at last

    rename all table & column name with dynamic name. the problem start when i will issue a sql select statement.

    i have to see the excel book and construct the sql like this way

    select col1 as "ID", col2 as "Name", col3 as "Salary" from TrpSlrmsh     

    so guide me how could i do this but very easily & programmatically . thanks

    • Marked as answer by Mou_kolkata Thursday, October 24, 2013 2:25 PM
    Monday, October 21, 2013 11:48 AM
  • Yes, maintenance cost goes up with it, not only that, complexity too.

    I have seen one product that holds all meta data in a table in encrypted form. Queries are built using another tool specifically created for generating queries. It hides the IP but, in a way, it adds a complexity for querying without the tool.

    And some companies keep the data dictionary (yes, Excel can be a way) for meta data and they refer it when querying. Cost is high but if the return is good, it is not an issue.

    With what I have seen, query like you have written are never used. Application uses encrypted files that contains info on meta data for reading and writing data. Application sets names when required.

    Whatever the way you use, making object names unreadable increases the cost, at least initial cost if you plan to have a tool like the one I mentioned above. My suggestion is, if the ROI is high, no harm of implementing this.

    Anyone has faced similar before?

    ----------------------------------------

    Dinesh Priyankara
    http://dinesql.blogspot.com/

    • Marked as answer by Mou_kolkata Thursday, October 24, 2013 2:25 PM
    Monday, October 21, 2013 12:22 PM
  • I am moving it to security.

    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    • Marked as answer by Mou_kolkata Thursday, October 24, 2013 2:25 PM
    Monday, October 21, 2013 3:03 PM
  • You could create generic table and column names.  Then create an encrypted table with "tablename", "physicalcol", "displaycolname".

    However as you said, your queries all reference the physical column name and has to be remapped every time you use it.  This is extremely irritiating to everyone using the database. 

    The best thing to do is abandon this entire idea and is encrypt the database, then only authorized users with the encryption key would even be able to access the data.

    http://technet.microsoft.com/en-us/library/bb510663.aspx

    • Marked as answer by Mou_kolkata Thursday, October 24, 2013 2:25 PM
    Monday, October 21, 2013 3:36 PM
  • I agree. This is an unnecessary complexity. Only users with some sort of permission on a table can see the table definition. And they have to in order to receive meaningful data. Additional information can be changed or hidden by accessing the table through a view, or a stored procedure. In fact, typically all access to data should be through a stored procedure, and only information that you choose to reveal will be returned.

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

    • Marked as answer by Mou_kolkata Thursday, October 24, 2013 2:25 PM
    Monday, October 21, 2013 4:54 PM