security through SQL

[Guest]

I have posted this in the wrong section. This is a repost

Hello,
first of all, i hope to be able to explain what's on my mind...
I have a db (Access2000) for which I have builded a specific workgroup file
(wrkgrp#1), with different groups of users and different permission to each
groups. Admin user and Users group haven't any kind of permissions. It works
fine.
This database runs on a computer that uses as *default* workgroup file (i.e.
the one that take into action whenever you open Access) another workgroup
file (wrkgrp#2), different from the one I created (wrkgrp#1) and different
from the system.mdw too. To open the database users use shourtcuts which link
to the workgroup file I have created (wrkgrp#1).
Now...
Sometimes I need to build small patches to manage some updating/deleting
action on this database. These patches are builded in Access2000 (i.e. .mdb
files). I would like to send them through email; when users open the patches
the code will take care of anything. When I build these patches I'm on the
system.mdw.
Mostly of the code is just plain SQL. My problem is that when user try to
open the patches they will be prompted to logon with userID an password for
the wrkgrp#2. And they do. In this way they will NOT be able to open the
patches and they will NOT be able to make any changes to the database. Right?
Things will be easier if they wiil restore system.mdw as the *default*
workgroup file. If so, they can open the patches but CANNOT make any changes
to the database. Correct?
Besides if in the patches I code first the ADD USERS... SQL statement to
add users from my wrkgrp#1 to the system.mdw, this will NOT work, since at
this time user is logged as Admin.
Question?
How can I manage this situation? (I colud build the patches linking to the
wrkgrp#1 (which I have). But there is some other way to go? What if I would
like to stay on system.mdw for the patches?
I mean.... there is a way to grant access to a secured database through SQL
(I know I can use ADO...but too much code to write for the task)? There is a
way to authenticate and/or authorize users? i was thinking to some kind of
string like the one that is commonly used in the target field of a shourtcut
but that could be used in SQL statement.
I hope to have said something meaningful.
Thanks for the attention and ...sorry for the headache.
Rocco

 

[Joan Wild]

first of all, i hope to be able to explain what's on my mind...
I have a db (Access2000) for which I have builded a specific
workgroup file (wrkgrp#1), It works fine.
This database runs on a computer that uses as *default* workgroup
file (wrkgrp#2), different from the one I created
(wrkgrp#1) and different from the system.mdw too. To open the
database users use shourtcuts which link to the workgroup file I have
created (wrkgrp#1).

Sometimes I need to build small patches to manage some
updating/deleting action on this database. When I build these patches I'm

on the system.mdw.

Mostly of the code is just plain SQL. My problem is that when user
try to open the patches they will be prompted to logon with userID an
password for the wrkgrp#2. And they do. In this way they will NOT be
able to open the patches and they will NOT be able to make any
changes to the database. Right?

Wrong. You are using system.mdw to build your patch. That has the
universal Users Group in it. This group should have permission on whatever
you put in your patch. Users in wrkgrp#2 are members of the Users Group.
Therefore they should have no problem running your patch.

However, I wonder how a patch built with system.mdw is able to run against
your mdb which is secured against wrkgrp#1, presuming you are modifying data
in that mdb.

Things will be easier if they wiil

restore system.mdw as the *default* workgroup file. If so, they can
open the patches but CANNOT make any changes to the database. Correct?

Well if they are logging in using wrkgrp#2, then they shouldn't be able to
make any changes.

 

[Guest]

on the system.mdw.


Wrong. You are using system.mdw to build your patch. That has the
universal Users Group in it. This group should have permission on whatever
you put in your patch. Users in wrkgrp#2 are members of the Users Group.
Therefore they should have no problem running your patch.

Actually, in the wrkgrp#1 the user group doesn't have any permission to any
object. I have found this is an important step in Access security. Also in
the wrkgrp#2, permissions have been removed for the users group. It has been
created the "data enters" group with assigned permissions. Users in wrkgrp#2
are members of "data enters" group

However, I wonder how a patch built with system.mdw is able to run against
your mdb which is secured against wrkgrp#1, presuming you are modifying data
in that mdb.

Exactly, I would like to know if this can be done granted somewhat users
permissions to do that. I cannot do it in code because the patch will NOT
open at all...so no code running...

Things will be easier if they wiil

Well if they are logging in using wrkgrp#2, then they shouldn't be able to
make any changes.

But at this time, since the patch will open, I can code in order to connect
my database with the right permission (i can use my ID and password - i act
like admin -). Changes will take action in this way. Right?

Thanks again
Rocco

 

[Joan Wild]

Actually, in the wrkgrp#1 the user group doesn't have any permission
to any object. I have found this is an important step in Access
security. Also in the wrkgrp#2, permissions have been removed for the
users group. It has been created the "data enters" group with
assigned permissions. Users in wrkgrp#2 are members of "data enters"
group

But you said you created the patch mdb using system.mdw. Permissions are
stored in the mdb, not the mdw. The Users Group has permissions in your
patch mdb. Every user in every mdw is a member of the Users Group.
Therefore they will have permissions in your patch mdb - the permissions are
in the mdb.

But at this time, since the patch will open, I can code in order to
connect my database with the right permission (i can use my ID and
password - i act like admin -). Changes will take action in this way.
Right?

Yes.

 

[Guest]

Sure! Thank you...
Is it possible to manage security issue through SQL instead of ADO...i think
it will be easier. Although I write ADo code, any time I can skip in favor of
SQl i'm happy to do it!

 

[Joan Wild]

Sure! Thank you...
Is it possible to manage security issue through SQL instead of
ADO...i think it will be easier. Although I write ADo code, any time
I can skip in favor of SQl i'm happy to do it!

No

 

[Guest]

how much can hurt just two letters...
thanks, anyway