What is a role in a database?
A database role is a collection of any number of permissions/privileges that can be assigned to one or more users. A database role also is also given a name for that collection of privileges.
The majority of today’s RDBMS’s come with predefined roles that can be assigned to any user. But, a database user can also create his/her own role if he or she has the CREATE ROLE privilege.
Advantages of Database Roles
Why are database roles needed? Well, let’s go over some of the advantages of using database roles and why they would be necessary:
Roles continue to live in database even after users are deleted/dropped
Many times a DBA (Database Administrator) has to drop user accounts for various reasons – say, for example, an employee quits the company so his/her user account is removed from the system. Now suppose that those same user accounts need to be recreated later on – just assume that same employee re-joins the company later on and needs his same account. That employee’s user account probably had a lot of specific permissions assigned to it. So, when his/her account was deleted then all of those permissions were deleted as well, which creates a hassle for the DBA who has to reassign all of those permissions one by one. But, if a role was being used then all of those permissions could have just been bundled into one role – and then the process of re-instating that employee into the system would mean that the DBA simply reassigns the role to the employee. And, of course that role could also be used for other users as well. So, this is a big advantage of using a database role.
Roles save DBA’s time
Another advantage is the fact that a DBA can grant a lot of privileges with one simple command by assigning a user to a role.
Database roles are present before users accounts are created
And finally, an advantage of database roles is that they can be used to assign a group of permissions that can be re-used for new users who belong to a specific group of people who need those permissions. For example, you may want to have a group of permissions in a role reserved just for some advanced users who know what they are doing and assign that role to a user only when a new advanced user needs that role. Or, you can have a group of privileges for users who are all working on the same project and need the same type of access.
Disadvantages of Database Roles
The main disadvantage of using a database role is that a role may be granted to user, but that role may have more privileges than that user may actually need. This could cause a potential security issue if that user abuses his extra privileges and potentially ruins some part of the database.
An example of this is that in older versions of Oracle (before release 10.2), there is a role called CONNECT, which included privileges like CREATE TABLE, CREATE VIEW, CREATE SESSIONS, ALTER SESSION, and several other privileges. But, having all of these privileges is probably too much for a normal business user. That is probably why in newer versions of Oracle (since version 10.2), the CONNECT role has been changed so that it only has the CREATE SESSION privilege.
How to create a database role
Most RDBMS’s use the CREATE ROLE syntax to define a role. And then, the GRANT statement is used to give permissions to that database role. But, the exact details vary from one RDBMS to another so it’s best to consult the documentation.
Example of a database role
Here is an example of what creating a database role could look like:
CREATE ROLE advancedUsers; GRANT UPDATE ON SOMETABLE TO advancedUsers;