determine a role

hi,

since I am kind o'new with SQL, I preffer get an advice fro you pro's: I created an application which performs access to a database on an SQL server. the application will be used by a few different users, each on a different computer. the application calls stored procedures, updates\inserts records in tables on the SQL and delete rows. what would be the best role to define the users activity ? How do I limit their activity ONLY to the specified actions ?

For the tasks you mentioned, typically there will be permissions at the granularity you want (for example EXECUTE on a stored procedure, INSERT and UPDATE on a table, etc.), but I would strongly suggest referring to BOL to learn more about this topic. A good starting point can be

“Security Considerations for Databases and Database Applications” (http://msdn2.microsoft.com/en-us/library/ms187648(SQL.90).aspx).

I hope this helps, but let us know if you have any further question.

-Raul Garcia

SDE/T

SQL Server Engine

Design Tool and Security roles

I have 2005 SP1 and after adding the new users to the Server in Studio, I go to the Design tool and create a new role, I add the user and check the following: process database and read definition.

I then go to Cell Data Tab and select Test Cube Secuirty; when asked if I want to deploy the cube I say "yes". after I deploy it I get the following message: "You are browing the cube using credential of the following roles: Sales" then I get the message below that

"Connection can not be made. Ensure the server is running" and

No connection could be made because the target server actively refused it.

I am connected to the cube so its running, remote connection are allowed, I already connected, just attempting to define and test other roles? Is there an issue with Test cube security? any suggestions?

Try to re-connect your Cube browser after deploying the solution.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Yes, I do reconnect to my cube but I always get the error message, I can't test security, even though the roles I defined do allow the users to access the cube I still get this message. Thank you,|||

It is a bit strang situation.

What worries me is that you say "adding the new users to the Server in Studio, I go to the Design tool and create a new role".

You should'nt go and manage your security in different places. If you to add uses in SQL Management Studio, next time you deploy your solution, Role memebership will get overwritten. Try to make all the changes in BI Dev Studio.

I would also recommend you upgrde your Analysis Services to SP2.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

Maybe I miscommunicated that, I do make them in the design tool, I keep all my roles in the BI tool but I started to try to test them there before I deploy them and I can't test them that was my error. When I go to CELL Data and select "test Cube Security" I get the error.

Thank you,

Janet

|||

One litte trick to troubleshoot your problem.

Try deploying the solution from BI Dev Studio. Process it. Then go back to SQL Management Studio and try to browse your cube under different user or role to test your securtiy.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

I don't know how to browse the cube under a different role in SQL Managment Studio.

I think we have moved onto a different direction, let me try to explain a little better.

All my roles actually work when the user accesses the cube the defect or error is I can't "test the role" in BI Development tool. If I select a role that I know actully works and attempt to do the test from I go to the "Cell Data" tab and click on Test Cube Security using a role that I know allows the user access I get this message " your are browing a cube using the following role: process and read" - connection can not be made insure the server is running. But if i check with the users in that role, all the users in that role can access the cube.

So my problem is not that the role is not allowing access, its that Test Cube Security gives me a bogus error, because that role can access and browse the cube.

Thank you,

Janet

|||

Just wondering does the "test cube security" actually work in the BI tool for everyone but me? is anyone else having this problem?

thank you

Design Tool and Security roles

I have 2005 SP1 and after adding the new users to the Server in Studio, I go to the Design tool and create a new role, I add the user and check the following: process database and read definition.

I then go to Cell Data Tab and select Test Cube Secuirty; when asked if I want to deploy the cube I say "yes". after I deploy it I get the following message: "You are browing the cube using credential of the following roles: Sales" then I get the message below that

"Connection can not be made. Ensure the server is running" and

No connection could be made because the target server actively refused it.

I am connected to the cube so its running, remote connection are allowed, I already connected, just attempting to define and test other roles? Is there an issue with Test cube security? any suggestions?

Try to re-connect your Cube browser after deploying the solution.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Yes, I do reconnect to my cube but I always get the error message, I can't test security, even though the roles I defined do allow the users to access the cube I still get this message. Thank you,|||

It is a bit strang situation.

What worries me is that you say "adding the new users to the Server in Studio, I go to the Design tool and create a new role".

You should'nt go and manage your security in different places. If you to add uses in SQL Management Studio, next time you deploy your solution, Role memebership will get overwritten. Try to make all the changes in BI Dev Studio.

I would also recommend you upgrde your Analysis Services to SP2.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

Maybe I miscommunicated that, I do make them in the design tool, I keep all my roles in the BI tool but I started to try to test them there before I deploy them and I can't test them that was my error. When I go to CELL Data and select "test Cube Security" I get the error.

Thank you,

Janet

|||

One litte trick to troubleshoot your problem.

Try deploying the solution from BI Dev Studio. Process it. Then go back to SQL Management Studio and try to browse your cube under different user or role to test your securtiy.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

I don't know how to browse the cube under a different role in SQL Managment Studio.

I think we have moved onto a different direction, let me try to explain a little better.

All my roles actually work when the user accesses the cube the defect or error is I can't "test the role" in BI Development tool. If I select a role that I know actully works and attempt to do the test from I go to the "Cell Data" tab and click on Test Cube Security using a role that I know allows the user access I get this message " your are browing a cube using the following role: process and read" - connection can not be made insure the server is running. But if i check with the users in that role, all the users in that role can access the cube.

So my problem is not that the role is not allowing access, its that Test Cube Security gives me a bogus error, because that role can access and browse the cube.

Thank you,

Janet

|||

Just wondering does the "test cube security" actually work in the BI tool for everyone but me? is anyone else having this problem?

thank you

Design Question - Implementing User Privileges

I have the following skeleton DDL and need to do the following:
*Ability to override a users privileges of the 'Role' that he belongs to.
For e.g. if a user belongs to Role 1 that has Privileges 1, 2, and 3 then I
want the ability to override this users privileges such that he can be
assigned, say, privileges 1, 2, and 4 though he still belongs to Role 1. I a
m
unable to make much headway into this. Any help is appreciated.
-- table Roles
CREATE TABLE [dbo].[Roles] (
[RoleID] [int] IDENTITY (1, 1) NOT NULL ,
[RoleDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
--table Privileges
CREATE TABLE [dbo].[Privileges] (
[PrivilegeID] [int] IDENTITY (1, 1) NOT NULL ,
[PrivilegeDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO
--table RolePrivilege
CREATE TABLE [dbo].[RolePrivilege] (
[RoleID] [int] NOT NULL ,
[PrivilegeID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Users] (
[UserID] [int] NOT NULL ,
[RoleID] [int] NOT NULL ,
[Username] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Password] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
Thanks,
NaveenYou need to modify your schema to do this...
Although there are several ways to implement such a pattern one way is to
add another table, similar to your RolePrivileges table, called UserPrivilig
es
--table UserPrivilege
CREATE TABLE [dbo].[UserPrivilege] (
[UserID] [int] NOT NULL ,
[PrivilegeID] [int] NOT NULL,
[Allow] TinyInt Not Null Default 1
) ON [PRIMARY]
GO
Then have your application code construct actual privileges from Both
UserPrivilege and RolePrivilege, with UserPrivileges taking Precedence, (or
Overriding) Role Provileges when there was a conflict i.e. If a User's role
Allows Privledge 10, but UserPrivileges has Deny for Prifiledge 10, th3en
Priviledge 10 would be denied...
"Naveen" wrote:

> I have the following skeleton DDL and need to do the following:
> *Ability to override a users privileges of the 'Role' that he belongs to.
> For e.g. if a user belongs to Role 1 that has Privileges 1, 2, and 3 then
I
> want the ability to override this users privileges such that he can be
> assigned, say, privileges 1, 2, and 4 though he still belongs to Role 1. I
am
> unable to make much headway into this. Any help is appreciated.
> -- table Roles
> CREATE TABLE [dbo].[Roles] (
> [RoleID] [int] IDENTITY (1, 1) NOT NULL ,
> [RoleDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ) ON [PRIMARY]
> GO
> --table Privileges
> CREATE TABLE [dbo].[Privileges] (
> [PrivilegeID] [int] IDENTITY (1, 1) NOT NULL ,
> [PrivilegeDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
> GO
> --table RolePrivilege
> CREATE TABLE [dbo].[RolePrivilege] (
> [RoleID] [int] NOT NULL ,
> [PrivilegeID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Users] (
> [UserID] [int] NOT NULL ,
> [RoleID] [int] NOT NULL ,
> [Username] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Password] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Email] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> Thanks,
> Naveen|||Thanks. I think I got a good idea on what to do.
"CBretana" wrote:
> You need to modify your schema to do this...
> Although there are several ways to implement such a pattern one way is to
> add another table, similar to your RolePrivileges table, called UserPrivil
iges
>
> --table UserPrivilege
> CREATE TABLE [dbo].[UserPrivilege] (
> [UserID] [int] NOT NULL ,
> [PrivilegeID] [int] NOT NULL,
> [Allow] TinyInt Not Null Default 1
> ) ON [PRIMARY]
> GO
>
> Then have your application code construct actual privileges from Both
> UserPrivilege and RolePrivilege, with UserPrivileges taking Precedence, (o
r
> Overriding) Role Provileges when there was a conflict i.e. If a User's rol
e
> Allows Privledge 10, but UserPrivileges has Deny for Prifiledge 10, th3en
> Priviledge 10 would be denied...
> "Naveen" wrote:
>