Write-Ups: How do roles simplify database administration with regard to privileges?

Managing privileges can be very confusing if not planned and implemented in an organized manner.
How do roles simplify database administration with regard to privileges?

I would like the answer about 200 words.

Sol:

Managing privileges is not an easy job in an enterprise database. There of thousands of privileges which are being used. Hence Users cannot be assigned and managed with privileges, so we go for something called roles. A set of privileges are assigned to a role. This role is then assigned to users. Each and every user must have a role for accessing any resources in a database.

A role definitely simplifies the work of database administration when compared to privileges. Managing a role is very much easy when compared to managing a privilege. Once the set of privileges are assigned to one particular role the role assigned to the user can be performed within the limits of the assigned privileges.

Let us take an example where the role1 is assigned to the user1 with privileges of insert, update, select and create. Here the user1 has got privileges of inserting the data to a table or updating the data or else just for selecting any table or data or create tables. We can call this as user privileges, since the delete privilege is not given to user. It’s mainly for admin use. Hence user will not be able to delete the data or table but he/she will be able to create a table, update a table or else insert data.

In this manner the set of privileges perform together in the form of a role, which is assigned to users to perform their activity. Hence role simplifies the task of DBA when compared to privileges.

References:

1) Managing Roles and Privileges, 2009