Roles in Oracle Database
When it comes to a database, what is a role? Before I answer that, let me ask and answer another question; what is privilege in a database?
Privilege
A privilege is the right to run a specific task or query on a database. You’re being allowed to do something. This can be very broadly restrictive and look at the kinds of SQL commands you are, or are not, allowed to use (select, insert, update, etc).
Privileges can also be more focused, with a narrow field of view. This narrow field of view would encompass object privileges. This basically denotes which tables, or subsections of tables, a user can and cannot use.
Role
So how do these privileges lead to roles? Well a role is a group of privileges in a single package. This allows for the administrator of the database to easily grant or revoke privileges among the database users. Rather than manually assigning privileges to each user, a user can be assigned a role.
You might be wondering about similar roles. What if one is a subset of another? Well a role can contain other roles as well as privileges. This can make updating roles easier.
Say for instance a ‘developer’ has all the privileges of a ‘tester’, plus a few others. So instead of making two near identical roles, a ‘tester’ is made and given roles to. Then a ‘developer’ is made and given the extra privileges, as well as those it inherits from the ‘tester’.
It’s then decided that both are to be given the same, new privilege. Since the ‘developer’ role contains the ‘tester’ role, by updating the ‘tester’ the privilege is passed along! A classic example of inheritance in computing.
While you can nest roles like this, remember that you cannot do this cyclically. Meaning, if we take our example, that while ‘developer’ can contain ‘tester’, ‘tester’ cannot in turn contain ‘developer’.
Implementation
So now you know the basics of what a role is, and how they shape a user’s interaction with your database. The next step is creating a role and adding privileges.
As it stands, an Oracle Database ships with a few dozen predefined roles. While these cover many of the use cases you might need, these roles have the potential to be changed as database updates are released. This is where the importance of user defined roles comes into play.
I’ll assume here that you have your basic (or complex) database set up, and that you are signed in with a user with the CREATE ROLE privilege. This would typically be something the database administrator, or a security administrator would have. There’s also an assumption that you have at least basic knowledge of SQL to have set the rest of that up.
Creating a Role
If you’ve set up your own database from the ground up, you probably know how to create a role. But this is here in case you need a quick refresher, or if you’re using a pre-built database.
To begin with the role name must be unique when compared to the other existing role names, and compared to user names. Let’s call this new one ‘employee’.
To be able to grant a role to a user, the role must be constructed using some form of authorisation. Either by a password, or an external application. For this example we’ll choose password.
CREATE ROLE employee IDENTIFIED BY password;Quite a simple process, no? Granting privileges to this role is almost just as easy! The toughest part is knowing the list of privileges you can grant (but you can just sneak a look at the documentation when you’re doing that, we won’t tell).
Granting a Privilege
Now let’s say we have a table ‘sales’. We want employee to be able to view and query the table, but without them being able to alter it. For this we give them the Object Privilege ‘SELECT’.
Similarly to creating the role, we have a pretty simple, one line command to grant the privilege:
GRANT SELECT ON sales TO employee;Granting a Role
Finally, let’s grant the role we’ve created to a user. We’ll say we have j_doe as a user of our system, who we want to grant the role of employee to. This is more or less the same process as granting SELECT to the employee role:
GRANT employee TO j_doe;One more little titbit I’ll throw in. Say you want to set up j_doe with this role, but allow them to grant the role to other users. There’s a simple addition to append to the command to allow for this:
GRANT employee TO j_doe WITH ADMIN OPTION;A few things to be wary with this option. As well as granting the role, the user can also revoke the role. In this set up j_doe would also have the ability to grant with admin option, which would allow j_doe`s grantee the same ability.
Most importantly, by using WITH ADMIN OPTION the grantee can alter the assigned role. So be sure that if you use this, it is only for trusted employees or users.
There are quite a few more options available to you when creating, granting to, and revoking roles. But this at least covers the basics, which cover you for a lot of use cases.
If you’d like to try this out for yourself, consider signing up for an Oracle free cloud trial and setting up your own database.
Questions, comments? You can find me over on LinkedIn.
* All views are my own and not that of Oracle *
