On Jun 28, 7:02=A0am, adjo <adgn...@[EMAIL PROTECTED]
> wrote:
> On 27 jun, 17:15, adjo <adgn...@[EMAIL PROTECTED]
> wrote:
>
>
>
> > I am working on an app with an Access2002 frontend and Sql2005
> > backend. I have to use integrated security. I want to prevent my users
> > from altering data in another way than via the frontend.
> > It looks to me that the mechanism to do it is the Sqlserver
> > sp_setapprole procedure. Works fine when programming directly to
> > Sqlserver, and also een Access Data Project at first sight seems to
> > work as it should via the call to the sp_setapprole proc.
> > But for a number of reasons I would like to use a normal MDB as
> > frontend with Dao3.6 as data access method. This works fine normally
> > when I use SqlServer as backend, but now when I want to use
> > Intergrated Security the necessary sp_setapprole won't behave as
> > expected:
> > 1) Excuting it via a passthrough query while using a DSN seems to
> > work, but suddenly the changes in tablepriviliges (because of
> > activating the role) can be gone. Seems like the mechanism is
> > unstable.
> > 2) Using a DSN less connectionstring has the result that the sql user
> > for the connection changes in the rolename (as it should be) but table
> > privs don't change at all.
> > I read about the '3 connections Access uses' when connecting to
> > Sqlserver ('How to use Application roles with Access projects and SQL
> > Server 2000 Desktop Edition'). Maybe this has got to do something with
> > the strange behaviour after executing sp_setapprole.
> > Is there some with experience with this problem. And hopelfully some
> > tips, because I desperatly need the Int.Security + an Mdb frontend.
>
> By the way: is there another way to solve the 'get to the data via
> another way than the app' problem then using the sp_setapprol
> mechanism? 90% solutions are welcome as well.....
One can use "normal" roles and logins and hide and encrypt the
usernames and passwords in code and compile applications to mdes or
ades or accdes.
This is as safe as the coding skills of the developer are good.
In this way users have no login or permissions of their own, so when
they create another adp, and the connection dialog opens they see no
servers. They can't login to the server, so they can't examines
usernames and passwords there.
Of course, such logins and permissions can be associated with
application roles, but if they are hidden and unknown, what's the
point of going that extra step?
Because Access opens multiple new connections erratically and
unpredictably, and because each of those connections must be
explicitly associated with an application role (where application
roles are used) this has been my practice. Actually it's not much
different than how we might do asp, where we hide connection
parameters in a special secure folder, or asp.net, where the
application hides them for us.
My experience in trying to use application roles with Access, and I've
done this on a very extensive project, is that this might double
development time. This is because Access may use connections one way
on Monday, but a slightly different way on Tuesday, depending of
course on what you had for breakfast. I know of no way to ensure that
Access will use application roles properly and consistently for pull-
downs and I resort to creating lists (strings) for them, based on very
basic ADO calls in code.
In my opinion this problem is likely to have been the determining
factor in MS abandoning ADPs. It makes ADPs potentially explosively
dangerous and MS had and has no credible solution.
There was a little girl,
Who had a little curl,
Right in the middle of her forehead.
When she was good,
She was very, very good,
But when she was bad, she had MS.
|
