Security Question

[R2D2]

I'm trying to figure out what the best way is to fully secure my database the way I want it to be. I've browsed through some of the posts and skimmed the Microsoft security FAQ, but I'm not seeing answers to my questions, and I'm unsure if that's the route I really want to take.

Basically, my database is more of an application than a database. It has a series of forms that the user interacts with. These in turn create a query, which pulls records into a table called "Your Query Results" (creative name, huh?). This is all I want the users to have access to: my forms, this one table, and a few other queries. These queries (and the table) are opened by buttons on my forms, so I don't want my users to have the ability to interact with the database itself at all.

My security at this point consists of distributing an .mde file (to protect the VBA code), disabling the shift bypass function, hiding the database window, and having the users logon using their oracle userid and password (I use a passthru query to check this). When I logon, I have my code show the database window, but it remains hidden for everyone else.

I discovered this week that my users can gain full access to any database object simply by choosing File->Open and opening the database while they are already in it. This basically just opens up the database window, and from there, they can do just about anything they want. In addition, they can import any of the objects in my database into another database, and gain access to the tables/queries that way.

Clearly, I need to bump the security up a notch; however, I've got a couple of questions I haven't gotten answered even after spending a significant amount of time skimming the FAQ, Access Help and this forum:

1. I don't want my users to have to login twice (once with their Oracle login, and again with a seperate access login). Is there some way I can have them just do the Oracle login, and then have my code do the appropriate access login for them? When I tried working with the built in Access security, it required the access login as the very first thing, but I'd like to have this come after they use their oracle login. At the same time, I don't want my database to be vulnerable while they're doing their oracle login - I'd want everything fully locked down while their doing this.

2. I'm unclear about what permissions VBA code has. For example, let's say I want to allow my VBA code to read, edit or add records to a given table, but I don't want any users to have access to this table besides myself. Does Access security allow you to do this? Or does it apply the security of the logged in user to the code?

3. Is there a way to accomplish what I need to do with out setting up Access security?

4. The Security FAQ applies to Access 2000 and all prior versions, but Access 2002 (which I'm using) came out after the FAQ was written. I've looked for a newer faq but can't find one. What differences do I need to be aware of?


Thanks to anyone who can help me!

 

[R2D2]

Pat - thanks so much for your help. I REALLY appreciate it.

I've spent my morning at work rereading the FAQ, working with Access security and generally just trying things. Here's what I've come up with which I think will work. Let me know if there's any gaping holes in this.

- I've created a new user and added it to the Admins group. This is the user I'm using to administer the database, and it has full permissions for everything, as well as being the owner for all objects.

- I've removed the "Admin" user from the Admins group and given it a password (for now.....as I'll explain below, I plan on taking this off).

- I'm going to set the security on the Users group to have access to only one table (called "Your Query Results", which I mentioned in my first post) and only Run permissions for all my queries.

- All my queries will have have the "RunPermissions" property set to User's as a default, but before my code runs any queries, it will temporarily set this to Owner's Permissions, run the query, and then set it back to User's.

- Before giving this database to anyone, I plan on compiling it as an .mde, and removing the password from the Admin account. That way, my user's won't actually have to log into access; they'll automatically open up the db as user Admin and have the limited permissions. I will retain a backup copy of the database with the Admin password still set so that I can still login as my administrative user and make changes to the database.

What do you think....will that work?

There's one piece I'm still slightly unsure about:

- This is not a shared db (although the backend db that contains the tables themselves is). All my users will have individual copies of the database on their machines. Do I need to distribute the .mdw file with the database? Or, can I put it in the same network directory as my backend DB and simply have the database access it there? Or...is it even necessary for my distribution once I remove the Admin password?

Thanks again...!