Showing posts with label roles. Show all posts
Showing posts with label roles. Show all posts

Monday, March 12, 2012

How to Use Login-Less Users the Alternate to Application Roles?

SQL Server 2005 introduces a new type of database user, one that is created using the “WITHOUT LOGIN” clause. This user is not mapped to a login, and is being touted as an alternate to Application Roles because no password is needed.. What I do not understand is how to use this type of user. I know how to use sp_setapprole with Application Roles, what are the equivalent steps needed to use login-less users?

Thank you in advance.

Terry Duffy

As I recall, the [WITHOUT LOGIN] option allows a user into the server without a specific database login. That user is mapped to the GUEST account.

In my opinion, you never want to use the GUEST account, so I couldn't recommend exploring this any further. (But someone must have a good case for this option.)

|||

You can impersonate loginless principals (using EXECUTE AS USER/EXECUTE AS on a module) the same way you can impersonate regular SQL users.

The only difference is that, based on the SQL USER SID, the system can recognize that these principals don’t have any login mapped to them. It will create a low privileged login token for impersonation, and a full user token for the database.

If you want to use them in a similar fashion as application roles, you can use the WITH NO REVERT or WITH COOKIE clauses in EXECUTE AS (ad hoc). http://msdn2.microsoft.com/en-us/library/ms181362.aspx

You can use them as an application context (using EXECUTE AS in your SP/UDF definition) and grant permissions only to this loginless user. This way you will only need to grant permission to execute your application SP/UDF to other users instead of granting direct permissions on the resources.

I also talk about these new principals on my blog: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx

Let us know if you have any further questions or feedback.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Arnie, users without login are only mapped to guest if they switch context to another database than the one they're defined in - because guest is by default disabled in user databases, this means that users without login are restricted from going to other user databases. You don't have to use the guest account, to use a user without login - such user is useful by itself. This mapping to guest is actually the only significant difference between users without login and approles.

Thanks

Laurentiu

|||

Thank you all. Just yesterday I attended a MS security web cast where login less users were highly recommended, so I do intend to research this further. Where application roles require the use of a password, this new type of user does not. Raul, your blog was cited as a resource and I was familiar with your content.

If I am reading this correctly, the only way to use this type of user is with "EXECUTE AS", and there is no comparable functionality to sp_setapprole. Is this correct? If true, I just don't see any advantage to this type of user over using "EXECUTE AS" with a user with a login, and APPLICATION ROLES (despite the password) seem more secure.

Thanks again to all.

Terry

|||

Both EXECUTE AS (stand alone) and sp_setapprole impersonation mechanisms can be used in similar ways. Both have options to prevent reverting back to the original context and both have an optional cookie-based mechanism to revert to the original context.The main difference between approles & user without login is the mechanism to verify the right to impersonate them: Approles are based on passwords and loginless users are based on permissions.

While using password as a protection mechanism can be effective, from the management point of view, requiring a password for implies that the security relies on keeping the password safe and having good password policies around them.The most common pitfall when using approles is hardcoding the password in scripts and applications. I have seen this mal-practice multiple times, and besides the obvious problem that it only takes to find the approle once to compromise the security, there is no easy way to comply with password policies and change the password regularly.If an attacker knows the password for an approle the only way to keep her from abusing the approle is to change the password to something different (affecting also regular applications and users who will need to get the new password). For loginless users, you only need to revoke/deny permission to impersonate such user o the attacker.

If you decide to use approles (a valid option, as Microsoft fully supports the feature in SQL Server 2005) I strongly recommend having a strategy to avoid hardcoding the password in any script/application and have password policies (especially password maximum life-time) in mind.

We appreciate any comments and suggestions on how we can improve our security features. If you have any concerns or feedback regarding loginless users or approles, please let us know.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

How to Use Login-Less Users the Alternate to Application Roles

SQL Server 2005 introduces a new type of database user, one that is
created using the "WITHOUT LOGIN" clause. This user is not mapped to a
login, and is being touted as an alternate to Application Roles
because no password is needed.. What I do not understand is how to use
this type of user. I know how to use sp_setapprole with Application
Roles, what are the equivalent steps needed to use login-less users?
Thank you in advance.
Terry Duffy
You use it with EXECUTE AS. This blog entry has a demo
walking through how to use similar to an application role.
http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx
-Sue
On 12 Apr 2007 14:44:27 -0700, "Terry" <tduffy@.calamos.com>
wrote:

>SQL Server 2005 introduces a new type of database user, one that is
>created using the "WITHOUT LOGIN" clause. This user is not mapped to a
>login, and is being touted as an alternate to Application Roles
>because no password is needed.. What I do not understand is how to use
>this type of user. I know how to use sp_setapprole with Application
>Roles, what are the equivalent steps needed to use login-less users?
>Thank you in advance.
>Terry Duffy

How to Use Login-Less Users the Alternate to Application Roles

SQL Server 2005 introduces a new type of database user, one that is
created using the "WITHOUT LOGIN" clause. This user is not mapped to a
login, and is being touted as an alternate to Application Roles
because no password is needed.. What I do not understand is how to use
this type of user. I know how to use sp_setapprole with Application
Roles, what are the equivalent steps needed to use login-less users?
Thank you in advance.
Terry DuffyYou use it with EXECUTE AS. This blog entry has a demo
walking through how to use similar to an application role.
http://blogs.msdn.com/raulga/archiv.../03/655587.aspx
-Sue
On 12 Apr 2007 14:44:27 -0700, "Terry" <tduffy@.calamos.com>
wrote:

>SQL Server 2005 introduces a new type of database user, one that is
>created using the "WITHOUT LOGIN" clause. This user is not mapped to a
>login, and is being touted as an alternate to Application Roles
>because no password is needed.. What I do not understand is how to use
>this type of user. I know how to use sp_setapprole with Application
>Roles, what are the equivalent steps needed to use login-less users?
>Thank you in advance.
>Terry Duffy

How to Use Login-Less Users the Alternate to Application Roles

SQL Server 2005 introduces a new type of database user, one that is
created using the "WITHOUT LOGIN" clause. This user is not mapped to a
login, and is being touted as an alternate to Application Roles
because no password is needed.. What I do not understand is how to use
this type of user. I know how to use sp_setapprole with Application
Roles, what are the equivalent steps needed to use login-less users?
Thank you in advance.
Terry DuffyYou use it with EXECUTE AS. This blog entry has a demo
walking through how to use similar to an application role.
http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx
-Sue
On 12 Apr 2007 14:44:27 -0700, "Terry" <tduffy@.calamos.com>
wrote:
>SQL Server 2005 introduces a new type of database user, one that is
>created using the "WITHOUT LOGIN" clause. This user is not mapped to a
>login, and is being touted as an alternate to Application Roles
>because no password is needed.. What I do not understand is how to use
>this type of user. I know how to use sp_setapprole with Application
>Roles, what are the equivalent steps needed to use login-less users?
>Thank you in advance.
>Terry Duffy

Friday, February 24, 2012

How to use cell data security with visual totals?

Hi,

We are working on a project with SQL Server 2005 and SSAS.

We created several roles using dimension security and one special role that requires cell data access permissions (i.e. cell security).

This last role is giving us trouble since we do not know how to implement visual totals on cell security.

A problem description follows:

1) We have a budget and expenses cube with a) Account, b) Time and c) Cost Center dimensions
2) The special role was created for a group of users who have the following requirement for data access permission:
- for some cost centers, restrict access to some accounts
- and for the rest of cost centers see all accounts
3) When looking at data at the cost center and account level, the role works fine. Restrictions are applied correctly.
4) However when looking at the data at the cost center level ONLY, the aggregated total shows a sum for all accounts, regardless of restrictions.


We know that if you use regular dimension security, you can enable Visual Totals in order to have aggregated values that reflect
the restrictions applied at the atomic level.

However, we do not know how to apply visual totals on cell data security. If anyone has faced similar situation,
we would appreciate any help on how to do this.


Another way to put it:

1) In a cube with a Product dimension (Family > Category > Product) and a Geography dimension (Country > State > City)
2) You can set dimension security to restrict access to certain products.
3) If you enable Visual Totals when creating the role, the aggregations at the Category level will show total values excluding amounts for those restricted products.
4) If you do not enable Visual Totals, the aggregated totals at the Category level will add up all products, regardless of access restrictions at the product level.

We are trying to achieve the same with Cell Data Access Permissions, but there is no option to Enable Visual Totals. For instance:

1) Same cube as above
3) Your create a role with cell data access permission; for some cities, you restrict acess to some products, and for all other cities allow access to all products.
4) When you look at data at the City level, you want to aggregate totals not for all products, but excluding amounts for those restricted ones.


We would appreciate your help on this enormously!

Cheers!
jbadillo@.streamnova.com

Cell security was not designed to support visual totals as you described.|||

Hi,

Is there a way around this? Instead of using visual totals, is there some other way to get the right totals?

Cheers

|||

One approach, which seems to work in limited testing with Adventure Works, would involve introducing cell security MDX conditions into the cube MDX script. Basically, a scoped calculation sets the secured cell values to NULL, which will cause the aggregation of those values up the hierarchy to adjust accordingly. However, when cube cells are browsed, the secured cells will still show "#N/A". The limitation with this approach, of course, is that user and/or role logic will be needed, so that unrestricted users are unaffected.

The simplified scenario in Adventure Works was as follows:

For user XYZ, secure the data for Bikes in CY 2003, and rollups in the Sales Summary measure group should reflect this.

The Cell Read Permission expression (for a role with user XYZ as member) is:

Not ([Date].[Calendar].CurrentMember is [Date].[Calendar].[CY 2003]
and [Product].[Product Categories].CurrentMember is [Product].[Product Categories].[Bikes])

And the cube calculation MDX script is something like:

Scope(MeasureGroupMeasures("Sales Summary"),

[Date].[Calendar Year].&[2003],

[Product].[Category].[All Products].[Bikes]);

IF UserName = "XYZ"

THEN this = NULL END IF;

End Scope;