User Level Security - Many Questions about FE/BE & no login

[Ann in CA]

Hello,
Please let me preface this by saying that I have read many things on user
level security, especially Joan and Lynne's sites, and while they are
extremely useful, I still haven't been able to get ULS to work. So I
apologize if I'm asking questions that have been answered time and time
again--maybe I just don't get it. =)

I do not want the user to have to log in. I am the only one who needs access
to the backend. I am fine with having one single users group, who can modify
data only through forms in the front-end file.

I assume I need to take the following steps to secure. Am I missing
something and/or what is the correct order?
1. Create a new workgroup--where should this be stored? On the server where
the database backend is?
2. In new workgroup, create new user who will "own" the system objects. Log
on as this user.
3. Open unsecure database backend.
4. Create new database and import tables. Remove Admin user from the Admins
group.
5. Start ULS, choosing Modify the current workgroup info file. Secure all
objects.
6. Do not choose any optional groups.
7. Give the users group permissions--here I don't know. Do I give them no
permissions in the BE file? I want them to only modify via forms in the _FE
file.
8. Create no new users--or should I put in the NT Login of everyone who can
have access? Remember, I don't want them to have to login.
9. After recording all the info, rejoin system.mdw. Create a shortcut to
the backend which I will use anytime later to make changes to the structure?

Then, I think I would secure the frontend following the same steps (but
perhaps changing the user group permissions?) Is there anything I have to
ship the users along with the next version of the frontend?

I think I have tried this whole process two dozen times and had zero
success. And, we're not upgrading to 2007 anytime soon but security has
suddenly become something we are focused on, so all the pressure is on me.

Thanks in advance for any help anyone might be able to offer.

 

[Joan Wild]

I do not want the user to have to log in. I am the only one who needs access
to the backend. I am fine with having one single users group, who can modify
data only through forms in the front-end file.

I assume I need to take the following steps to secure. Am I missing
something and/or what is the correct order?
1. Create a new workgroup--where should this be stored? On the server where
the database backend is?

You can put this where ever you like. It won't be used by your users, so I'd be inclined to keep it on your PC (with your own backup of it).

2. In new workgroup, create new user who will "own" the system objects. Log
on as this user.
3. Open unsecure database backend.
4. Create new database and import tables. Remove Admin user from the Admins
group.
5. Start ULS, choosing Modify the current workgroup info file. Secure all
objects.
6. Do not choose any optional groups.
7. Give the users group permissions--here I don't know. Do I give them no
permissions in the BE file? I want them to only modify via forms in the _FE
file.

I would not give them any permissions. However you'll want to use RWOP queries in the FE for all data interaction.

8. Create no new users--or should I put in the NT Login of everyone who can
have access? Remember, I don't want them to have to login.

Don't create any users.

9. After recording all the info, rejoin system.mdw. Create a shortcut to
the backend which I will use anytime later to make changes to the structure?

Correct. You'd use your secure mdw and login as the user you created to own everything.

Then, I think I would secure the frontend following the same steps (but
perhaps changing the user group permissions?) Is there anything I have to
ship the users along with the next version of the frontend?

Yes you'd use the same workgroup file to secure the FE. In this case, you'd grant permissions to the built-in Users Group - give that group the necessary permissions. You'll want to use RWOP queries (which will be owned by 'you'). Then the Users Group doesn't need permissions on the tables at all. Give them the necessary permissions on the RWOP queries. You can create a RWOP query for each table. Then all your queries/recordsources can be based on these queries.
More on RWOP www.jmwild.com/RWOP.htm
You then don't need to ship the users anything. They'll use their standard system.mdw, and be silently logged in and get the permissions on the Users Group.

 

[Ann in CA]

Wow--Joan, I think that this time I FINALLY got it! I'll have to play around
a bit with the RWOP queries--for example, if I've got code behind a button to
insert a row in a table, is having "WITH OWNERACCESS OPTION" in the sql
statement enough to make it work, or if I need to insert into a saved query
based off of a table. But preventing the user from opening the backend file
takes sooooo much pressure off of me, it's amazing. THANK YOU!!!!

 

[Joan Wild]

WITH OWNERACCESS OPTION in a sql statement in code is meaningless (won't work). A temporary query is created to run this and this is owned by the user running it. So you need to change the SQL to insert into a saved RWOP query (and just drop the WOO in the SQL statement).

Users will need insert permission on the saved RWOP query

 

[Ann in CA]

Okay, I think I get that. Is there any way to get DLookup to work if I'm
trying to find a value from a query that is essentially the table saved as
RWOP?

 

[Ann in CA]

Okay, maybe I'm more confused than I initially thought. My FE and BE are both
locked down. I thought I made my query RWOP, but when I open the FE without
the workgroup, I cannot even run my query to view the data because it says I
dont have permissions on the BE file?

 

[Ann in CA]

Okay, nevermind, on the BE file I gave the user Open database permissions and
now that works. Thought I could prevent hte user from opening the file
completely but I guess as long as they can't open the tables and I don't
store anything other than tables there it's fine.

 

[Joan Wild]

What's not working?

DLookup("whatever","RWOPQueryName",Where clause)

 

[Ann in CA]

Ah--FINALLY got reply to work, was about to fire Microsoft =)
So I got the DLookup to work by giving the user open database permissions on
the backend file.

Now, however, I am having another problem. I'm trying to test and modify a
database to use all of these new queries that I am creating, and it is not
letting me odify some of the queries when I am not logged in. For the sake of
this testing, I gave the user additional permissions on the FE file, such as
update queries. But I keep running into "You do not have the necessary
permissions to use the 'qryMedBulk1' object. Haev your system administrator
or the person who crated this object establish the appropriate permissions
for you." And this error does not occur on all queries, only certain ones.
Now, I know I could go in and modify them as the administrator. But the point
is, why is this error occurring, is it a sign of bigger trouble elsewhere?

 

[Joan Wild]

If you are not logged in, then you shouldn't be able to even open the secure mdb, let alone modify the queries in it.

The permissions you put on the RWOP queries depend on what the user needs to do. If they need to just read the data, then give Read Data permission. If they need to be able to delete (then delete permission), edit (then update permission), add (then insert permission). They don't need these permissions on the source tables, just the RWOP query.

You mention modifying. It's unusual for an end-user to need to modify a saved query, and there would be limits on what they could do, however you'd need to give them modify design permission on the query.

 

[Ann in CA]

No one logs into the database except for me, I give the users group all
permissions and control access to the users group by my NT folder security.

Depending on if they enter a value in a filter box or not, I use VBA to have
the user modify a query, without their knowledge, that 14 other queries are
based off of. Otherwise, no, I don't usually let the user modify queries =)

 

[Joan Wild]

I see. Users Group needs modify permission on the queries, if they are modifying the SQL prop of the saved query in code. For example, they'd need modify permission on 'qryMedBulk1' ; it is assumed that qryMedBulk1 is a saved RWOP query. Also the modification would include adding new tables.

 

[Ann in CA]

Okay, that makes sense and it's all working, for the moment. Thank you so
much again for all of your help and patience.