Securing a Database

1. Securing a Database • Understanding database security • Protecting the Database File • Protecting Code • Using User level Security • Working with workgroups • Working with Accounts • Working with Permissions • Converting a secured databases • Start Up Forms • Main Switchboards

2. DBMS – SECURITY ISSUES Security of information: Why, What, How, and When. Confidentiality Privacy Accuracy Reduce the chance for errors Level of access / privilege Unauthorized access Control Updates Sharing of Databases

3. Sharing of Databases

4. Protecting a Database File Securing a Microsoft Access database file • The simplest method of protection is to set a password for opening a Microsoft Access database (.mdb). • Only users who type the correct password will be allowed to open the database. • Once a database is open, all its objects are available to the user (unless other types of security have already been defined) Caution: Do not use a database password if you will be replicating a database. Replicated databases can't be synchronized if database passwords are defined. If passwords is forgotten, Database can not be opened.

5. Protect a database by adding a database password Caution • If you lose or forget your password, it can't be recovered, and you won't be able to open your database.Do not use a database password if you will be replicating a database. Replicated databases can't be synchronized if database passwords are defined. Notes • A database password is stored with the database and not with the workgroup information file.You can't set a database password if user-level security has been defined for your database and you don't have Administer permission for the database. Also, a database password is defined in addition to user-level security. If user-level security has been defined, any restrictions based on user-level security permissions remain in effect.If a table from a password-protected database is linked, the password is cached (saved) in the database it is linked to when the link is established. This may have unforeseen consequences. For more information, click .

6. Protect a database by adding a database password • Close the database. If the database is shared on a network, make sure all other users have closed the database. • Make a backup copy of the database, and store it in a secure place. • On the File menu, click Open. • Click the arrow to the right of the Open button, and then click OpenExclusive. • On the Tools menu, point to Security, and then click Set Database Password. • In the Password box, type your password. Passwords are case-sensitive. • In the Verify box, confirm your password by typing the password again, and then click OK. The password is now set. The next time you or any other user opens the database, a dialog box will be displayed that requests a password.

7. Remove a database password On the File menu, click Open. • Click the arrow to the right of the Open button, click OpenExclusive, and then open the database. • In the Password Required dialog box, type the database password, and then click OK. Passwords are case-sensitive. • On the Tools menu, point to Security, and then click Unset Database Password. This command is only available if a database password was set previously. • In the Unset Database Password dialog box, type your current password. • Click OK.

8. Hide objects in the Database window – Protecting Code: • In the Database window, click the object you want to hide. • Click Properties on the toolbar. • Click Hidden, and then click OK. Notes • To show all hidden objects, click Options on the Tools menu, click the View tab, and then select the Hidden Objects check box. The icons for hidden objects will be displayed with a dimmed outline. You can then repeat the steps, but in step 3 clear Hidden, to unhide the objects. • In a Microsoft Access project, you cannot hide tables, views, database diagrams, or stored procedures.

9. Security Account Passwords The second kind of password is called a "security account password" and is only used when user-level security has been defined for a workgroup. A security account password is created to make sure that no other user can log on using that user name. • The Admin user account (to activate the Logon dialog box).Theuser account that owns the database and its tables, queries, forms, reports, and macros.Any user accounts that you add to the Admins group. In addition, you might want to add passwords to the accounts you create for users, or instruct users to add their own passwords. Users can create or change their own user account passwords; however, only an administrator account can clear a password if a user forgets the password.

10. How to organize security accounts A Microsoft Access workgroup information file contains the following predefined accounts. Account - Admin The default user account. This account is exactly the same for every copy of Microsoft Access and other applications that can use the Microsoft Jet database engine, such as Microsoft Visual Basic for Applications and Microsoft Excel. Account - Admins The administrator's group account. This account is unique to each workgroup information file. By default, the Admin user is in the Admins group. There must be at least one user in the Admins group at all times. Account - Users The group account comprising all user accounts. Microsoft Access automatically adds user accounts to the Users group when a member of the Admins group creates them. This account is the same for any workgroup information file, but it contains only user accounts created by members of the Admins group of that workgroup. By default, this account has full permissions on all newly-created objects. The only way to remove a user account from the Users group is for a member of the Admins group to delete that user.

11. Secure a database using the User-Level Security Wizard By using the User-Level Security Wizard, you can apply user-level security with a commonly-used security scheme and encrypt your Microsoft Access database. • Open the database that you want to secure. • On the Tools menu, click Security, and then click User-Level Security Wizard.Follow the directions in the wizard dialog boxes. Notes • The User-Level Security Wizard creates a back-up copy of the current Access database with the same name and a .bak extension, and then secures the selected objects in the current database.

12. User-level security When using user-level security in a Microsoft Access database, a database administrator or an object's owner can grant specific permissions to individual users and groups of users on the following objects: tables, queries, forms, reports, and macros. Data access pages and modules are not protected by user-level security. Workgroup A group of users in a multiuser environment who share data and the same workgroup information file. Admin account The default user account. When you install Microsoft Access, the Setup program automatically includes the Admin user account in the workgroup information file it creates. The Admin account is the same for every copy of Microsoft Access and for other applications, such as Microsoft Visual Basic, that use the Microsoft Jet database engine. By default, Microsoft Access automatically logs you on at startup using this account and gives you full permissions to database objects. Admins group The system administrator's group account that retains full permissions on all databases used by a workgroup. The Setup program automatically adds the default Admin user account to the Admins group. There must be at least one user in the Admins group at all times.

13. Control how an Access database or Access project looks and behaves when it starts You can specify, for example, what form to display, whether toolbars can be customized, and whether shortcut menus are available. • On the Tools menu, click Startup. • Select the options, or enter the settings you want to use. Notes • For information about a specific item in the dialog box, click the question mark at the top of the dialog box, and then click the item. • For a list of issues to consider when setting options in the Startup dialog box, click .For additional information about creating an application, click

14. Create, customize, and delete a switchboard form You create, customize, and delete a switchboard by using the Switchboard Manager. Create a switchboard • Open the database. • On the Tools menu, point to Database Utilities, and then click Switchboard Manager. • If Microsoft Access asks if you'd like to create a switchboard, click Yes. • Click New. • Type the name of the new switchboard, and then click OK. Microsoft Access adds the switchboard to the Switchboard Pages box. • Click the new switchboard, and then click Edit. • Click New. • Type the text for the first switchboard item in the Text box, and then click a command in the Command box. For example, type View Recording Artists, and then click Open Form In Edit Mode in the Command box. Note:   To create a switchboard that branches to other switchboards, click the Go To Switchboard command in the Command box, and then specify the switchboard you want to go to. • Depending on which command you click, Microsoft Access displays another box below the Command box. Click an item in this box, if necessary. For example, if you clicked Open Form In Edit Mode in step 8, click the name of the form you want to open in the Form box, such as Recording Artists, and then click OK. • Repeat steps 7 through 9 until you've added all the items to the switchboard. Note:   To edit or delete an item, click the item in the Items On This Switchboard box, and then click Edit or Delete. If you want to rearrange items, click the item in the box, and then click Move Up or Move Down. • When you've finished creating the switchboard, click Close.

15. Notes • To make a switchboard open when you open the database, click the switchboard name in the Switchboard Manager dialog box, and then click Make Default. • When you create a switchboard with the Switchboard Manager, Microsoft Access creates a Switchboard Items table that describes what the buttons on the form display and do. If you make changes to the Switchboard form later in form Design view, the application may no longer work.

18. Database Recovery • Backups – Off site, full system, incremental, part • Log Files • Recovering from hardware or software failure and sudden power cut • Recovering the database with a transaction that was half way thru • Preventive methods - UPS

19. Back up a database • Close the database. If you are in a multiuser environment, confirm that all users have closed the database. • Using the Windows Explorer, My Computer, Microsoft Backup, the MS-DOS copy command, or other backup software, copy the database file (an .mdb file) to a backup medium of your choice. Notes • If you are backing up to a floppy disk and your database file exceeds the size of the disk, you cannot use Windows Explorer or My Computer to back up your database; you must use Microsoft Backup or backup software so that you can copy the file over more than one disk. • You should also create a backup of the workgroup information file. If this file is lost or damaged, you won't be able to start Microsoft Access until you restore or rebuild it. • You can back up individual database objects by creating a blank database and then importing the objects you want from the original database.

20. Repairing an Access database In most cases, Microsoft Access detects that an Access database is damaged when you try to open it and gives you the option to compact it at that time. In some situations, Microsoft Access may not detect that an Access database is damaged. If an Access database behaves unpredictably, compact it. Compacting a previous-version Access database won't convert it to Access 2000 format. Learn more about converting a previous-version Access database. If you are compacting a multiuser (shared) database that is located on a server or shared folder, make sure that no one else has it open. You must have Open/Run and Open Exclusive permissions for an Access database in order to compact it. Learn more about assigning permissions.

21. Powerful Tool to Repair Corrupt Access Database • SysTools Access Recovery is the most popular advanced access database repair tool to repair corrupt MS Access database. SysTools Access Recovery supports to repair Access database of MS Office 97/2000/XP/2003. Software is a secure & easy access database recovery utility designed to repair corrupt Access database (.mdb files). • SysTools Access Recovery recovers objects like tables, queries, relationships and repairs password protected MS Access or mdb files.