ADP, Application Role, and objects

[JohnSmith]

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q308312

The above link is to an atricle on how to implement SQL Server Application
roles with and access project. In a nutshell it goes like this:
1. Create an Application role on the SQL Server DB.
2. After you connect with your ADP, fire a bit of code to set the
application role for your session. This will set the users permissions
within the context of the application but will prevent the user from using
third party tools to view the data on the same database.

<Excerpt From Document>
The main complication when you are using application roles in Access
projects is that Access uses three connections to SQL Server to handle
various tasks. Ideally, to apply an application role to the whole project,
you would have to execute sp_setapprole in the context of all three
connections. The objects handled by each connection are as follows:

1. Used for determining which objects appear in the Database window and for
miscellaneous database administrative tasks.
2. Used for opening tables, views, stored procedures, functions, and the
record sources for forms and subreports (but not for the main report
itself).
3. Used for obtaining the record sources for combo boxes, list boxes, and
reports.

Although connections #2 and #3 can be accessed fairly easily, <Not shouting,
just highlighting>THERE IS NO METHOD AVAILABLE FOR EXECUTING THE STORED
PROCEDURE IN THE CONTEXT OF CONNECTION #1. FORTUNATELY, THIS CONNECTION IS
THE LEAST IMPORTANT OF THE THREE AND IS EASILY WORKED AROUND BY CONSTRUCTING
YOUR OWN USER INTERFACE (FOR EXAMPLE, A SWITCHBOARD-TYPE FORM) FOR HANDLING
DATABASE OBJECTS INSTEAD OF RELYING ON THE BUILT-IN DATABASE WINDOW.
<End Excerpt>

So given this, here is my issue:
I have a form that is based on an object in the database. The record source
is set to the name of that object.
Scenario 1 - If I explicitly grant permissions on that object to the user
in the DB (no Application Role) everything works as it should when I log in
via the ADP.
Scenario 2 - If I explicitly grant permissions on that object to the user
in the DB and then set the Application Role (modifying those permissions)
everything works as it should when I log in via the ADP.
Scenario 3 - If I do NOT grant explicit permissions, but depend upon the
Application Role, the form does not find the object when it opens. I get the
following error:
Run-Time error '2850':
The record source '<source_name>' specified on this form or report does
not exist.

I know that I, in fact, have permissions on it because I can reference it
programatically in another area of the ADP. Nearest I can Figure is that
when I use only the applicating role, the form cannot find it because it is
not 'in the database window'. The name of the object is being set somewhere
on the ADP side.

Therin lies my problem: After banging my head for a couple of days on this
I am totally stuck on how to get the form to recognize the existance of the
underlying object. The easy workaround simply is not coming to me.

How do I get the ADP to recognize the objects upon which it has permissions
in the SQL Server DB?

Cheers and thanks,

Ian.

 

[Sylvain Lafontaine]

The use of Application Role with ADP is strongly discouraged. See for
example:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q229564

In your case, you may try to prefix the name of the procedure with dbo. or
use an alternate syntaxe, like (I don't remember exactly):

EXEC MyProc
or:
Exec dbo.MyProc

If you make a search on Google for this newsgroup, you will find some
previous post about this topic. See for example:

http://groups-beta.google.com/group...a4?q=application+role&rnum=3#b13d10e0a2c7bda4

http://groups-beta.google.com/group...ation+role&qt_g=1&searchnow=Search+this+group

S. L.

 

[JohnSmith]

Thanks Sylvain, I really appreciate the reply.

I had been unsuccessfully googling for a couple days. Never thought to
google a group...Learn something new everyday.

Let me run this by you. ..just for fun.
After I posed the question yesterday, I got to thinking about this a little
differently. Rather than being hung up on the front end (Access) I started
thinking about it from the database side The data in my forms are being
sourced from stored procedures. By granting execute permisssion of the SPs
to the individual user, but suppressing permissions on the underlying
objects, it allows the Access Front end to recognize the datasources when it
connects. The application role then grants the required privileges in the
context of the applicaton and everything works great.

If the user were to connect directly to SQL Server he would still see the
SPs but if he were to attempt execution he gets a polite message back
telling him to mind his own buisiness ;-) (I trap the SQL Error and replace
it with one of my own). The SPs are encrypted, of course.

FWIW - The database architecture is really my baby. I am using Access to
prototype a front end because I once knew a bit of VB and it was supposed to
be easy. I do not know what the final front end will be written in but I
will take the info that you pointed me to into account.

Cheers,

Ian.

 

[Sylvain Lafontaine]

The problem with ADP, Role and Stored Procedures is that they work great for
reading or extracting data from the database but fails miserabily when it
come to write them back to the database; because with ADP you cannot control
this part with bound form. You cannot tell Access to use a specific stored
procedure to update the modified data (ever you can for resyncing the
operation). Obviously, this is a very severe limitation and, unless you are
using unbound forms, limits considerably the usefullness of Application
Roles under ADP or Access.

One possible solution that I have never time to test would be to use instead
a View with an INSTEAD OF trigger; because you can control the update
operation from inside the trigger. However, I don't know if you can call an
operation role from this trigger or if Access won't try to update directly
the tables behind the views.

The best solution is to forget about Access and to go with the .NET
Framework, where these problems have been solved. Using Access - either
ADP, DAP or linked ODBC tables - for prototyping a database is easy but only
for simple cases; because with it you cannot never control how the updating
of the datas is done with bound forms. Of course, you can try with unbound
forms but then, you will loose a good part of the simplicity of Access.

S. L.

 

[Vadim Rapp]

J> Scenario 3 - If I do NOT grant explicit permissions, but depend upon
J> the Application Role, the form does not find the object when it opens. I
J> get the following error:
J> Run-Time error '2850':
J> The record source '<source_name>' specified on this form or report
J> does not exist.

I tried the following:

1. created table1 with permissions granted to userA
2. connected as UserA, created a form bound to table1.
3. created another form Form2 with OnOpen event running
currentproject.connection.execute 'sp_setapprole 'RoleA','secretpass' .
Saved the form.
4. revoked permissions on table1 from UserA; granted them to RoleA
5. tried to open form1, it failed (as expected).
5. opened Form2, then closed it


Then I opened Form1, and it showed the records in the table correctly.

Vadim

 

[Sylvain Lafontaine]

Yes, but the problem with this is that now the user can do everything he
want with the table1 from inside the ADP.

Used in this context and unless I'm missing something, I don't see any
difference between an Application Role and the use of any other User
Account written directly in the connection string if we are applying the AR
directly to the current connection. We cannot even bring the possibility of
an additional level of security because the password for the AR will go down
directly over the wire; exactly the same as for the connection string.

But it's possible that I'm missing something here.

S. L.

 

[Vadim Rapp]

Hello Sylvain:
You wrote in conference microsoft.public.access.adp.sqlserver on Sun, 10
Apr 2005 02:00:28 -0400:

SL> Yes, but the problem with this is that now the user can do everything
SL> he want with the table1 from inside the ADP.

If the ADP is actually an ADE, the user won't be able to do anything.

SL> Used in this context and unless I'm missing something, I don't see any
SL> difference between an Application Role and the use of any other User
SL> Account written directly in the connection string if we are applying
SL> the AR directly to the current connection.

It makes possible to use NT authentication - the user connects with his own
login, appears in EM as himself, etc. At the same time, he is permitted to
work with the data only through our ADE; if tomorrow he brings his own
database application, or figures to open query designer in excel, it won't
help.


Vadim