Access rights to two mssql dbs via password protected role

I dont know how to arrange situation when application enduser needs to access data in two databases of mssql server concurently in those circumstances that access rights to the data should be restricted by password protected role (whose password is not known to the end user).

Detailed description of problem:

So far there was an application, that manipulated its data, saved in mssql server's database. End user authenticates to application by his (mssql server's) login name and password. The application authenticates the user by connecting to the database with the given name/password credentials, and then the application sets application role with hardcoded name/password. Thus application role sets the access rights for consequent end user's requests, delivered via application to the database server.

The goal is that end user cannot manipulate application database data when connects to the database by other means (e. g. via SQL server Manager), because he does not know the application role's password.

Now suppose that there are two applications (A1, A2), both using the same model for access restrictions. Each of them has its own database (A1DB, A2DB) and its own application role (A1R residing in A1DB, A2R residing in A2DB). End user (login) X can manipulate A1DB data when connects via A1, and A2DB data when connects via A2, and NO data when connects by other means.

Finally suppose that some subset of A2 data (let's say one table) is useful to see also via A1 application. There is no problem to add to A1DB view, that shows data from A2DB table together with A1DB tables. But when the user is connected via A1, he cannot see the data, because query on A1 view fails (user has not access rights on A2 data).

The access rights for A1 enduser cannot be set by no means i know because:

1) I cannot set the rights via public (guest) access because in that case they will be accessible to any users connected by any third party products, which is supposed to be security hole.

2) I cannot set the rights via dbuser or dbrole privileges, because they will not work when connected via A1 application (setting the app role suppresses the db privileges)

3) I cannot set the rights via application role because two application roles cannot be set concurrently.

4) I cannot abandon using application roles mechanism and use database roles mechanism, because db roles cannot be protected by independent password (not known to the enduser).

Please can anybody review my problem and either find the mistake in my approach, or propose other solution? So far I suppose the problem is my ignorance, because I am not great mssql expert.

I hope I can explain why approles will not work on your particular scenario. Approles are principals defined within a database, and they have absolutely no presence on the server or on other databases. Even if the 2 approles are named the same and have the same passwords, because they are defined in different databases they are not the same principals. The fact that after establishing an approle it is possible to go to a different DB as “guest” account (given that guest access is allowed) is a consequence of the previous SQL Server security model and that we have to maintain it for backwards compatibility reasons.

One potential solution for your problem would be to use the new impersonation mechanisms in SQL Server 2005. For example, you can create a module (i.e. a SP or a multistatement function) marked with EXECUTE AS (disable the login mapped to this user to prevent direct connections) with enough permission to allow that the impersonated context on A1DB can access the data on A2DB. Because you will be accessing cross-database data, you will need to either sign your module (recommended) or set the TRUSTWORTHY bit option ON on A1DB (source DB).

I would recommend the following references to understand this new impersonation model better:

· EXECUTE AS Clause http://msdn2.microsoft.com/en-us/library/ms188354.aspx

· Using EXECUTE AS in Modules http://msdn2.microsoft.com/en-us/library/ms178106.aspx

· Understanding Context Switching http://msdn2.microsoft.com/en-us/library/ms191296.aspx

· Understanding Execution Context http://msdn2.microsoft.com/en-us/library/ms187096.aspx

I also recommend reading Laurentiu’s blog as well as my own blog

· Laurentiu Cristofor’s blog http://blogs.msdn.com/lcris/

· Raul Garcia’s blog http://blogs.msdn.com/raulga/

I hope this information will be useful. Let us know if you have further questions.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks to Mr. Garcia

Thank you very much for your answer. It seems you have solved my problem. In fact the focus of my problem was not in (not)understanding of role mechanism in mssql server. The problem was, that i was not able to convince my managers, that the concept of roles in mssql server does not allow simple setting of access rigthts to objects in two databases via password protected roles. They did not believed me, because were used to platforms, on which it is not problem at all. After reviewing your explanation it seems, that my managers will give me more time for solving our inter-application-communication problem via more complicated way - the new impersonating mechanism.