January 25, 2002 at 10:59 am
I will be setting up a new SQL Server 2000 soon and I plan on using an application role. I heard something one time about if you are planning on using cross-database queries, the external database needs to have a guest role. At the same time, I have been told to not have a guest role setup because of security concerns.
Can someone shed some light on this for me?
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
January 25, 2002 at 11:10 am
I think your app roles will need to cross databases and be setup for each databare. Not sure if you can have one app role that maps to others in other databases. You might need to connect to each db and run the sp_set_approle in each.
Interesting idea.
Steve Jones
January 25, 2002 at 11:13 am
BTW, I don't really use App roles. Do you have a good reason for not just creating a role and assigning users to it?
Steve Jones
January 25, 2002 at 12:16 pm
I don't have any real reason for doing this. I am definitely not set on doing this. Are there any benefits to using one over the other?
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
January 25, 2002 at 12:40 pm
App roles are a solution looking for a problem I think. The key feature and/or bug is that once you activate the app role, any permissions the user would have gotten via other roles/logins is stopped. Like Steve I just use standard roles. Be interested to hear from anyone who has used app roles in a create way:-)
Andy
January 25, 2002 at 3:40 pm
I am using app roles on bank system.
If you are using “standard role”, users could use isql, excel, odbc etc. to change or see the data using the stored procedures in any way.
I use app role in my asp login page.
I have a table APP_ROLE with one row where I keep app_role_name and password_role encrypted.
I had created a "standard role" that can see this table, nothing more, and a “application role” to see and exec all objects.
The asp page first consults the APP_ROLE table, then decrypts the password and set the app role.
In the other database I had to create a “guest” user. But you have also to create a "standard role" and deny permission on all objects. You do not forget add the users to the other standard role.
In this way my application is the only way that can see the datas.
Usually you give permission only to stored procedures and “standard role” is enough but if you want exclusivity use application role.
Sorry for my English.
Carlos.
January 25, 2002 at 4:03 pm
Makes some sense, but what prevents me from connecting via isql, reading the table, decrypting the pwd (too many tools on the internet can do this), setting the app role and seeing everything?
A user who desires to use isql can probably accomplish the above. If you limit access to the ASP file with a firewall, you can also limit access to SQL.
Still not sure this is worth it and agree with Andy.
Steve Jones
January 25, 2002 at 5:47 pm
Its one of those things I want to like, just hasn't clicked yet. Seems I remember a good article in SQL Mag about it, will have to dig around for it. Have heard...though not tested...that you can have problems with it with some controls that open a second connection. The biggest pain about it (and SQL logins too to be fair) is securing the password/changing the password in the client.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply