On 28 jun, 14:56, lyle fairfield <lyle.fairfi...@[EMAIL PROTECTED]
> wrote:
> 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 =A0can 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.- Tekst uit oorspronkelijk bericht niet
w=
eergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -
Thanks. This is the kind of reaction I need. I already expected that
the 'sp_setapprole' is not realy useful in my situation because Access
is not suited as a stable clientenvironment. Your experiences save me
the time and frustration of keep on trying. It's one of the very few
moments I get stuck with Access as a clienttool.
> 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.
Hm. With a lot of users this is not realy attractive. Think I'd rather
stick with one useraccount and hiding the accountdata as good as
possible. This works for almost 20 years now without a problem. But in
the application I'm talking about it would have been so nice to be
able to use int.security so that users have their own account. Would
also have been easier for our Dba in case performance or locking
issues should occur.
|
