SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need A Help in DATA MASKING in SQL SERVER 2008


Need A Help in DATA MASKING in SQL SERVER 2008

Author
Message
Learner44
Learner44
SSC Eights!
SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)

Group: General Forum Members
Points: 816 Visits: 452
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61395 Visits: 17954
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 Modens 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)
Learner44
Learner44
SSC Eights!
SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)

Group: General Forum Members
Points: 816 Visits: 452
perfact...
Learner44
Learner44
SSC Eights!
SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)

Group: General Forum Members
Points: 816 Visits: 452
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61395 Visits: 17954
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 Modens 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)
Learner44
Learner44
SSC Eights!
SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)

Group: General Forum Members
Points: 816 Visits: 452
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61395 Visits: 17954

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 Modens 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)
Learner44
Learner44
SSC Eights!
SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)

Group: General Forum Members
Points: 816 Visits: 452
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211145 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Learner44
Learner44
SSC Eights!
SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)

Group: General Forum Members
Points: 816 Visits: 452
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search