Analysis :: DAX - Dynamic Masking Of Information Based On Role Security
Oct 2, 2015
I 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.
View 2 Replies
ADVERTISEMENT
Sep 28, 2015
I have an issue related to SSAS security. We have an SSAS multidimensional cube which needs 3 types of security:
- Access to the entire cube => OK, based upon a role
- Restricted access to one department (= dimension) => OK, based upon a role
- Access to the entire cube, but with dynamic security on 2 measures.
Let's say, we have 2 departments (food and non-food). Users within food are allowed to see sales and pieces from the food department, but not from the non-food department.
It is not an option to restrict access to the non-food department because there are other measure which they have access to. I tried cell security, but this is very slow and generates multiple empty rows on my selections.
View 3 Replies
View Related
Jun 15, 2015
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
View 9 Replies
View Related
May 12, 2015
I am setting up some security requirements - the requirement is that team leaders get to view all teams within their division. What I would like it to do when they log onto their dashboard/ssrs report is that it defaults to the team leaders default team (they can still select from other teams if they require).If I can avoid I don't really want another parameter in the report for this.
In my cube I have an attribute under the people dimension (where teams and division also reside) called Fee_Earner_Effective_Flag and where this is set to Y this is their current team.
Under the Role properties security under the Fee_Earner_Effective_Flag attribute I have set the Default member to
[People Primary FeeEarner].[Fee_Earner_Effective_Flag].[Fee Earner Effective Flag].&[Y]
When I then logon as the user and try to access the cube I get the following
Errors in the metadata manager. The '[People Primary Feeearner].[Fee_Earner_Effective_Flag].[Y]' security default member from the '{' attribute returns a result from a different hierarchy.
I have also tried setting the default member in the cube solution and then processing but then the cube failed.
View 3 Replies
View Related
Jan 16, 2007
I am attempting to set up a new user that has only the ability to run reports in the report manager.
I have created a new ActiveDirectory entry for DOMAINReportUser. I have a created a new folder with the reports, and have set this user as a Browser role on this directory, and all reports in the directory.
I have made it throught the security maze to gain access to these reports as this user, but I cannot dynamically run the reports. As the BUILTINAdministrator (content manager), I get a grayed background on the parameters and can run the reports. As this DOMAINReportUser, I get what looks like HTML parameter items, and cannot run a dynamic report. Even if I change the role of this user to Content Manager, I still cannot run a dynamic report.
How do you properly set up a user to be able to dynamically run, and only run, a report in Report Manager, and have this user only see reports in a single folder?
I have been fighting the security issues of creating a RS site and properly setting up access, and have yet to find a single site or person explaining the entire process in any coherant method.
Mark
View 3 Replies
View Related
Jun 3, 2015
I have number of users which are under 6 Groups. We have 4 drill through actions. Now I would like to restrict 2 drill through actions for only some groups.
View 2 Replies
View Related
Sep 20, 2015
There is a STIG Check that does not allow grant "Connect SQL" directly to any logins except SQL System and the SA account. My way of resolving this is to do the following:
Step One:
We create a Server Role called SQL_APPLICATIONS – for the application accounts
We create a Server Role called SQL_DBA – for the DBA accounts
and give them direct “Connect SQL’ server permissions. MAKE THE ROLE OWNER = sysadmin (group)
Note: I think that creating a Server Role is only available starting with SQL Server 2012, but not sure. I am using SQL 2012
Step two:
I add the members (Logins - SQL & Windows) – in this case any application accounts and DBA accounts to the new roles respectively
Step three:
I remove the “Connect SQL” Permission from each Login
The first problem i noticed is that the maintenance plans failed with "The owner domainusername of job db_backup does not have server access.I am currently using a test system and wondered If you think I will have trouble with the application connecting when I try and implement on the production systems.
View 3 Replies
View Related
Jun 29, 2007
Hi All,
I have a report running and I am attempting to assign role based security. I added a group to the site level security. The group I added contains child groups. It doesn't seem that report server is looking into the child groups to see if the logged in user is a member of the child group. Is there anyway to get this to work instead of adding all the groups directly? I suspect that report server is using cominterop and cominterop is not traversing the directory tree?
Thanks,
Darren
View 1 Replies
View Related
Feb 7, 2006
From Books Online Help:
In the role-based security model that is implemented for Reporting Services, users who are assigned to the Content Manager role can create and edit reports in Report Builder. Local administrators are automatically assigned to this role.
If you want other users to be able to work with a Report Builder report, you must create a role assignment for them that includes the default role "Report Consumer." Alternatively, you can create a custom role definition. As long as the customized role includes the "Consume reports" task, users who are assigned to that role will have sufficient permission to create and modify reports using Report Builder
---------
However, when I try to give a contact those rights, the "Report Builder" link still does not show up for my users. Is there any other place where I can examine the security rights to see why the Report Builder permission is not granted to my users?
View 1 Replies
View Related
Oct 30, 2015
If user want to see the grand total for a measure with include all members, even though the user has limited access for that member, so how we can do using DAX?For example, let’s say the total revenue for all the divisions in a cube is $15,000. You create a role called “Division A”, and set it up so members of that role can only see the revenue for Division A, which totals $3,000. If you use a front-end tool like Excel to access the cube and use the division hierarchy to see the total revenue, you will see the revenue of $3000 for Division A, but also want to see the Grand Total for the revenue as $15,000How we can achieve above scenerio in tabular model (DAX).
View 3 Replies
View Related
Aug 6, 2015
It seems simple but I'm not able to make it works. I've got a fact table with sites (DimImplantation) with allowed users (DimDroit) :
1 user can have access to many sites. I've tried these codes but only one worked and only in case where the user had only one site :
a) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME() ) ==> worked for 1user with 1 site
b) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME(); DimDroit[DimImplantation_ID];DimImplantation[DimImplantation_ID] ) ==> doesn't work and says 'not able to to identifie the value of DimImplantation[DimImplantation_ID]
in the current context.
c) CALCULATE(VALUES(DimDroit[DimImplantation_ID], SUMMARIZE('DimDroit',DimDroit[Utilisateur]), DimDroit[Utilisateur]=USERNAME()) ==> version for SQL2012. I've had 'End of input reached'.
View 3 Replies
View Related
Oct 10, 2015
I have two different roles, each one with a dax filter. One is for filtering users that access by Excel, and other for filtering users that access by Reporting Services, respectively:
=CONTAINS(FILTER(V_FILTRO_C;V_FILTRO_C[IDPerfilGrupo]<>7);V_FILTRO_C[iCursoCod];[SK_CURSO];V_D05_DIM_UTIL[DSC_LOGIN_USER];RIGHT(USERNAME();LEN(USERNAME())-SEARCH("";USERNAME())))
=CONTAINS(FILTER(V_FILTRO_C;V_FILTRO_C[IDPerfilGrupo]<>7);V_FILTRO_C[iCursoCod];[SK_CURSO];V_FILTRO_C[IDUtilizador];VALUE(CUSTOMDATA()))
To create only one role that serves Excel and Reporting Services users, is it viable to use only the || (OR) operator?, is there any other regard i should take?
View 2 Replies
View Related
Sep 30, 2015
I need to create a set so that when a user selects a month in filter (say 201506) then it should give me a list of months from 201406 to 201506. Any appropriate MDX query.
View 7 Replies
View Related
Jul 6, 2015
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?
View 4 Replies
View Related
Jun 18, 2007
I want to use an Active Directory security group that is a Distribution List for a new role assignment for an existing report. Can someone tell me if this is possible? I get an error each time I try:
The user or group name <DLName> is not recognized. (rsUnknownUserName)"
View 1 Replies
View Related
Aug 24, 2015
we are using a 2012 SSAS cube with many (about 100-200) roles. We use AdoMD to programmatically create, edit and drop roles. Everything works fine until we want to drop a role (this occurs only with some of the roles). I thought this was an AdoMD problem, but I actually get the same error when I delete such role in Management Studio. I attached the full exception message. I tried to remove all members before dropping the role to no avail, there must be some other dependency.
Program Location:
at Microsoft.AnalysisServices.Server.SendBatch(ArrayList objectsToDelete, ArrayList objectToAlter)
at Microsoft.AnalysisServices.MajorObject.DropPrivate(DropOptions options, XmlaWarningCollection warnings, ImpactDetailCollection impactResult, Boolean analyzeImpactOnly)
at Microsoft.AnalysisServices.MajorObject.Drop(DropOptions options)
at Microsoft.SqlServer.Management.SqlManagerUI.DropOlapObjects.DoDropObject(Int32 objectRowIndex)
at Microsoft.SqlServer.Management.SqlManagerUI.DropObjects.DropAllObjects(Boolean stopOnError)
View 2 Replies
View Related
May 23, 2007
Does anyone know if there is a way to use a SSIS task to add a role to SSAS cube? At the moment I use Management Studio, Right Click "Roles" under the cube and go through New Role wizard, but I'd like to be able to include this in a SSIS package instead.
Thanks
Richard
View 5 Replies
View Related
Dec 10, 2003
Hi,
Which role we can assign for user other than Sysadmin to able to see and execute all sql agent jobs?.
Thanks,
Ravi
View 7 Replies
View Related
Feb 11, 2004
Hi all,
Our SQL2000 server now allows all member of the Windows 2000 local administrators group log in with SysAdmin role.
I only want couple of people with sysadmin role. What should I do
to prevent that. I was told once that I should delete the
BUILTINAdministrator ID and manually add each window login ID
to SQL server. Am I on the right track?
Thank you for your help.
View 1 Replies
View Related
Sep 4, 2007
We have an application use Approle to read from database. If the client login to windows as administrator or a name that has the administrator rights, the application can get all data. If the client login to windows as a domain user that has limited rights, the application can't get all data.
I run profiler and found that it seems, when application use approle to access a database, the login name is the domain user that log into windows. Is there anybody know what type of right the window login name should have in order to get all data from a database?
Second question, when I log in to window as domainusername( username is not administrator, but has administrator rights). In the profiler, I can see the application use this domainusername access database. However, under sql server login node, I didn't find domainusername. Is this because, the domainusername belongs to buildinadministrator?
Thanks
View 1 Replies
View Related
Nov 2, 2015
Just wanted to know is there any way to write scope statements only for a specific role in the cube.
View 3 Replies
View Related
May 21, 2008
On our report server I have several folders. I would like to set up security such that a user doesn't see any folders except the "My Reports" folder (I enabled the My Reports site option) and a few other folders I specify. These are a shared folder and the models folder.
Is there a way to create a system level role that by default can't browse any folders except My Reports and ones I specifically grant? I would also like this person to default to having the item level security role of report builder to create reports off of report models.
The only way I have been able to accomplish this so far was to add the user to the the system users site level role and then go to every undesired folder and delete the item level role assignments for the user. It seems odd for the user to have access to every folder then to remove permissions from each folder. Should I create a role with no tasks and then selectively add in the permissions to the desired folders?
I did try creating a new system level role that only had the Execute Report Definitions task assigned and assigned the user exclusively to this role but they could still see all of the folders and their My Reports folder. When I created a new folder the user had been defaulted to item level roles of Browser and Report Builder. I suspect these defaults are what caused the new folder to be visible as soon as I created the folder.
I searched the forum for my question so hopefully I am not causing a re-answer of this question.
Thanks in advance,
Sean
View 3 Replies
View Related
Jul 28, 2015
I have a requirement to set Description values for our cube dimensions and attributes.
I've done this for regular dimensions but I cant seem to find a way for role playing dimensions. I can set the base dimension descriptions but not the 'clones'. Is this possible?
View 2 Replies
View Related
Oct 28, 2015
I am modelling cube in SSAS. Cube has around 20 dimensions and 6 fact tables. Some of the dimensions are common among the fact tables. e.g. Time dimension. Fact_PNL has 3 date columns for those we have 3 role playing dimensions in the dimension usages.
Another fact table has 5 date columns for them as well we have separate role playing dimensions in dimension usage tab. We have a common dimension Company which is foreign key in all fact tables. We might need to combine the data from multiple facts to get final output.
Should i create 6 role playing dimension for each of the fact table or use the same dimension for all fact tables?
Role playing dimensions should be created when we have multiple columns pointing to the same dimension ?
View 2 Replies
View Related
Mar 27, 2002
Occasionally I need to restore a SQL 7 database from Server B onto Server A. Is there a way to backup/script the existing database security information on Server A and then restore the security info after the database restoration?
Thanks,
Rod
View 1 Replies
View Related
Sep 8, 2006
Hi all,
I have setup a new SQL 2000 SP4 and internal auditor query about revoke permission from Public role and remove guest from all databases.
1. Can I revoke all default permissions (select on system tables in all DBs) from "Public" role? I am concern any error after such action.
2. I found that guest account in DB -- master, tempdb and msdb. According to Microsoft documents. The account should not remove and can't from master and tempdb. How about msdb?
Thanks,
Regards,
Edwin
View 7 Replies
View Related
May 16, 2007
Hi,
I have a folder structure of reports like this
Home
|
/
A B
Now, suppose I have created this folder, A first and user, say A_User is given permission to view this folder. I am expecting this user to browse from 'Home' to A folder but as you know to browse folder 'Home' he must also be added to 'Home' folder . So I add him to 'Home' now.
The problem comes when I add new folder B, for user B_User. The moment I add this folder A_User is also added to folder B because of the inheritance of users to child folders.
So user A is able to see both A and B folder....and thats my problem, he is supposed to only A folder and not B
Is there any way to disable this inheritance feature ??
I tried deleting A_User from B with a script using "InheritParentSecurity" method of ReportingService but it says that it can not delete inherited users.
How can I fix the problem ? without need to manually delete users from folders ??/
thanks
prashant
View 3 Replies
View Related
Oct 30, 2007
I have created reports using SSRS 2005 and deployed in ReportServer.
I calling these reports form my web application.Users have different roles based on their login into web application.I need check these user roles and display the reports based on their roles. There could be 10 reports in total, but for this user i should display only 4 out of them. Its pretty urgent. Can somebhelp on this?
View 1 Replies
View Related
Aug 17, 2015
Is it possible to design a Cube with a Dimension "DimEmployee" (SCD Type2) and Query the following: What "title" had the person "xyz" at time "2015-01-01"? - See Example data on Images.
I have also a DimTime with Day granularity. The DimEmployee is not at day granularity.
View 9 Replies
View Related
Aug 11, 2015
My company has a Windows 2008 R2 server which is running SQL Server v11.0.5058. This server was previously running SQL Server 2008 and was recently upgraded. Since the upgrade I have noticed that when I connect to this server using SSMS and Windows authentication it seems as though I have a limited user context as I cannot see SQL Agent in the server tree at all and underneath the server security > logins folder I can only see the sa and SQL Server Windows service accounts (there are many more).
If I connect to the server using SSMS and the sa credential then I can see everything I expect to be able to see as a sysadmin.
I tried connecting as sa, then deleting my Windows AD account from the security > logins folder and reading my Windows AD account with the sysadmin role however this yielded the same result, when I connect using Windows authentication I still appear to be in a limited user context.
We have several other SQL 2008 / 2012 servers within our organization and all of them appear to be working fine / none of them exhibit this problem.
View 8 Replies
View Related
Apr 26, 2007
I'm using Reporting Services 2000 and I have a situation where I need to specify the file formats available to export. I know I can change the RSReportServer.config file to change these at a global level, but what I really need is to be able to configure these differently for different user groups. For example:
group 1 - has all available export formats available
group 2 - only has the ability to export to excel
group 3 - has no option to export to any format.
Is there a way for this to be done? If so, can anybody give me any pointers on how it can be done?
Any help given will be most appreciated!!
View 3 Replies
View Related
May 26, 2015
An old website I inherited uses sa to connect to SQL SessionState and had the details in the web.config. This is bad for security.The session state database is of -sstype "t" which is defined as:Temporary. Session state data is stored in the SQL Server tempdb database. Stored procedures for managing session state are installed in the SQL Server ASPState database. Data is not persisted if you restart SQL. This is the default.What kind of WIndows user, SQL Login, role and permissions do I need to create to make Session State secure? (Windows Server 2012 and SQL Server 2012 mixed mode authentication, Webfarm).
View 4 Replies
View Related
Feb 21, 2006
I am working on the security model for an application that will be used by 100s of users with a dedicated SQL 2005 database for this application and access via SQL XML Web Services.
The client has asked to make it "open" during alpha testing such that anyone can access the web services without having to set them up first. Is there a way to do this? The best I can figure is to use mixed mode security and hard code a login and password. Any method using Windows authentication would require that I add every user at a minimum to the database.
In production, all users will have an active directory role specified that determines if they should have access to the web services or not. However, it is my understanding that to use Windows authentication, I would still need to add each individual user at a minimum as a Login to the SQL Server, and under best practices also as database users with permissions granted to the endpoint.
Am I correct in the above, or is there a more efficient way to achieve these results?
Thanks
-L
View 1 Replies
View Related
