March 27, 2006 at 2:46 pm
We're thinking of using application roles, but I don't quite get how to give it permissions. We currently have a sql login that's being used & abused. It has dbo access, and I want to use an app role that also has dbo access so that it can read, write and create work tables and run Stored Procedures via an application . I can see how to grant access to specific tables and SPs, but I don't want to have to manually manage specific objects. Is there a way to give the application role permission to the database so that it has rights to any tables & SPs in the future ?
What am I missing ?
March 30, 2006 at 8:00 am
This was removed by the editor as SPAM
March 30, 2006 at 8:09 am
Tables, yes. You can make the application role a member of the db_datareader and db_datawriter fixed database roles. Stored procedures and functions, no. Everything one of those is created, you'll have to grant the appropriate rights to the application role. However, if you bundle such rights with the object creation script, it should be more manageable.
K. Brian Kelley
@kbriankelley
March 30, 2006 at 10:08 am
I was thinking of creating 3 different App Roles. 1 for read access to be added to db_datareader, 1 for read/write to be added to db_datawriter, and 1 added to db_owner for applications using SPs and creating temp work tables.
March 30, 2006 at 10:26 am
If you're going down that route... follow the Principle of Least Privilege. Only give the rights needed, nothing more.
Don't use db_datareader and db_datawriter. Instead, assign the appropriate permissions. Keep in mind that db_datareader and db_datawriter have implicit access to all tables and views, including the system ones. You can script out the user objects and build permissions against them.
Stay away from db_owner. That's TOO much. The db_owner role is going to allow the application role to change security, to create "permanent objects" and things of that sort. If you need to create temp tables, as SQL Server defines them (# and ##), then you don't need db_owner. Temp tables get created in tempdb and such actions should already be possible. If, however, you mean actual table objects in the database, you can assign CREATE TABLE explicitly, meaning the app role can't create stored procedures, views, etc. However, keep in mind that a CREATE TABLE permission also means the ability to ALTER TABLE.
K. Brian Kelley
@kbriankelley
March 30, 2006 at 12:48 pm
Is there a better route ? I'm trying to figure out the best way to fix a bad situation. Currently there's a widely known sql login that has db_owner permissions. I want to get rid of it without breaking the current production applications that use it. I was thinking I could phase it out by using the App Roles in the various applications. If the App Roles only have db_datawriter permissions, then I'll have to get in the habit of granting 'exec' permissions to public or the new App Roles for each new Stored Proc ?
March 30, 2006 at 12:57 pm
Well, keep in mind that the app role must be set by the calling application (through the sp_setapprole stored procedure), if it's not going to do that, there's no point creating an app role.
Probably the best approach is to identify what each application needs, create an appropriate user account with the minimal set of permissions needed for the app to run, and switch the app over. Do that one at a time. That's probably the safest way to get rid of it, although it may take a while.
K. Brian Kelley
@kbriankelley
March 30, 2006 at 1:28 pm
Yes, our lead developer is testing a few applications by having them invoke sp_setapprole, and it looks good so far. We're planning the "one at a time" method as we try to identify everything that needs modifying.
March 30, 2006 at 3:07 pm
So, for the Stored Procs, I should grant 'exec' permissions just to the App Role, not public ?
By the way, thanks very much for your help !
March 31, 2006 at 9:00 am
Whenever possible, avoid granting permission to public. If you grant any logins access to a given database, the public permissions would automatically apply. I believe Andy Warren has an article on the site that discusses the issue in more detail.
K. Brian Kelley
@kbriankelley
April 4, 2006 at 3:07 pm
Now we find that using Application Roles means you can't use Connection Pooling.
From SQL_Performance: "While SQL Server application roles are handy, they can also negatively affect your application's performance. The reason for this is that a connection to SQL Server using an application role cannot take advantage of connection pooling. In effect, connection pooling is turned off for any connections using application roles. If your application will be making many connections to SQL Server, avoid applications roles for your application's connections. "
Is there a better solution ?
April 4, 2006 at 9:47 pm
This is one of the issues with application roles in SQL Server 2000. Once you set an application role for a connection, it cannot be undone. Since connection pooling relies on being able to re-use connections, there is an issue. You can use connection pooling if you check to see if the app role is set (remember, you basically lose the login and user context, so with that, you can detect whether you're using an app role or not by querying for USER_NAME() and seeing if you return the app role name). If you detect that the app role is already set, that don't re-execute the stored procedure.
With SQL Server 2005 you have the option of executing sp_unsetapprole, which will return the connection back to its original context.
K. Brian Kelley
@kbriankelley
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply