Access User-Level Security
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
User-Level security is the most flexible and secure method of protecting the sensitive data, code, and design of objects in an Access database. In addition, User-Level security is the only form of Access security that allows you to establish different levels of access to sensitive data and objects. User-Level security for Access databases is provided through the Microsoft Jet database engine. Because all access to an .mdb file is accomplished through Microsoft Jet regardless of what program is being to used to open the database, User-Level security is enforced whether Access or some other program is used to access the database.
Microsoft Jet provides a robust and powerful security model that gives you a great deal of control over users' access to your Access solution and the data it contains. Because of its flexibility, the model is somewhat more complicated than those provided by other desktop databases. Indeed, security is one of the more commonly misunderstood aspects of Access and is usually incorrectly implemented by developers who lack a cohesive understanding of its workings. The following section contains detailed information, structured in a way that helps you understand the model before you implement security.
User-Level Security Support in Access 2000
If you are familiar with User-Level security in previous versions of Access, you should be aware of the following differences in Access 2000:
****User-Level Security No Longer Supported for Modules ****In previous versions of Access, User-Level security can be used with VBA modules. In Access 2000, the entire VBA project for an .mdb or .adp file, which contains all modules (stand-alone modules, modules behind forms and reports, and class modules), must be secured by setting a password, or by saving the database as an .mde or .ade file, which removes VBA source code. For information about setting a password for a VBA project, see Chapter 17, "Securing Office Documents and Visual Basic for Applications Code." For information about saving a database as an .mde or .ade file, see "Saving Your Solution Without VBA Source Code" earlier in this chapter.
User-Level Security Not Supported for Access Project Files Microsoft Jet User-Level security can't be used with Access project (.adp or .ade) files. To secure access to the tables, views, stored procedures, and database diagrams in the SQL Server database that your .adp or .ade file is connected to, you must establish security for those objects on your server by using the SQL Server Enterprise Manager, the properties and methods of the Group and User objects in ADO code, the GRANT and REVOKE statements in Transact-SQL, or security management system stored procedures such as sp_addrole and sp_addrolemember. To secure access to the VBA code in an .adp file, you must password-protect its VBA project, or remove your VBA source code by saving your .adp file as an .ade file. To secure access to the design of forms and reports in an .adp file, you can either set startup options as described in "Using Startup Options" earlier in this chapter, or save your .adp file as an .ade file. The only way to secure access to macros in an Access project file is by setting startup options.