July 22, 2013 at 9:35 am
It's not clear what you're asking, I think somewhat from the language barrier.
So , as you are saying that I should not provide the key to other user, whom I don't want to see that clear text column..How I can do that.?
what key, and regardless, what does the key have to do with the clear text column? It would help if you laid this out in a scenario with a real table, and what you want people to see or not see. Right now I think you don't understand encryption and how it's used, and that makes it hard to discuss.
There is no "have". Users who log into the server can see and access various objects. In the case of encryption, you have another key, or a password protecting that key. Without that password, accessing the key doesn't matter.
As far as a good practice, it's never a good practice to have encrypted data and encrypted data on the same system. It defeats the purpose of using encryption.
The retrofit of encryption into an existing application is complex and requires lots of work. Encryption also should be used sparingly.
July 22, 2013 at 10:07 am
yes you are right..it's all about my language barrier..as english is not my first language..I always find my self in backfoot , while explaning the situation , what I really want
I am trying one more time as follows, what's my current situation is
I have working in SQL SERVER 2008 R2, which has 6 accounts(U01,U02,U03,U04,U05,U06) under one server name called "SQL SERVER MANAGMENT STUDIO\server23".
I am U02 user.
All our accounts have access on specific database and table (client)..
This table consists of columns list as clientID, clientName,clientSalary,clientAccountNumber.
I need to restrict the user U01,U03,U04,U05,U06 to see "clientAccountNumber" column in client table.
I mean, I don't want these users to see the real data inside the ""clientAccountNumber" column, instead of this I want them to see some emcrypted data.
Now can you suggest me the way to do this...Please...
My senior manager, asks me that he doesn;t want somthing like grant and revoke permisiion for all the users..
He needs either encryption or datamasking.
I hope this time I am more clear, and gave less hard time to you "Big Helpers" to understand my situation.
thanks.
July 22, 2013 at 10:18 am
Learner44 (7/22/2013)
yes you are right..it's all about my language barrier..as english is not my first language..I always find my self in backfoot , while explaning the situation , what I really want
No worries about the language barrier. We can work through that easily enough.
I have working in SQL SERVER 2008 R2, which has 6 accounts(U01,U02,U03,U04,U05,U06) under one server name called "SQL SERVER MANAGMENT STUDIO\server23".
I am U02 user.
All our accounts have access on specific database and table (client)..
This table consists of columns list as clientID, clientName,clientSalary,clientAccountNumber.
I need to restrict the user U01,U03,U04,U05,U06 to see "clientAccountNumber" column in client table.
I mean, I don't want these users to see the real data inside the ""clientAccountNumber" column, instead of this I want them to see some emcrypted data.
Now can you suggest me the way to do this...Please...
My senior manager, asks me that he doesn;t want somthing like grant and revoke permisiion for all the users..
He needs either encryption or datamasking.
I hope this time I am more clear, and gave less hard time to you "Big Helpers" to understand my situation.
thanks.
Here is the problem. The type of encryption you have implemented is on the entire column, it has nothing to do with which user happens to be running a select statement currently.
It sounds like your boss doesn't understand encryption either.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2013 at 10:27 am
I would agree that you and your boss don't understand encryption.
someone has to see the account number, who is it? And when? Can you change the code that allows them to query the account number?
If you just are trying to prevent them from seeing the data, you can use grant/revoke. Remove all rights to the table for all users.
Create a view, and grant rights to the view
CREATE VIEW ClientView
as
select ClientID, ClientName, '#$FW$FEDEED' as clientAccountID'
from Client
July 22, 2013 at 10:31 am
Hi Sean ...
can you please suggest me any way to achive the following objective.?
what should I do , in order to restrict other 5 users , having same access as me on the server, so that they can not get an idea about that sensitive information in "clientAccountNumber" column.?
e.g.
Current table looks like
clientName ClientSalary clientAccountnumber
James 30000 123456
Panze 45000 923854
Jibar 40000 325658
I want them to see somthing like,
clientName ClientSalary clientAccountnumber
James 30000 (*mn>>
Panze 45000 (*&gdb
Jibar 40000 *(s^5s
according to my research on google, i found Data Making can be the solution, but I may be wrong too.
Please Help.
July 22, 2013 at 10:47 am
Learner44 (7/22/2013)
Hi Sean ...can you please suggest me any way to achive the following objective.?
what should I do , in order to restrict other 5 users , having same access as me on the server, so that they can not get an idea about that sensitive information in "clientAccountNumber" column.?
This is the problem. If all 6 have the same access they ALL see the same thing. If you want to change access for certain individuals by definition they no longer have the same access. There just isn't any way around this. You MUST do some sort of user level permissions.
If you want to keep your legacy systems working maybe you can create a view to this table that does not include the encrypted data. Then you could rename the original table to something like OriginalTable_Encrypted. Your new view would be the original table name. Then you deny select permission to the table, except for the people you want to view the encrypted data. And you then grant select permission on the new view to the people who can currently select from the existing table.
There really just is no way around this. You are going to have to do some work with permissions in order to do this.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2013 at 10:59 am
Ok "BigHelpers" here is the main point,
I guess finally I came out with proper quetion, Please forgive me for any spelling mistakes...I am trying my best to get command on english language.
so, my issue is ..
there are 4 tables called, 1) ClientInfo, 2) ClientAccount, 3) ClientDimension,4) ClientSecurity.
All of them have one common column called "AccountNumber", which PK in ClientAccount table and FK in all other tables.
There already script written by us, whch performs Joins, Stored Procedures and Cursors and retrive the data across these tables based on matching table1.AccountNumber = table2.AccountNumber.
Now, at moment we are in test enviornment, where we do the testing, but while doing that we can see the actual
"AccountNumber" of the clients.
Now , my task is to convert this "AccountNumber" column's data in all 4 tables into encrypted , masked or somthing other form , so that we don't have to make a change in our Joins, Stored Procedures and Cursors , which we are using right now and we can still run them via matching
table1.AccountNumber = table2.AccountNumber, but this time "AccountNumber" data will be in unidentified form.
and only DBA people , can go back or see with some techniques and make references of this unidentified data in "AccountNumber" to actual "AccountNumber" column data.
any suggestion on this , will be a huge huge help from you to me , as always..
thanks.
July 22, 2013 at 11:30 am
you can't do this. If this data is used in joins, you can't do it. you've designed things poorly.
July 22, 2013 at 11:41 am
Oh..no..:crying:
I am pretty sure that there will not be any way, once you said.
Is there any way we can solve from the scratch, or somthing else., because data is loaded already in all 4 tables and they are like 3500+ records...!!!!!
Because in my team...our "Bigheads" don't want us to see the real accounnumber of clients , while we develop any storedprocedure or cursor or even monthly ETL in our test enviornment.
My condition is like , i have to change somthing at data level to make it unidentified in the table with some algorithm or technique, so that other guys can still run the same scripts as they run from several months, but now they want be able to identified individual clients.
July 22, 2013 at 11:55 am
I mean to say...
Can we add extra column in each table where "AccountNumber" column exists and apply somekind of algorithm, make modification in "AccountNumber" column data and make it unidentified and drop the original column or make that column unavailable for other user..
is it somthing like this possible?
thanks.
July 22, 2013 at 12:02 pm
It's possible, but it's not simple. how do account numbers get populated, and then used in other tables? This entire process has to change if you are going to re-use the "account number" table you currently have for another purpose and move the actual values somewhere else.
Encryption can help, but you really have to think about who can access the information and how, and how you can change your code for the decryption.
Right now, you aren't going to solve this in a forum. It's a major change and an architectural change as well. It also sounds like you don't have enough knowledge to design this. It seems obvious to me what problems you have, but it's a lot of work to explain them and detail them. That's the type of work I would want to be paid to do. If you don't see the issues, then you aren't really ready to tackle this by yourself.
July 22, 2013 at 12:08 pm
So what you really want is a way to randomize the values in your test system? This is an entirely different situation than encrypting data in a column. Of course all you are really going to do here is to change the value of the account number. This would be really simple to do. Just create a quick and easy algorithm to take the existing value and turn it into something different. Then update all of your tables using the same algorithm.
Also, you mentioned twice about developing cursors and you throw that out there like it is something you do frequently. Cursors and other forms of looping in sql server is probably the absolute worst thing you can do for performance. There are times that they are needed but those are generally left to administrative tasks and NOT something that you need in every day sql programming. Next time you are about to create a cursor, instead start a new thread on SSC and we will help you figure out how to perform the same thing a LOT faster.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2013 at 12:18 pm
I completely agree with you Steve...As this is not the easy task.
I am fresh graduate...I know this very hard for me..as new to this orgranisation where , I am working .
Honestly I am not in a condition , to pay for this solution, I know that this task reuqires Experienced Guy.
Can you suggest me a steps for encryption to handle this situation, if it doesn't costs too much to you in terms of time.!!
Side by Side I am trying understand the encryption from this forum, and online, as I have only acedemic knowledge about it.
Thanks for all you help.
July 22, 2013 at 12:28 pm
I am not sure whether or not I am gonna make this solution or not, but as per your explaination,
You want me to do somthing like following.
1) pick column "AccountNUmber" from each table.
2) take one by one character from eache AccountNumber
3) e.g.
if account number is 1234
4) then wirte an algorithm that convert
1 into ^
2 into *
3 into A
4 into #
somthing like this?
5) do this for all the tables (Parent and Child tables) where "AccountNumber" Column is acting as PK or FK.
this what you want me to do or somthing elese?
July 22, 2013 at 12:34 pm
I don't have a simple, easy solution for you. Anything I give you quick is as likely to break things as help if you don't understand.
If you're a new graduate then tell your boss you can't do this. There's nothing wrong with ignorance, and admitting mistakes. You need help. Explain what you've done, but that you need someone there that can see the system to help.
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply