Prohibiting deletion of Access db file/hiding specific fields

[ps40life]

Hello,
Two questions:
1. I have a database that I just put security on. That is working well, but what about actually deleting the whole db file. When I go in to the network server folder, I can just pick it and delete it. How can I prevent someone from mistakenly deleting the db file?

2. I see that you can limit access to tables, forms, etc. as a whole, but how can I keep certain users from seeing just one specific field in a table, like a password field, but allow them to see other info in the same table. If I give Open/Run and Read privileges, can't they look directly at anything they want in the table, even I leave it off forms and reports.

Thanks! : )

 

[Guest_imported]

The answer to the first question lies with your Network people. Getting and setting Folder permissions, etc. Talk to them about protecting files/folders.
The second problem is normally handled by splitting the database. That way no one has access to the Tables except thru Forms, Reports and queries. You control who sees what information. You can hid a column in a Table, and you can do the same thing using VBA, but you can unhide it using the Toolbar. Format.....Unhide Column. So in VBA you could hide it based on the UserID but the user could unhide it.

This probably doesn't help much, but it's food for thought.

Paul

 

[JimHorton]

ps40life,
The second part can be handled with an Owner Access Query and Access Security. First, set up security (there are faq's here for that so I won't go into it, but it's somewhat involved).

Then leave the password field out of the query, but put the other fields in. Restrict the table totally--don't even give 'read' permission, but set the query (in properies under 'Run Permissions') to 'owner'. Make sure the query and tables are owned by the secured user (not admin).

Now the table itself will not even be able to be opened by a non-priveleged user, but the query and your chosen fields will open and be updateable as if it were the non-secured users. This query could be the source of the form, as well.
--Jim

 

[ps40life]

Thanks for the suggestions. I have a few more questions.

Re: Splitting the db (Paul)
I was thinking about splitting the database, but don't you still have access to the tables because they are linked?

Re: Owner Access Query (Jim)
The query sounds like a good idea. I am not sure I understand it all though.
I set up the security through the Security Wizard. I am pretty new at this, so I am not sure if that was good or bad. I don't understand the part about the "password field".

I did find one FAQ that looked like it might help. Thanks for the tip. : )

 

[JimHorton]

ps40,
There are a few confusing concepts in the security model here. If you go the Security route (which, if you're going to do some real-world Access projects, you should) then read on, otherwise the below paragraphs will be a bit confusing and overkill. But you should understand the difference between 'database password', 'password', and PID.

First thing is to forget about 'database password' completely. It's easily hackable and just plain confusing to most users.

Now, when you create a new user (hit F11 to focus the main db window, then use the Tools menu, then Security, then User & Group Accounts), you choose "New..." and add the user name in the upper textbox that is presented. The lower textbox is for PID, which is *not* a password, and you will most likely never need to use this value again. Remember or write it down, though, because if you lose your system.mdw (where this info is stored) you'll need this to recreate that user.

You can't create a password at this point, however. But make sure for the new user, if it's your new 'God' user, to add the Admins Group to his groups.

A bit about the PID field. It is only used internally by Access to create a hash value for the userid, ie. when Access checks permissions on an object, say a table or query, for the logged on user, Access never sees 'john' or 'guestuser' or whatever, instead it sees the userid as some gibberish created by some algorithm involving the userid and this PID, but you'll never ever need to know or deal with this value.

Now, the password is what is used to log the userid onto Access when you open the program--but you can't set the password until you logon as that user. This is sort of a catch-22, because you can't logon as that user unless you use a command line to open access via a shortcut (forget about that for now), or create a password for Admin user, which is who you'll be normall logged on as. So create an easy password for 'admin', usually just make it 'admin' or 'x' or something, because this user is going to be a user without many permissions, and you don't care about a difficult password for him.

So after you create admin password, the next time you open Access you'll be forced to enter a userid/password. So you log on as your new user, say, 'john', and *no* password, since you haven't created it yet. Now go into the Tools|Security|User & Group Accounts, and create the password.

Now you must create a new blank .mdb, (while logged on as 'john' or whatever your 'god' user is), and import all the objects from your existing database. Keep, but rename and save the old db. Now rename this new db to the orignal name, and you're ready to go in and strip all object permissions from the Admin User and Admins Group, same with 'User' user & groups. In the User & Group Permissions menu option, you can select the 'Object Type', and then use the Shift key to select all the objects in the 'object name' window, and then to remove all permissions, just uncheck 'read design', which unchecks all at once. But for each set of object types you select, you must do the same for Admin user Admins group, and also User user and User group. Then you'd typically, for each set of object types, give 'read' permissions, or 'open' (for forms, reports) and then test the db (while logged on as Admin) and add permissions as you see fit--which of course requires closing access and loggin back on as 'john' or whatever.

This seems like alot, but once you do it a few times, it will become much more understandable, and it's really the better way to go, in my opinion, rather than .mde (kludgey) or using database password, etc.
--Jim

 

[Guest_imported]

I think Jim's answer is closer to what you need. Splitting the db will not protect that particular field without Access security being in place. Sorry, it must have been Friday Fog that got in my way.

Paul

 

[JimHorton]

ps40,
I may have misunderstood you asking about 'password field', I thought you were referring to password in setting up security, but then I saw that in my response to your first post that you had used as an example a 'password field'.

I'm guessing you mean you have a table in which there is a field where some password is stored? First, you could set the inputmask to Password, and it will mask it, but this still isn't secure; a dlookup can get by that. So, what I meant was, in the Owner Access query, you just drag all the fields from the table, except the password field. Then if that field is needed in code, you can code dlookups or recordsets or whatever to get that value, but it will never be a part of the form or query, and all the code behind the form will be secured.
--Jim