We are in the process of doing a database redesign. Our system architect is planning to a bit masked integer field vs. lookup tables. I personally have never used bit masking at the db level. I was wondering if this is a good design choice.
I should also note that our application will be db agnostic so it will need to work on SQL, Oracle, MySQL, etc.
Hello,
how can I mask an char such as: ' or " in an sql-statement.
Thanks a lot, Chris
I have a column called study in a table which has the number of studies such as 10000 or 9934. I wish to display the result 10,000 or 9,934. I wish to put a comma in the numbers. How can I achieve this using a select statement.
View 5 Replies View RelatedI one of the requirements, i have to mask the data of a column based on a security parameter. how do we do that in Ad Hoc reporting?
View 3 Replies View Relatedhi
is there any option by which i can mask my parameter for password.
Is there a way to mask the contents of a cell or column? I have a table that stores passwords and I would like to mask the password much like Access does with asterisks. I know I can restrict the column based on user but that creates other problems on the frontend.
View 1 Replies View RelatedOur application running on SQL server 2008 since its storing confidential data we would like to mask the sensitive data in non prod environments.Currently I would like to mask data for one table and their 4 columns data. Basically I would like to know how to do this and how it’s being consumed by downstream systems?Are downstream needs to undo the mask data on their end?
View 5 Replies View RelatedAnyone who knows a smarter way to select a special part of a text string.
A have done like this now but mabye the textstring changes. I know that the GO02 in the future will expand.
KnowgoodP_GO02_AA_K_20070807_2010_L.BBB
But the underscore _ sign is like a seperator and will always be there to separate the words.
I need the 20070807_2010 and fomat to 2007-08-07 20:10
SELECT @state = STATE,
@thetime = CONVERT(char(20),STATE_TIME,20),
@id = ID,
@textstrr = TEXTSTRRWHERE SUBSTRING(FILENAME, 21,4)+ '-'+ SUBSTRING(FILENAME, 25,2)+'-'+SUBSTRING(FILENAME, 27,2)+' '+ SUBSTRING(FILENAME, 30,2)+ ':'+ SUBSTRING(FILENAME, 32,2)+':00' between @starttime and @theEndTime
I need to give the below script which contains CREATE CREDENTIAL query to an app team.
CREATE CREDENTIAL crdntl WITH IDENTITY = '<service_acct>',
SECRET = '<pwd>'
GO
My concern is i don't want the password to be visible. Basically i want to use this credential to create a proxy which is then used to run SQL Agent backupjob on number of SQL servers. Also, i cannot leave the SECRET value as blank (as the MSDN suggests.)
Is there any way to mask the password OR any other alternative solution.
I have a table of id numbers that I wish to mask. My thought was to create a new column for this new id number and populate it with a unique sequential value - start at 1 and go as high as needed. My problem is that I cannot recall how to populate that column with a number...
View 4 Replies View RelatedI have a dataset which contains sensitive person information that can be sliced by various demographic groups.
When I use PowerPivot to query it if I use more than say 5 groups the resulting value within a cell may fall below an acceptable value of say 10 and render the group of people identifiable.
What I would like is some way to generate the lowest group such that any combination < 10 returns 10 and any hierarchy/group above the detail uses this new value for its summary.
Here is an example of how it would look now (I have used only 3 groups Gender followed by Age group and Age to keep it simple):
Gender Age group Age Count
Male 32
20-25 30
21 9
24 10
25 11
26-30 2
30 2
This is how I would like it to look:
Gender Age group Age Count
Male <41
20-25 <31
21 <10
24 10
25 11
26-30 <10
30 <10
I have actually tried to do it myself but the DAX expression is beyond me. I use ISFILTERED(<column name>) or HASONEVALUE to get the lowest level group (Age) to work but of course I can't get the levels above it in the hierarchy to calculate based upon the interim result...
IF(HASONEVALUE(Measure[Age]),if([Count]>5,[Count],if(ISBLANK([Count]),0,5)),BLANK())
I have designed a form and created two parameter fields to accept username and password from the user. Based on the values entered above, I am validating inside a stored procedure. I am unable to mask the text the user enters in the password field. I could not locate any mask property. Anybody can help me?
View 1 Replies View RelatedIs there an elegant way to fill the empty space leading a textbox in a table with periods, similar to a table of contents effect?
When I do not allow the textbox to grow and just append a long string of periods it looks fine in my report preview, but after I deploy everything appended after the primary field in that text box is missing?
My report has a lot of data to the right but it is collapsible so the fields to the left are a good distance away. This is why I am trying to include light visual aids that assist lining up data values. I am not stuck on the leading period idea but it seems the least cluttered.
Aaron
I find that dynamic masking does not work on joining tables in SQL Server 2016 CTP2.1.
For examples, I create the following table:
CREATE TABLE [dbo].[HRM_StaffAppointment](
[StaffID] [nvarchar](11) NOT NULL,
[ApptSeqNo] [smallint] NOT NULL,
[ReportingDept] [nvarchar](10) NULL,
[Code] ...
Then I apply mask on StaffID and RankDesc.
alter [dbo].[HRM_StaffAppointment] alter column [StaffID] add masked with (function='default()')
alter [dbo].[HRM_StaffAppointment] alter column [RankDesc]
add masked with (function='default()')
When User A logged in and query on HRM_StaffAppointment, StaffID and RankDesc are perfectly masked. But User A can remove the masking using another table:
CREATE TABLE [dbo].[staffID](
[staffID] [nvarchar](255)
) ON [PRIMARY]
select a.*
from dbo.HRM_StaffAppointment as a
left join dbo.staffID as b
on a.StaffID = b.StaffID
It looks like a security hole to me, or I'm doing anything wrong?
when prod db is restored in test server, i have to hide/replace sensitive fields (name, phone etc)Is it doable in SQL 2008 and SQL 2012?
View 5 Replies View RelatedI have a business requirement to build a tabular data model, where I need to mask information of other Agents from a given Agent but I still need to show the overall sales of the given product.
For eg: IF an Agent is in APAC region he should see APAC region sales and also should be able see the sales of the same product in other region without knowing region specific break down.
For Agent "Tom" in APAC region, the numbers will look like this
APAC_Sales = 100,000
Other_Sales = 500,000
And if "John" is in NA region, then the number will look like this for him
NA_Sales = 200,000
Other_Sales = 400,000
I wanted to create "Roles" based on the Region, so all the agents belong to "APAC" region will have same view as Tom and "NA" region agents will have John's view.
I am trying to implement data masking based on user login and not sure why this is not working. I have the dimensions DimBrand, DimProduct and DimUser. I should mask the BrandCode with 'XXXX' nothing but in the report all the BrandCode should appear but few of the code will be masked if the user is not belongs to that group. I have a fact table FactProduct in this. In the cube I created all these 3 dimensions and the fact table. I created a new dimension DimBrandMask and I separated the code over there with a relationship with the actual DimBrand dimension. In the cube a reference relationship is set up with the measure group. Created a role with read access.
In the dimension data tab of role I put the below MDX to allowed set.
NonEmpty([DimBrandMask].[Brand Code].Members, (StrToMember("[DimUser].[Login Name].[Login Name].[" + UserName() + "]") ,[Measures].[Dim User Count]))
And in Denied Member set i put the below MDX
IIF( (StrToMember("[DimUser].[Login Name].[Login Name].[" + UserName() + "]"), [DimUser].[Access Right].&[False]), NONEMPTY( [DimBrandMask].[Brand Code].Members,(StrToMember("[DimUser].[Login Name].[Login Name].["
+ UserName() + "]"), [DimUser].[Access Right].&[False], [Measures].[Dim User Count])),{})
Note I created one measure group from the DimUser table and the measure [Dim User Count] is used in the above query.
I am expecting some result like below
Brand BrandCode Count
Brand1 b1 6
Brand2 XXXXX 5
Brand3 XXXXX 10