August 19, 2008 at 12:20 pm
Hi all,
My client would like to have the sp_setapprole implemented on the application, so the users can have full access to the database only when connected through the application. I am in the process of gathering knowledge about the application roles and how I can implement it.
If you any of you have experience with the sp_setapprole or sp_addapprole, please give me your suggestions on how I can start with this.
And, we use windows authentication to connect to the SQLserver database. I am not sure if it is possible to use the approle with it. Please let me know. THanks for your help.
August 19, 2008 at 1:15 pm
I haven't used approles so cannot give you any info on that
But to ensure users can access database only through the app, you can limit the permissions of the user that the app uses to connect to database.
Instead of allowing users to connect using their windows username, create a single windows user that the app can use to connect to database. Grant execute permissions on all stored procedures to this user. Use stored procedures to access database so that you don;t have to give explicit permissions to insert/update/delete on tables.
August 19, 2008 at 1:27 pm
avamin: That is how you limit an app to SProcs. To force user to only use the app you need something like AppRoles or Certificates.
These are two independent security options. You can do either or both.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 19, 2008 at 1:34 pm
Yes, my client is looking for providing access to the users only through the application and not at the database level..
I researched a bit and I was able to get some information about this. I will have to add an application role, grant permissions to the role, execute the sp_setapprole from the application code when the application starts.
But, if windows authentication is used, where do we specify the password for for the sp_setapprole? Will it be in the application code while executing sp_setapprole? or the password has to be given ahead?
THanks for your help.
August 19, 2008 at 1:40 pm
psangeetha: As I recall it, here is the procedure:
1) Create AppRole in the App DB. Remember the Role name and password.
2) Give the user Connect-only access to the DB:
a) Add the user or user-group as a Windows Login to the Server
b) Add them to the Database
c) Remove all rights except "CONNECT" in the database.
3) Write your app to connect to the Database using trusted connections (ie., the user's windows login).
4) Once connected write the App to execute the folloiwng:
EXEC EXEC sp_setapprole 'appRoleName', N'password';
substituting in your actual App Role name and password.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 19, 2008 at 1:49 pm
rbarry: To clarify, I suggested creating a single windows user that only the app will use to talk to database. Sorry if my earlier post was not clear.
August 19, 2008 at 2:18 pm
Rbarry, THanks for your reply.How can I grant connect only access to the user??
THank you
August 19, 2008 at 3:40 pm
Ah, this is one of the worst dialog sequences in SQL Server:
1) In SSMS, right-click on the user's name in the database and select properties
2) select the "Securables" pane, click the Add... button
3) check "specific objects" and click OK
4) Click "Object Types" then "Databases", then click "Browse.."
5) select your database and click OK
6) click OK again, to return to the "Database User" dialog, select your database which you just added. The user's explicit permissions should be displayed in the pane below.
7) uncheck everything except "Connect .. Grant".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 19, 2008 at 4:02 pm
avamin (8/19/2008)
rbarry: To clarify, I suggested creating a single windows user that only the app will use to talk to database. Sorry if my earlier post was not clear.
Wouldn't it be easier to create a SQL login that would only be used by the application, instead of a windows user? How do users log on as a different windows user? They would have to use something like "run as".
Create a SQL login and assign it the rights it needs. Have the application connect to SQL Server using that login. This way there's no connections between what the users can access via the application and what they can access without it.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 20, 2008 at 7:48 am
Thanks a lot, Rbarry.
One last doubt, I just found the syntax for creating app role is different.
CREATE APPLICATION ROLE application_role_name
WITH PASSWORD = 'password' [ , DEFAULT_SCHEMA = schema_name ]
Is this different from sp_addapprole??? Is this better?
Thank you
August 20, 2008 at 8:28 am
Alvin Ramard (8/19/2008)
avamin (8/19/2008)
rbarry: To clarify, I suggested creating a single windows user that only the app will use to talk to database. Sorry if my earlier post was not clear.Wouldn't it be easier to create a SQL login that would only be used by the application, instead of a windows user? How do users log on as a different windows user? They would have to use something like "run as".
Create a SQL login and assign it the rights it needs. Have the application connect to SQL Server using that login. This way there's no connections between what the users can access via the application and what they can access without it.
Though it's possible for the app to impersonate another windows user, I agree, SQL Login is easier and is also easier to implement than using approle.
August 20, 2008 at 9:11 am
Yeah, sorry, I forgot: "CREATE APPLICATION ROLE..." is the preferred way to add an AppRole in SQL 2005.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 20, 2008 at 9:17 am
avamin (8/20/2008)
Though it's possible for the app to impersonate another windows user, I agree, SQL Login is easier and is also easier to implement than using approle.
Well, it's not much easier, if at all. And it certainly is not nearly as good, because AppRoles make it possible to easily combine accessibility checks for both the Windows User and for the Application's AppRole Login.
Even more secure in SQL Server 2005 is the use of Application/Login Certificates.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 20, 2008 at 1:10 pm
We are using approles for all updates, inserts, and deletes. The issues we run into involved using things like a gridview. We haven't found a way to set the approle for this and have been forced to grant read privileges to the login. Has anyone else come across this (or found a way around it?)
August 21, 2008 at 8:17 am
Well, it's not much easier, if at all. And it certainly is not nearly as good, because AppRoles make it possible to easily combine accessibility checks for both the Windows User and for the Application's AppRole Login.
Even more secure in SQL Server 2005 is the use of Application/Login Certificates.[/quote]
rbarry,
I guess using AppRole depends on the application, we didn't want to create windows login for every user as it was not an intranet application so went with the SQL Login.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply