Changing login permissions

I am trying to set up a secure system and would like some advice on how to do it as it is a bit tricky.

The underlying security is Windows Authentication but I need to set various permission levels in the application.
What I want to do is to allow users read-only access to a users table. Once they are validated and their permission level is determined, then I want them to be assigned to the role that is set for their permission level.

I have looked at Application Roles but when I try to set the role up using the sp_setapprole then I get network errors (odd). There does not seem to be an SP which assigns a user to a role.

So I do I go about this?

Many thanks for your help.
Ian Logan

You can add a user to a role using sp_addrolemember.
The errors that you get for sp_setapprole are strange. Do you also get them when you're connected locally? Can you post these errors?

Thanks
Laurentiu|||

Can you please clarify how users are validated in your application? Is it handled by your aplication or SQLServer? If this is the latter, then you can assign permissions in advance to windows groups and your windows users who are the members of those groups will get those permissions upon logon to SQLserver. If this is former (i.e. you have applicatoin users, rather than windows or SQLServer principals) then you can
1) provide set of wrappers (such as table-valued functions) to access your data, which take application user name as an argument and have security logic inside; or
2) you can create a set of SQLServer users with necessary permission granted to them, map your application users to them using "EXECUTE AS user" feature on the TSQL procedure level or inside your application.

You can use sp_setapprole for 2, but I" believe it can be fully replaced by "EXECUTE AS"
sp_addrolemember is also available to add users to db roles, but itis not recommended to call it during your application on permanent basis, both due to security and reformance reasons.

|||

I am passing the Role Name and the Password to sp_setapprole. The role does exist in the database and it has permissions set for the various objects that I want to use.

I seem to be able to run sp_setapprole from the application, but as soon as I try to access the database again then the following error arises:

"System.Data.SqlClient.SqlException: General Network Error. Check your network documentation"

Note that I am using the Microsoft.Practices.EnterpriseLibrary.Data application block for all database interfacing. The application is obviously .NET and it is SQL 2000.

Note also that the code I am using works fine if I do not use sp_setapprole, i.e. the user has Windows Authentication and Public rights. (I have yet to REVOKE ALL FROM PUBLIC).

Many Thanks
Ian

Code:

Public Shared Function SetDBRole() As String

' Create the Database object, using the default database service. The

' default database service is determined through configuration.

Dim db As Database = DatabaseFactory.CreateDatabase()

Dim sqlCommand As String = "sp_setapprole"

Dim dbCommandWrapper As DBCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand)

' Add paramters

' Input parameters can specify the input value

dbCommandWrapper.AddInParameter("@.RoleName", DbType.String, "MyAppRole")

dbCommandWrapper.AddInParameter("@.Password", DbType.String, "MyPassword")

db.ExecuteNonQuery(dbCommandWrapper)

End Function

|||Ruslan

The users are to be validated by the application. However Windows Authentication is being used to allow them access to SQL Server in the first place. The users must logon separately to the application using a different user name and password from their Windows logon.

The application requires three tiers of user access (data entry, supervisor, etc) and the users of the application, plus their access level, are set up within the application.

The plan would be that then a new user is created then sp_addrolemember would be used to add then to the role. Then when they log in the users table would be looked up to determine their access level (role) and then sp_setapprole would be run to set their permissions.
Initially users would only have read-only access to the users table and no access to anything else. Actually, to be pedantic, nobody will have access to tables as I am using stored procedures for data access.

As for EXECUTE AS, I am using SQL 2000 and have not come across this. Is is 2005?

Kind Regards
Ian|||Ian, I don't understand why you both add users to a role and also calling sp_setapprole. I expect you could just use roles, have permissions assigned to them as appropriate, and then just have the users added to the appropriate role. The users would not be granted any permission directly, they would get their permissions from the role that they belong to. Wouldn't this address your security requirements?

Thanks
Laurentiu

PS: Yes, EXECUTE AS is a SQL 2005 feature.|||Laurentiu

Users would be added to a role when they were created within the application. However when they log in to the application then how is their role activated? I thought that you had to use sp_setapprole to do this.

As you may note from other parts to this thread, I am having problems using sp_setapprole. When I run it from SQL Query Analyser ( sp_setapprole "MyRole", "mypassword" ) then I get Msg 2762 saying that it has been invoked incorrectly. When I run it from .NET then I get the odd network error message as described in this thread.

I reckon that if I can get sp_setapprole to work then I should be there...

Kind Regards
Ian

|||

I've tried sp_setapprole on SQL 2000, and I would get error 2762 whenever I would try to set the approle again after it was already set. You can verify whether the approle is already set by executing:

select user_name()

This will return the approle name if it is already set.

If a SQL user is a member of a SQL database role, the role membership takes effect when the user connects to the database.

I still don't understand how authentication and authorization is handled by your app. You mentioned that:

"The users are to be validated by the application. However Windows Authentication is being used to allow them access to SQL Server in the first place. The users must logon separately to the application using a different user name and password from their Windows logon."

Does the app connect to SQL Server using the user's Windows credentials or by using some other Windows credentials? How are the users that you are adding to roles related to the users that connect to the app? Could you explain the steps that are involved when a person that uses the app logs in to it?

Thanks
Laurentiu

|||Laurentiu

I have discovered an article which explains some of the problems with sp_setapprole. It is 229564, and basically the connection pooling has to be disabled for sp_setapprole. Also, another post indicated that running sp_setapprole in SQL Query does not work, I think it was again due to connection issues. This looks like there may be a problem because connection pooling is useful and switching it off is a backward step.

This instance of SQL Server has only Windows Authentication.

1. The app connects using Windows Authentication.
2. On clicking OK on the app's Login, a user will be validated against a Users table in the database. This table will also contain their access level (1-3).
3. They will then be allocated one of three roles, based on their access level.

The user name and password in this table will be quite independent of their Windows user name and password as we want to make this as secure as possible. (We also need to encrypt at least one table as well - but that is another story).

The app will control the setting up of the users for the Users table. Only a superuser of the app will be able to do this. They will enter the user name, password and access level, and these will be stored in the Users table.

Kind regards
Ian
|||

I think I begin to understand. When you say roles, you really mean application roles, right, not database roles? You allocate a user to a role by calling sp_setapprole. I thought you were using both database roles and application roles.

I'll try to see if I can find anything else related to the limitations of using sp_setapprole with connection pooling.

Thanks
Laurentiu

|||Laurentiu

Any further thoughts on the connection pooling issue?

Kind Regards
Ian|||I inquired and there is no workaround on SQL Server 2000 to make sp_setapprole work with connection pooling.

Thanks
Laurentiu|||OK, many thanks. I will raise the connection pooling issue in another forum as I have some further questions about it.

Kind Regards
Ian Logan