Mask a column for SSMS
-
I know this is not a new topic but there does not appear to be agood solution for my needs.
The problem comes with having to give read rights to outsourced developers to sensitive data. I know I can do a deny but that would create a problem for those who may be working on a search for that column (SSN). I have tryed this sp_addextendedproperty:
EXEC sys.sp_addextendedproperty @name=N'Input Mask ', @value=N'XXX-XX-' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PERSON1', @level2type=N'COLUMN',@level2name=N'SFEDID'
GO-- mask does not work
EXEC sys.sp_addextendedproperty @name=N'microsoft_database_tools_support', @value=N'<Hide? , sysname, 1>' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PERSON1', @level2type=N'COLUMN',@level2name=N'SFEDID'
GO-- hide column does not work either
As I said the scenario of developers needing to have partial access for coding purposes. Here is the table and inserts sample: Change to your test db if it is not named Test.
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PERSON1](
[HMY] [numeric](18, 0) NOT NULL, ---- this is an ID row assigned by app
[ULASTNAME] [varchar](256) NULL,
[SFIRSTNAME] [varchar](255) NULL,
[SADDR1] [varchar](50) NULL,
[SADDR2] [varchar](50) NULL,
[SCITY] [varchar](40) NULL,
[SSTATE] [varchar](5) NULL,
[SZIPCODE] [varchar](12) NULL,
[SFEDID] [varchar](15) NULL, -- does not have the same name in every table but is a SSN
) ON [PRIMARY]
GO
INSERT INTO [Test].[dbo].[PERSON1]('1629','Ying','Chris','3776 Main Street 227','','Brooklyn','NY' ,'36501' ,'123456789');
INSERT INTO [Test].[dbo].[PERSON1]('2352','Hawk','Don','13836 Alamo Memorial Pkwy 310','','San Antonio','TX' ,'78201' ,'234-56-7890');
INSERT INTO [Test].[dbo].[PERSON1]('9356','Cox','Ed','8803 North 7 Highway','Building 6','Kansas City','MO' ,'64106' ,'345-67-8901');
INSERT INTO [Test].[dbo].[PERSON1]('1981','Ainey','Adam','4836 Alamo Memorial Pkwy 192','','San Antonio','TX' ,'78201' ,'456-78-9012');
INSERT INTO [Test].[dbo].[PERSON1]('6392','Talon','Wilson','6732 Main St','Building 12','Kansas City','MO' ,'64102' ,'567890123');
Another problem you will notice is that the SSN has no format to it "SIGH". My expected results would only affect querying through SSMS or similar toad or what ever(not the Application). It can be against user groups or the column itself.
Results would look like this when a select is made(select only access is all they would have):
select * FROM [Test].[dbo].[PERSON1]
--------results---------
HMY ULASTNAME SFIRSTNAME SADDR1 SADDR2 SCITY SSTATESZIPCODE SFEDID
6392 Talon Wilson 6732 Main St Building 12 Kansas City MO 64102 XXX-XX-0123
Thanks in advance for your input!:-)
:cool:"There are no problems! Only solutions that have yet to be discovered!" -
[Grant said this in another thread]
sp_addextendedproperty is for putting descriptions, etc., on to your SQL server objects. It doesn't do any kind of formatting of any kind. It's for creating meta-data about your database objects. Say, for example, you want to add a property for the build number from source control so that you can always track the version of objects inside your database, or the name of the programmer or dba that created the object. There's a whole set of procedures around accessing, creating & deleting extended properties.
so those commands will not alter or manipulate the data in any way, shape or form.
you could build a custom app that looks for those extended properties,a dn use them, but nothing will force other apps to use it.,
i think the right solution is to createa VIEW of the table, and hav3e the view fudge the columns you don't want displayed...
you could also use more granular permissions and take away select rights of the column itself.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
[Grant said this in another thread]
sp_addextendedproperty is for putting descriptions, etc., on to your SQL server objects. It doesn't do any kind of formatting of any kind. It's for creating meta-data about your database objects. Say, for example, you want to add a property for the build number from source control so that you can always track the version of objects inside your database, or the name of the programmer or dba that created the object. There's a whole set of procedures around accessing, creating & deleting extended properties.
so those commands will not alter or manipulate the data in any way, shape or form.
you could build a custom app that looks for those extended properties,a dn use them, but nothing will force other apps to use it.,
i think the right solution is to createa VIEW of the table, and hav3e the view fudge the columns you don't want displayed...
Lowell
--------------------------------------------------------------------------------
--There is no spoon, and there's no default ORDER BY in sql server either.
I can not create a view it would be outside the scope of the application and would not work.
:cool:"There are no problems! Only solutions that have yet to be discovered!" -
then how about creating a role that revokes the rights to the specific column, and adding everyone except the app login to that to take away th permissions of the column?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I already said I could do (deny or revoke) but it would not allow the developers who are working on the app who have to do selects statements to be able to make sure that it is working proper:
Is there away to make the select statement to only show this XXX-XX-last four digits: detail would be smoething like
user logs in > runs select [SFEDID] from PERSON1 > results would be XXX-XX- last four digits
:cool:"There are no problems! Only solutions that have yet to be discovered!" -
only what i mentioned before...for example, rename [SFEDID] to [SFEDID_ORIG]
and then create a view named [SFEDID] that selects from the table, that masks that specific column.
you could also simply give the developers a copy of the database, and update the table in that database to have either NULL, sor fake SSNS or, update with XXXX if the field is varchar XXXX for the last four digits .
I know we munge/ depersonalize database copies here all teh time for exactly that purpose.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Unfortunately I am under the constrant to give access from management to production(stupid) not a copy and the view thing won't work in this case.
:cool:"There are no problems! Only solutions that have yet to be discovered!" -
bopeavy (6/24/2011)
Unfortunately I am under the constrant to give access from management to production(stupid) not a copy and the view thing won't work in this case.wow....that's kind of what i expected too, but...wow....I hope you are doing backups and transaction log backups, and they work, cause it's only a matter of time before a developer runs an update or delete without a WHERE statement on production....and waits a couple of days before telling you what he did.
for me, that would be the point where i stand up to managmeent and force them to test on a different server, without backing down, , or start shopping my resume around.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Yes I know! I have not bow down to this yet. I am trying to find a better solution. If triggers would use: for select then I could create a trigger but every angle so far is a dead end.
:cool:"There are no problems! Only solutions that have yet to be discovered!" -
Given that what you are trying to protect is SSN shouldn't it be encrypted in the database in the first place? Sensitive information of this nature really should not be stored in plain text. It it is encrypted when it is stored then your problems go away because the management won't be able to read it.
_______________________________________________________________
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/ -
How do you only encrypt part of the data in a column?
:cool:"There are no problems! Only solutions that have yet to be discovered!" -
You can't. But like i said the whole field should be encrypted when it is stored. You should not store plain text SSN for the same reasons you don't store plain text credit card numbers.
_______________________________________________________________
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/ -
I agree the problem is I did not design this system. The company is purchasing it and leaving me to fix these type issue after implementation. The other problem is anything not supplied, creates another issue of anything I implement has to have the ability to recreate itself becuase of updates of this system would wipe out what I have done. 😉
:cool:"There are no problems! Only solutions that have yet to be discovered!" -
Sounds to me like management has mandated that you provide visibility to sensitive information that at the very least in incredibly unethical (which obviously you realize since you are trying to figure out how to hide it). If everything dictates that you have to leave sensitive information in plain text and provide access to the data you in a tough spot. You seem to be doing your best to protect management from themselves but they demand to not be protected. Seems that you have to wash your hands of it and let the results of a bad decision fall on somebody else's shoulders. Voice your concerns, do your best to convince them that it is wrong. Make sure it is noticed that you think this is a bad idea. Then either live with the decision or dust off your resume and find a new gig.
_______________________________________________________________
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/ -
Sounds to me like management has mandated that you provide visibility to sensitive information that at the very least in incredibly unethical (which obviously you realize since you are trying to figure out how to hide it). If everything dictates that you have to leave sensitive information in plain text and provide access to the data you in a tough spot. You seem to be doing your best to protect management from themselves but they demand to not be protected. Seems that you have to wash your hands of it and let the results of a bad decision fall on somebody else's shoulders. Voice your concerns, do your best to convince them that it is wrong. Make sure it is noticed that you think this is a bad idea. Then either live with the decision or dust off your resume and find a new gig.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Sean,
That is well spoken and I am going to nominate you for an Academy Award for bring tears to my eyes!:
This is a nomination for Sean:
"SQLServerCentral.com Best Quoters Award"
:cool:"There are no problems! Only solutions that have yet to be discovered!"
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply