Dynamic Security in SSAS 2005

I am trying to create dynamic security and was hoping to use a named query to link the fact table to a dimension that contains Active Directory users and companies - a user can have access to multiple companies. The problem is that I cannot find out how to get the username of the current user.

Anyone have any ideas?

thanks in advance.

Does the standard MDX keyword: UserName return what you want, like:

>>

With member [Measures].[CurrentUser] as UserName

select {[Measures].[CurrentUser]} on 0

from [Adventure Works]

>>

|||

thanks for the feedback - however, I am relatively new to Analysis Services (and 2005) - and cannot see where this would be created. Are you saying that this is a calculated Dimension (or measure?)

Many thanks

|||

The MDX keyword: UserName will return the name of the current user in an MDX expression, which could be used in many different contexts:

http://msdn2.microsoft.com/en-us/library/ms144884.aspx#expressions_statements_scripts

>>

SQL Server 2005 Books Online

Key Concepts in MDX (MDX)

In MDX, an expression is a combination of identifiers, values, functions, and operators that Analysis Services can evaluate to retrieve an object, such as a set or a member, or a scalar value like a string or a number.

>>

http://msdn2.microsoft.com/en-us/library/ms146016.aspx

>>

SQL Server 2005 Books Online

UserName (MDX)

Returns the domain name and user name of the current connection.

>>

|||Thanks for the feedback.

Dynamic Security and Rool ups

Hi

I thought I had this dynamic security worked out but I guess not.

this is on AS 2000.

I have a Fact table - one of the columns is a companyid, this is joined to the company dimension.

For security I have another table "empcompany" which lists all the users (ntusername) and the company codes they can access. I created a member property against the company code and used MDX similar to this:

filter([Companycode].[Companycode].members,([Companycode].CurrentMember.Properties("ntusername") = username))

The problem is that when I join the "empcompany" table to either the fact or to the company dimension instead of getting 1000 rows I get 80000+ rows and my numbers are wrong. Most of the users have access to more than 1 company.

Any one have any ideas?

Thanks

Steve

Hi Steve,

My suggestion would be to try the "Security Fact Table" approach with the "empcompany" table, as discussed in slides 18-32 of this webcast deck (you're using the "Member Property" approach above). In the 2nd approach, you don't need to join "empcompany" to the fact table - rather, it becomes the fact table for a "Permissions" cube, which is combined in a virtual cube:

http://support.microsoft.com/kb/828343/

>>

Support WebCast: Dynamic Dimension Security in Microsoft SQL Server 2000 Analysis Services

...

Dynamic Security
The Three Basic Approaches

Member property approach

Permission data (e.g. UserName) is stored at the desired dimension level

Security fact table approach

A permissions cube is combined with original source cube using a virtual cube

Filter members at the leaf level

Filter members at the non-leaf level

User-defined function callout

Call a user-defined function to reference an outside source (e.g. external RDBMS)

Dynamic Security

Hi,

I am a little concerned about the recent discussion of “Dynamic Security” and SSAS 2005.

http://blogs.proclarity.com/blogs/dgustafson/archive/2006/3/21/54.aspx

Is the “Dynamic Security” approach really scalable?

Christian

Don't take my post at blogs.proclarity.com the wrong way. This is not so much a performance problem in SSAS 2005. Rather, using dynamic security makes external OLAP query caching difficult for our product, ProClarity Analytics Server. My advice would be to think twice before using dynamic security to simply bypass the management of OLAP roles or AD groups.

Dynamic search with xml?

We have a table like this:
CREATE TABLE [Account](
[ID] [int] NULL,
[Name] [nvarchar](20) NULL,
[Status] [nvarchar](20) NULL
)
We have an xml file that defines search parameters that looks like
this:
<SearchParms>
<Parm col="ColToSearchOn" val="ValToSearchFor"/>
</SearchParms>
The basic idea is to create a stored procedure that accepts an xml doc
as parameter and then searchs the account table based on the parameters
passed in the xml file.
For example, if this is the doc passed into the sproc:
<SearchParms>
<Parm col="ID" val="123"/>
</SearchParms>
Then the sproc will eval something like:
SELECT *
FROM ACCOUNT
WHERE ID = 123
If the doc looks like this:
<SearchParms>
<Parm col="ID" val="123"/>
<Parm col="Name" val="Straus, LLC"/>
</SearchParms>
Then the sproc will eval something like:
SELECT *
FROM ACCOUNT
WHERE ID = 123
AND Name = 'Straus, LLC'
So, I'm wondering if there is a way to create a join between the xml
file and the relational table that will return the results I'm wanting.
I was hoping to be able to utilize some fancy XQuery type of stuff
rather than dynamically build a string based on the xml and then run
the sp_executesql method on the string.
I'm open to suggestions on xml format. I've only included the above as
an example. Ideas? Thanks for any help.Something like,
select * from account
where
id in
(
select a.b.value('(.)[1]', 'int')
from @.xml.nodes('/descendant::@.val')
)
Pohwan Han. Seoul. Have a nice day.
"Chris Kilmer" <christopherkilmer@.gmail.com> wrote in message
news:1138404194.372843.253320@.f14g2000cwb.googlegroups.com...
> We have a table like this:
> CREATE TABLE [Account](
> [ID] [int] NULL,
> [Name] [nvarchar](20) NULL,
> [Status] [nvarchar](20) NULL
> )
>
> We have an xml file that defines search parameters that looks like
> this:
> <SearchParms>
> <Parm col="ColToSearchOn" val="ValToSearchFor"/>
> </SearchParms>
> The basic idea is to create a stored procedure that accepts an xml doc
> as parameter and then searchs the account table based on the parameters
> passed in the xml file.
> For example, if this is the doc passed into the sproc:
> <SearchParms>
> <Parm col="ID" val="123"/>
> </SearchParms>
> Then the sproc will eval something like:
> SELECT *
> FROM ACCOUNT
> WHERE ID = 123
> If the doc looks like this:
> <SearchParms>
> <Parm col="ID" val="123"/>
> <Parm col="Name" val="Straus, LLC"/>
> </SearchParms>
> Then the sproc will eval something like:
> SELECT *
> FROM ACCOUNT
> WHERE ID = 123
> AND Name = 'Straus, LLC'
> So, I'm wondering if there is a way to create a join between the xml
> file and the relational table that will return the results I'm wanting.
> I was hoping to be able to utilize some fancy XQuery type of stuff
> rather than dynamically build a string based on the xml and then run
> the sp_executesql method on the string.
> I'm open to suggestions on xml format. I've only included the above as
> an example. Ideas? Thanks for any help.
>|||If your column names would be fixed, Han's suggestion may work.
However, if you need to create different predicates and you query against a
relational table, you either have to make the table into an XML datatype
itself (e.g using FOR XML) and then use local-name() to compare against the
col attribute or use dynamic SQL.
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OJBUABxJGHA.1320@.TK2MSFTNGP15.phx.gbl...
> Something like,
> select * from account
> where
> id in
> (
> select a.b.value('(.)[1]', 'int')
> from @.xml.nodes('/descendant::@.val')
> )
> --
> Pohwan Han. Seoul. Have a nice day.
> "Chris Kilmer" <christopherkilmer@.gmail.com> wrote in message
> news:1138404194.372843.253320@.f14g2000cwb.googlegroups.com...
>sql

Dynamic Search SP

Hi,

I'm new to SQL 2005.

I need to create sp that perform search on Users table.

It gets few Parameters.

@.UserName

@.UserStatus

@.UserRole

@.OrderByColumn

All Parameters are optional, and i need to build sql statement that include only the parameters that the sp got on the specific.

I cand do that by concatanating a string and execute it using the sp_executesql().

Is it the best way in sql 2005 ?

Can you please show me an example using Case When or what ever ?

Thanks A lot.

Tok

A very good article on dynamic search that contains more than examples at:

http://www.sommarskog.se/dyn-search.html.

Please check it out.

Dynamic Scripts

I've found that SSIS does not work well with running small dynamic scripts ...

For example, I have scripts that need be created via dynamic SQL. I want the dynamic SQL to be created using variables in SSIS.

The ways around this are to create small temporary stored procedures that I can create in the SSIS package at run time that have dynamic SQL in it - and execute it with the variables as my parameters.

Does anybody else do this, or have a better way of doing it?

I would be inclined to use:

package variables for my input values;|||

I have the same problem too; I tried 2 approaches to do it without success. Could someone please point out where I’m missing and how to do it?

Approach 1:

1, use an Execute SQL task the define an package variable

select last_modified_date as variable1

from table

2, use another Execute SQL task to define second package variable(variable2)

select * from sourceTable

where last_modified_date > ? --variable1

3, use variable2 in OLE db source in a data flow to get source data

Approach 2:

1, 1, use an Execute SQL task with dynimac sql to define an package variable

select ‘select * from SourceTable where last_modified_date > ‘’ + ‘’’ +last_modified_date + ‘’’ AS variable_SQL

from table

2, use the variable (variable_SQL) to get source data in OLE db source in a data flow

|||

Jessie,

Why is this not working for you? Do you get an error message?

-Jamie

|||I don't think he meant to say that it doesn't work- it's just not very elegant ... it's such a workaround and difficult to work with ...|||

This may work for you.

Create 2 string variables (SQL_DATE & SQL_QUERY)
Set SQL_DATE to 20050101
Change Expression property of SQL_QUERY to True
In Expression Value property, type in "Select * from mytable where datecol = " + @.[User::SQL_DATE]

Look at the resulting value. The query and the date should now be together.

You can apply this to many different scenarios. The biggest flaw, which will be fixed with SP1 is that there is no Expression designer for variables. Just use the one that is in one of the other tabs to test your expression.

cheers,

Andrew

|||I have just been creating stored procs and passing the parameters into them. This has been the easiest solution for me.

Dynamic Scripts

I've found that SSIS does not work well with running small dynamic scripts ...

For example, I have scripts that need be created via dynamic SQL. I want the dynamic SQL to be created using variables in SSIS.

The ways around this are to create small temporary stored procedures that I can create in the SSIS package at run time that have dynamic SQL in it - and execute it with the variables as my parameters.

Does anybody else do this, or have a better way of doing it?

I would be inclined to use:

package variables for my input values;|||

I have the same problem too; I tried 2 approaches to do it without success. Could someone please point out where I’m missing and how to do it?

Approach 1:

1, use an Execute SQL task the define an package variable

select last_modified_date as variable1

from table

2, use another Execute SQL task to define second package variable(variable2)

select * from sourceTable

where last_modified_date > ? --variable1

3, use variable2 in OLE db source in a data flow to get source data

Approach 2:

1, 1, use an Execute SQL task with dynimac sql to define an package variable

select ‘select * from SourceTable where last_modified_date > ‘’ + ‘’’ +last_modified_date + ‘’’ AS variable_SQL

from table

2, use the variable (variable_SQL) to get source data in OLE db source in a data flow

|||

Jessie,

Why is this not working for you? Do you get an error message?

-Jamie

|||I don't think he meant to say that it doesn't work- it's just not very elegant ... it's such a workaround and difficult to work with ...|||

This may work for you.

Create 2 string variables (SQL_DATE & SQL_QUERY)
Set SQL_DATE to 20050101
Change Expression property of SQL_QUERY to True
In Expression Value property, type in "Select * from mytable where datecol = " + @.[User::SQL_DATE]

Look at the resulting value. The query and the date should now be together.

You can apply this to many different scenarios. The biggest flaw, which will be fixed with SP1 is that there is no Expression designer for variables. Just use the one that is in one of the other tabs to test your expression.

cheers,

Andrew

|||I have just been creating stored procs and passing the parameters into them. This has been the easiest solution for me.