Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Need A Help in DATA MASKING in SQL SERVER 2008 Expand / Collapse
Author
Message
Posted Friday, July 19, 2013 8:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
Hello Friends,

I want to mask certain fields in employee_bank_account_info table, which are very sentive information.

I searched for it , i found verious third-party tools online, which I can not use in my current enviornment.

I need help to find out some way of data masking via sql script or use of any inbuilt function or library in sql server 2008!!!

Please give me any suggestion or example about this.

thanks in advance.
Post #1475523
Posted Friday, July 19, 2013 8:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 13,455, Visits: 12,318
Start here.

http://msdn.microsoft.com/en-us/library/ms179331.aspx


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1475531
Posted Friday, July 19, 2013 8:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
perfact...
Post #1475534
Posted Friday, July 19, 2013 1:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
Hi,

I successfully applied/used symmetric key in order to encrypt the one particular column data.

it also show me that real number - > encrypted numbers -> decryted number(real number).

Now, again I am on the same position as I was , few months back.

My quetion is how to test this case.

Like I encrypted this coulmn data, by createing Master Key-> Certificate-> Symmetric Key -> encrypt the data.

when I want I can simply use the "DecryptByKey" and decrypt it..

BUt, How I can test this thing with other user.

As, they are not allowed to see the decryptedata,because they are normal users.

How I can show this , that normal user can not see the decrpted data.

Please Help.

Thanks.
Post #1475680
Posted Friday, July 19, 2013 2:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 13,455, Visits: 12,318
Well you know have kind of shifted gears. At first you wanted to protect the data. That is a good idea with any sensitive information. It sounds like you have encrypted those columns now. That means that data is protected at rest.

Now however you want to disallow selecting certain columns to certain users. This is a different animal. The article here does a good of explaining how you can implement this with column level permissions.

http://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-using-column-level-permissions/

Another way it to use views. Here is a decent article that explains a way to do that.

http://www.mssqltips.com/sqlservertip/2125/filtering-columns-in-sql-server-using-views/


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1475683
Posted Friday, July 19, 2013 2:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
Hi,

Thanks for those good articles. It solves my half purpose.

As there number of user for couple of tables , in which case we can not assign different roles to everybody, i guess, and also I can not create that many views from all those tables.

I want to upgrade my logic with your valuable help , what is the usefulness of the encryption which I performed on the particular column.?

I mean to say under my login id in SQL SERVER MANAGMENT STUDIO\server23, I created that master key, certificate and encrypted the data.

Now other users who works with me, also have the access to this same server instance, I just want them to see the table with enrcypted data in one column(Which I have encrypted). how I can achive this.?

Thanks in advance for your help.


Post #1475687
Posted Friday, July 19, 2013 2:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 13,455, Visits: 12,318

I want to upgrade my logic with your valuable help , what is the usefulness of the encryption which I performed on the particular column.?


The data is now encrypted at rest. This is very important for security. If somebody gains access or is somehow able to select * from your table, the results are gibberish for those columns.


Now other users who works with me, also have the access to this same server instance, I just want them to see the table with enrcypted data in one column(Which I have encrypted). how I can achive this.?


If you want others to not be able to view the decrypted information the easiest way by is to not give them the key. They can select the column all they want but it is indecipherable without the key. Is that what you are asking?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1475690
Posted Friday, July 19, 2013 3:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
Yes, This wxactly what I want..

But, Here is the thing, The first document which you have provided me, as you know I suceessfully understood it and appield it too.

No I can see two columns like AccountNumber, and EncryptedAccountNumber,

In short, table has now one more column , called EncryptedAccountNumber, which has encrypted sensitive data,

so what I should do with original column(AccountNumber), Which I don't want other member to see it.

As my role is, I can do any DDL and DML operation in this server instant, which is same as my co-worker

and I also told one of my co-worker to check from his account via accessing the same database and the same table, and I found out he can still see both AccountNumber, and EncryptedAccountNumber columns which doesn't solve the purpose.

I am just missing somthing at some point, otherwise I know this thing should not happen.

Thanks again, in advance for your time and support.

Post #1475695
Posted Saturday, July 20, 2013 10:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 37,099, Visits: 31,649
Are you saying that the EncryptedAccountNumber an encrypted version of the visible AccountNumber column?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1475802
Posted Monday, July 22, 2013 7:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
yes...You are right..

In my tabel there is one column with sensitive data, called account number.

On which I successfully applied, coulmn encryption, after createing new coulmn called EncryptedAccountNumber.

Now My table show both original "AccounNumber" and EncryptedAccountNumber" column in select query.

I have to show to my boss that no other user can see that original column..while he/she looks into table.

I successfully applied to that column encryption.

We have our server name is like "SQSERVERSTUDIO\sqlserver2008" , and everybody has their own windows authentication,via which they login and access databases.

I am now stuck , how I can restrict other user with that specific table and specific column, which allows them to see only "EncrytedAccountNumber" column, not the actual "AccountNUmber"

and , here the enviornment is all the user can access same tables with its lates update by any other user, before that login.

Please help me.
Thanks.
Post #1476019
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse