Need help with SQL Roles

Question

I'm totally new to this so sorry  if I sound very ignorant. I'm Developing a Windows Form Application that access an SQL DataBase in a server. I need to create permissions for different users with different access. I have read a lot of the Application Role but I don't quiet understand if that's what I need. I want to authenticate the user and according to the user's permission grant options or operations in the Windows Form Program. 

Thanks in advance for the help. 

Answer 1

I'm totally new to this so sorry  if I sound very ignorant. I'm Developing a Windows Form Application that access an SQL DataBase in a server. I need to create permissions for different users with different access. I have read a lot of the Application Role but I don't quiet understand if that's what I need. I want to authenticate the user and according to the user's permission grant options or operations in the Windows Form Program. 

Thanks in advance for the help. 

Answer 2

I'm sorry but you should first post this question to a SQL forum.  Once you know how your database will be setup, then you could get help here with connecting to it from VB.Net.

All I can really say is that you'll need to define your own users and roles in the database, assign users to roles, and set permissions in the tables for those roles.  The database has to be configured to allow SQL authentication and not just domain authentication.

Let me see if I can find an appropriate forum to move this to...

---

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

Answer 3

Thanks for the response. If you find the correct forum. Let me know.

Answer 4

It depends a lot on your application. Does it use stored procedures? Is two-tier? Three-tier?

If you need different levels of access within the application, the application needs to handle that on its own. If only some users are permitted to cancel orders, for instance, only these users should be able enter that form, or be able to press that button. If you would rely on SQL Server permissions alone, the unauthorised users would get a nasty message in their face.

You can implement this in different ways. In the system I work with we have five tables: functions, authorisationgroups, users, authorisationgroupfunctions and userauthorisationgroups. That is, users belong to a group, and the group is granted permission to a function. But you could also decide that instead of having your own user and groups table, rely on what SQL Server has, and then have a junctio table between function and database roles.

Whatever, you still need to consider what happens if the users get access to the database directly. To this end, you could use application roles, as this could permit you to add users to the database with no permissions at all. When the users run the application, the application activates the application role, to which all required SQL Server permissions have been granted. However, this is not secure with a two-tier application, since the password for the application role has to be embedded into the application, and a skilled user can get hold of the password.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

I need to create permissions for different users with different access. I have read a lot of the Application Role but I don't quiet understand if that's what I need. I want to authenticate the user and according to the user's permission grant options or operations in the Windows Form Program. 

 

<content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">

The following steps make up the process by which an application role switches security contexts, but based on your requirement, this does not look to be suitable for you.

1. A user executes a client application.
2. The client application connects to an instance of SQL Server as the user.
3. The application then executes the sp_setapprole stored procedure with
   a password known only to the application.
4. If the application role name and password are valid, the application role is
   enabled.
5. At this point the connection loses the permissions of the user and assumes
   the permissions of the application role.

The permissions acquired through the application role remain in effect for the duration of the connection.

You probably might have to create a login and associated user in the database and then grant required permissions to each user. If a set of users require common permissions then you can create a database role and assign it to the users

</content>

Answer 6

It depends a lot on your application. Does it use stored procedures? Is two-tier? Three-tier?

If you need different levels of access within the application, the application needs to handle that on its own. If only some users are permitted to cancel orders, for instance, only these users should be able enter that form, or be able to press that button. If you would rely on SQL Server permissions alone, the unauthorised users would get a nasty message in their face.

You can implement this in different ways. In the system I work with we have five tables: functions, authorisationgroups, users, authorisationgroupfunctions and userauthorisationgroups. That is, users belong to a group, and the group is granted permission to a function. But you could also decide that instead of having your own user and groups table, rely on what SQL Server has, and then have a junctio table between function and database roles.

Whatever, you still need to consider what happens if the users get access to the database directly. To this end, you could use application roles, as this could permit you to add users to the database with no permissions at all. When the users run the application, the application activates the application role, to which all required SQL Server permissions have been granted. However, this is not secure with a two-tier application, since the password for the application role has to be embedded into the application, and a skilled user can get hold of the password.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

You are saying that I have to handle that on the application right? So how to the tables functions authorisationgruops, authorizationfunctions and userauthorizationgroups will look like. I already have the user table.

Answer 7

Those tables are not very advanced. Functions and Groups are just an id and name. And the other two tables are junction tables with (funid, groupid) and (groupid, userid).

Or at least you can keep them that simple if it fits or need. Or authorisationgroupfunction also has a permisionlevel column where 1 = readonly, 2 = insert/update and 3 = insert/update/delete. The function table also has a column which defines the maximum applicable permission. (Some functions are read-only functions.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

It all depends on your design, of course, but here is a simple outline:

CREATE TABLE dbo.AppUsers
(AppUserID INT,
 AppUserName NVARCHAR(128)
 );
 
CREATE TABLE dbo.AppFunctions
(AppFunctionID INT,
 AppFunctionName NVARCHAR(128)
 );
 
CREATE TABLE dbo.AuthorizationGroups
(AuthGroupID INT,
 AuthGroupName NVARCHAR(128)
 );
 
CREATE TABLE dbo.AuthorizationFunctions
(AuthFunctionID INT,
 AuthGroupID INT,
 );
 
CREATE TABLE dbo.UserAuthorizationGroups
(AuthGroupID INT,
 AppUserID INT
 );
 
INSERT AppUsers VALUES (1, 'George');
INSERT AppUsers VALUES (2, 'Elizabeth');
 
INSERT AuthorizationGroups VALUES (10, 'MaintainAccounts');
INSERT AuthorizationGroups VALUES (20, 'Billing');
 
INSERT UserAuthorizationGroups VALUES (10, 1)
INSERT UserAuthorizationGroups VALUES (20, 1)
INSERT UserAuthorizationGroups VALUES (10, 2)
 
INSERT AppFunctions VALUES (100,'UpdateCustomerDetails');
INSERT AppFunctions VALUES (200,'RecalculateBalance');
INSERT AppFunctions VALUES (300,'RecalculateInterest');
 
INSERT AuthorizationFunctions VALUES (100, 10);
INSERT AuthorizationFunctions VALUES (200, 20);
INSERT AuthorizationFunctions VALUES (300, 20)
   
-- Selection to show user, group, and function
SELECT U.AppUserName, G.AuthGroupName, F.AppFunctionName 
FROM AppUsers U 
   JOIN UserAuthorizationGroups UG
        ON U.AppUserID = UG.AppUserID
   JOIN AuthorizationGroups G
        ON UG.AuthGroupID = G.AuthGroupID
   JOIN AuthorizationFunctions AF 
        ON g.AuthGroupID = AF.AuthGroupID
   JOIN AppFunctions F
        ON F.AppFunctionID = AF.AuthFunctionID
  
DROP TABLE dbo.AppUsers
DROP TABLE dbo.AppFunctions
DROP TABLE dbo.AuthorizationGroups
DROP TABLE dbo.AuthorizationFunctions
DROP TABLE dbo.UserAuthorizationGroups

Of course, these tables could all carry further details depending on how much control you are trying to define.  This is just a structure.  With this structure, you would query just to see if the user has rights to the function.  (You might not care about the group that gave him the permissions except when you are setting up or changing security.)

FWIW,
RLF