Security can be maintained by giving different authorities to different users according to their work. Different authorizations are :
- Read access : It allows only reading of User cannot modify, delete, alter or update the data.
- Update access : It allows only updation of User cannot delete data.
- Insert access : It allows addition of new User cannot modify and delete the existing data.
- Delete access : It allows deletion of User cannot modify the existing data.
- Index access : It allows the creation and deletion of indices.
- Alteration access : It allows the addition or deletion of attributes in a relation.
- Resource access : It allows the creation of new relations.
- Drop access : It allows the deletion of relations.
Difference Between Delete Access and Drop Access
A user having delete access can only delete data but not relations. Even if user delete all the data, relation exists. While if relation will be deleted nothing remains.
Ex. Consider the Student Information System.
– After deletion of all the data, relation or table exists as shown in Figure 9.1.
– But drop command deletes the table completely with its data.
Ex. Drop table student.
This command deletes the whole table named Student.
By giving above authorization, DBA maintains security in database and avoid unauthorized accesses.
Source: Gupta Satinder Bal, Mittal Aditya (2017), Introduction to Basic Database Management System, 2nd Edition-University Science Press (2017)