Oracle has several features to allow you to improve the security of your database. One such feature is the ability to grant privileges to users. In this article, I’ll discuss what privileges are and list some best practices for their use. It will help you to gain more visibility into what’s going on in your Oracle database.
What Are Oracle Privileges and Roles?
Different users can be set up on your Oracle database, which allows other people to log in to your database and do their work. In reality, different users have different needs. Analysts may simply need to view data in some tables. Developers may need to insert, update, and delete data. Database administrators may need full access.
In Oracle, a privilege refers to an action that can be performed by a user. There are three main types of privileges:
- Object: privileges that allow access to a specific object, such as a table.
- System: privileges that allow system commands to be performed, such as creating a tablespace.
- Role-based: a group of privileges that apply to all users within a role.
A role is a group of privileges. Users can be allocated to roles, and they receive all the privileges associated with that role.
Best Practices for Oracle Privileges
In Oracle, there are many ways to set up privileges for roles and users—some are good, some are not so good. Let’s look at some of the best practices for granting privileges.
1. Plan Your Access Carefully
When setting up privileges for all users, things can quickly spiral out of control. As is often the case for software, it helps to carefully design and plan what you’re going to do before you do it.
You can grant privileges to specific users, to roles, and to grant roles to other roles. If the access isn’t preplanned to ensure you provide the appropriate access for each user, you may face access overlaps and/or some users may have more access than they need. Conversely, a lack of planning might mean that some users don’t receive the access they need. So, it’s a good idea to plan what access you want to give before you give it.
This often involves:
- Defining the role each user needs
- Defining access for each role
- Assigning users to roles
2. Grant Privileges to Roles Instead of Specific Users
There are two ways to grant privileges to users:
- Grant them to specific users (e.g., to JSMITH)
- Grant them to roles (e.g., to DEVELOPER) and add the user to the role
Of these two methods, granting privileges to roles and then adding the user to the role proves advantageous. This is because it reduces administration, is easier to view, and is consistent for users.
3. Assign the Least Access Needed for Users to Do Their Jobs
You may be tempted to grant many privileges to users to let them do what they want or to allow them access in case they need to do something outside the traditional scope of their job. However, this may present a security risk. Users may make changes that you did not foresee and these changes may not manifest for a while because it’s not something they do often.
The best practice for granting access is to give a user the privileges he or she needs to work properly and nothing more. That is, access granted should be the minimum required for users to do their jobs.
4. Create Users for Individual DBAs Instead of Generic DBA Users
Having a generic user as database administrator (DBA) is easy and tempting. However, it’s better to set up a separate user for each DBA for several reasons:
- Easier audit tracking. If there are separate users, you can keep track of changes easier than in the case of a single user.
- Better password management. Having separate passwords means that each user only knows his or her password.
5. Split DBA Role from Developer Role
During development, it’s common that the DBA and developer are the same person.
However, when a system moves into production, the DBA and developer are usually different roles. For this reason, it’s better to create different roles for these two jobs and assign them separately. When one person is the DBA and another the developer, privileges are easier to manage.
6. Do Not Use Oracle Roles Such as CONNECT
Some roles are built into Oracle, such as the CONNECT role.
Generally speaking, it’s not advisable to use these roles when setting up your security because Oracle can change them at any time for any release. It’s better to create your own roles tailored specifically to your needs.
7. Roles Can Be Nested
You aren’t limited to assigning roles to users. You can assign roles to other roles. This allows for the “nesting” of roles.
For example, you might have:
- An HR role that allows HR users to view salary data.
- A DEVELOPER role that allows developers to create tables.
- A MANAGER role that combines both HR and DEVELOPER.
The MANAGER role will have privileges from both the HR and DEVELOPER roles without needing to add privileges specifically to the MANAGER role. This also means that if you update the DEVELOPER role, any concomitant roles (such as MANAGER) will also be updated.
Role “nesting” cuts down on administration and makes it easier to manage.
8. Avoid Granting PUBLIC
You can assign privileges to PUBLIC. However, this is not a good idea. Granting privilege to PUBLIC means that anyone can use the privilege. This is hard to control. It also takes priority over the other privileges you have set up. So, for enhanced security, I’d recommend avoiding the PUBLIC option.
9. Only Grant Necessary System Privileges to Trusted Users
I mentioned that there is a type of privilege called “system privilege.” This grants all kinds of privileges to the specified user or role (such as UPDATE ANY TABLE). It’s best to avoid these kinds of privileges in your database. They can be abused if they are not set up correctly, and they are harder to maintain. If you must set them up, only grant them to trusted users—people who you trust not to abuse the system or break the database.
To get more handful how-tos for Oracle database security check-out “How-To” category in Netwrix Academy.
