Assigning Oracle user security roles

Oracle Tips by Steve Karam, OCM

Also see our notes on:

Assigning Oracle user security roles

A database must have users. Without them, there is no profit. But having users connecting to your database presents a whole range of potential problems. You, the DBA, must give privileges to access other objects on the database.

There are three levels of users in the average database system:

Administrators
Developers
End users/ Vendors/ Customers/ Viewers

The article you are reading now is stored in a database, and is being delivered by a web application to your computer screen. At this moment, you are an end user. When you go to the bank and use an ATM, you are a user of a database. The act of putting your ATM card into a system and enteriing your PIN is your login to the application. From there, you are a customer and viewer, able to select (view balance), insert (make a transaction), and update (deposit/withdraw).

The main thing that you, the DBA must apply to your users is the principle of least privilege. Here are some ideas for making this principle work:

Do not give your users more abilities than they need to get the job done
Revoke unnecessary privileges from the PUBLIC pseudo-user
Expire and lock unnecessary users

Do not give your users more abilities than they need

This is the foundation of the principle of least privilege. Give your users only the rights they need to do their job, and no more. Do not give DBA to your users just to make life easier on you. Even simple roles such as CONNECT and RESOURCE can pose a security threat to your system. If you are not carefull, a user can easily crash your database using just the CONNECT role.

To comply with auditing standards and to protect your database, you should create separate roles for other DBAs, developers, and other database users. Create your roles according to job roles. If your company has sales clerks and sales managers, create the SALES_CLERK and SALES_MANAGER roles. If you have a junior DBA, create a JRDBA role. This makes it easy to maintain security through changing business rules and employees. It also will help you if you are audited. View the example below:

create role cust_serv_clerk;
grant select on customers to cust_serv_clerk;
grant select, insert, update on issue_track to cust_serv_clerk;

In this example, we create a role called CUST_SERV_CLERK. To this role we grant the ability to select from the CUSTOMERS table, and select, insert, and update the ISSUE_TRACK table. The role now has the privileges of a customer service clerk.

grant cust_serv_clerk to bob;

The above command gave a user named 'bob' the role. Bob now has all of the privileges granted to the role.

create role cust_serv_mgr;
grant cust_serv_clerk to cust_serv_mgr;
grant insert, update, delete on customers to cust_serv_mgr;
grant delete on issue_track to cust_serv_mgr;
grant cust_serv_mgr to mary;

Now we have created a role called CUST_SERV_MGR. To this role we have granted the CUST_SERV_CLERK role. Along with it, we grant the privileges it leaves out: insert, update, and delete on CUSTOMERS, and delete on ISSUE_TRACK. Lastly, we grant this newly created role to the user named 'mary.'

Use roles and privileges to enable your users to do the work they need. However, if you grant too much to your users, it will come back to bite you. Any user has the ability to see the privileges that are granted to them. All it takes is one clever user to figure out how to exploit those privileges - and in the DBA world, a mistake like that could cost you your credibility, if not your job.

Revoke unnecessary privileges from the PUBLIC pseudo-User

In Oracle, there is a pseudo-user named PUBLIC who is everyone and no one. A privilege or role granted to PUBLIC will be given to every user - past, present, and future. Never, under any circumstances, grant privileges to PUBLIC that you do not wish every user to have. Even if you want to do this, make sure there is a pressing business reason for doing so. It is not good enough to argue that it makes life easier for you.

The PUBLIC user comes with several execute privileges on key Oracle packages. Some of these packages include:

DBMS_RANDOM - Used to generate random numbers, often used as encryption seeds
DBMS_OBFUSCATION_TOOLKIT - Oracle's encryption toolkit
UTL_FILE - Utilities to read and write to the file system
UTL_SMTP - Used to send mail from PL/SQL
UTL_TCP - Network functionality from PL/SQL

Look again at the descriptions of the above packages. The thought of every database user having these privileges should terrify you. You can very easily revoke privileges from public, and then grant them only to the users that need them - like this:

revoke execute on dbms_obfuscation_toolkit from public;
grant execute on dbms_obfuscation_toolkit to security_user;

To find out what is granted to your PUBLIC user, you can use the following query:

select privilege from dba_sys_privs where grantee = 'PUBLIC';

Be warned that you should NOT revoke every privilege granted to PUBLIC. Look for key packages and use your common sense to find critical ones that should be revoked.