Maintaining the Database [MS Access]
Database Security is essential
Security can be administered in the form of:
-A database Password
-User-Level Security
Compacting a Database
-Backing up a Database
-Importing and Linking external data
Database Password
—————————–
-Simplest mode of securing a database.
-Not very fool proof.
-Database password to be used for opening database and accessing data from it.
Encryption
———————
The encrypted database cannot be read by using any Disk Editors.
Encrypting a database degrades its performance. The RDBMS takes care of ‘Decrypting’ the database.
User Level Security
————————-
1. Read, Write, Delete and modify structure of tables
2. Read, and Write
3. Only Write
Groups
———–
Very useful when there are a number of people who are performing the same role.
Assigning permissions
———————————-
GRANT: Grants permissions to user.
GRANT SELECT, INSERT ON OrderMaster TO john;
REVOKE: Revokes permissions from user.
REVOKE INSERT ON OrderMaster FROM john;
The Data Dictionary
—————————–
Data dictionary stores Meta data (data about data)
Oracle maintains all this information in “System Tables”
Workgroup Information File
—————————————
Also called ‘Workgroup’ or ‘System’ file
This file is called System.mdw
Stores Usernames, Passwords and the Groups to which each user belongs.
Access reads this file at startup
Access cant start if this file is lost or damaged
Created by Access at installation time
New WIF is created through WRKGADM .EXE utility
Optimising Tables
—————————-
Tables need to be optimised to ensure that they operate efficiently.
1. Avoid Redundant Data
2. Choose Appropriate Data Type
3. Index Only When Necessary
Compacting an Access Database
————————————————-
1. A database gets fragmented due to addition, modification and removal of data.
2. Scattering of data is called fragmentation.
3. Compacting rearranges the way data is stored on the disk.
4. Reduces disk space occupied by database and Increases speed of data retrieval.
Backing up
——————
1. Backup databases to prevent loss of data
2. The workgroup information file also need to be backed up.
3. Backups are typically maintained on devices like floppy zip or tape drive.
4. Maintain a copy in a different location.
Importing and Linking
———————————
Importing creates a duplicate copy of the data in Access. Changes made to the imported data does not affect the original data.
Linking creates a reference to the master database in Access. Changes made to the linked data is reflected in the original data as well.
