Access Security

[vani65]

hi,
I am using Access 2003 in a multiuser environment. we have 10 users. 1-5 users should be able to look everything on the application but other 6-10 users need to be limited to few objects in the application. I think I need to create low/Row level security when users from 6-10 logs in, all objects(on builtin MenuBar) should be hidden and only few should be visible. I am not too sure how this can be done. Could someone please guide me. thanks in advance

 

[willir]

Okay

Before you start, backup your database.

First you want to setup security groups...
- RestrictedUser
- SuperUser
- SuperAdmin

Create logins and assign a user to the required group. To do this, use the User and Group accounts, from the menu, "Tools" -> "Security".

Then create your permissions for the tables, forms and queries. By setting security at this level will help protect your database -- for example, SuperUsers can view forms but not edit them.

Hints:
- Use the same user account names as their respective login names on the network.
- For the database permissions, make sure to assign your users Open/Run but not give them Exclusive access.
- After setting up your SuperAdmin group / user and testing, you probably want reduce the Admin user and Admins group to minimal access. This way, if someone gets in with the known Admin user, they can't do much.
- Did I mention backups??
- Use your network security to protect the database. It should be in a Share or Folder with restricted users. Unfortunately, even the lowest of low users require update permissions since they will need to update the system tables when they login.

...Moving on
Once you have security setup, you can use it to...
- Control forms. Make controls visible or invisible (includes text fields, combo boxes, subforms, tabs on a tab form, menu options)
- If you want Record / Row security, Access does not support this. But if you add a field to the record, or parent of the record, you can either used a boolean yes/no field or a small text department field which can be checked to restrict or give access to the record. More work, but do-able.

And lastly, Access is not really considered a super secure database. You would have to upgrade to MS*SQL or Oracle or Informix like products to get the extra security.

Oh yea, one more thing, did I tell you to back up your database?

Richard

 

[vani65]

Hi Richard,
Thanks for immediate reply. I will follow what you suggested and let know how it goes.
vani

 

[psemianonymous]

If you don't believe him about the 'non-security' of Access, read my FAQ. Or not: it basically says that you can't get security, period--but you can fake it well enough for most users.

Gauging your security needs; alternatives to Access/JET security faq181-3893

 

[MoLaker]

Pseale is correct, locks are mainly for keeping honest people out. Access costs about $200 versus $1500 + for SQL Server (MS pricing). SQL Server has much bigger and tougher locks. But, that's like comparing a bicycle to a Cadillac (well, maybe a Chevy). Generally speaking, you get what you pay for, although Access is still a pretty good bicycle.