Default Roles - Oracle

Just as the Oracle Database comes with default schemas, it also comes with several default roles. These roles exist mostly for legacy reasons and according to the Oracle documentation will one day be removed. This section discusses these roles and how to securely interact with them.

The first default role you should understand is the CONNECT role. This is one of the most misused roles probably because its name implies that it's a necessary privilege. Many DBAs grant users CONNECT thinking that it's only the simple privilege to log on to the database. It's not! The actual privilege required to log on to the database is the CREATE SESSION privilege. As you can see from the following code, the CONNECT role has more than this single privilege:

sec_mgr@KNOX10g> SELECT PRIVILEGE
  2    FROM dba_sys_privs
  3   WHERE grantee = 'CONNECT';
PRIVILEGE
--------------------------------------
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK
8 rows selected.

Some of these privileges, such as CREATE VIEW, CREATE TABLE, and CREATE DATABASE LINK, are probably more powerful privileges than you want your users to have. Therefore, in the spirit of least privileges, you shouldn't be granting the CONNECT role to your users as the means by which they are privileged to log on to the database.

The next default role is RESOURCE. This role has also been in existence for many years and its use should be limited for the same reasons cited above. You can see this role, like the CONNECT role, also has many privileges granted to it:

sec_mgr@KNOX10g> SELECT PRIVILEGE
  2    FROM dba_sys_privs
  3   WHERE grantee = 'RESOURCE';
PRIVILEGE
--------------------------------------
CREATE TYPE
CREATE TABLE
CREATE CLUSTER
CREATE TRIGGER
CREATE OPERATOR
CREATE SEQUENCE
CREATE INDEXTYPE
CREATE PROCEDURE
8 rows selected.

There is also a hidden system privilege that is granted to users with the RESOURCE role: UNLIMITED TABLESPACE. This can be dangerous because users with this privilege have no effective quota and can use up all available disk space (see how to curtail such use in the previous section, "Limiting Database Resources").

In the following example, a user is created and granted the RESOURCE role, and by checking the user's privileges, you'll notice that the user has an unrestricted quota.

sec_mgr@KNOX10g> CREATE USER unlim IDENTIFIED BY VALUES 'noPassword';
User created.
sec_mgr@KNOX10g> -- user has no privileges
sec_mgr@KNOX10g> SELECT *
  2    FROM dba_sys_privs
  3   WHERE grantee = 'UNLIM';
no rows selected
sec_mgr@KNOX10g> -- grant resource role to user
sec_mgr@KNOX10g> GRANT RESOURCE TO unlim;
Grant succeeded.
sec_mgr@KNOX10g> -- note the user now has unlimited quota
sec_mgr@KNOX10g> SELECT PRIVILEGE
  2    FROM dba_sys_privs
  3   WHERE grantee = 'UNLIM';
PRIVILEGE
------------------------------------
UNLIMITED TABLESPACE

It's common to see grants to both the CONNECT and the RESOURCE roles within Oracle example code as well as actual deployed commercial applications. Don't assume that this is a best practice.

Complying with the least privilege principle is a best practice and relying on the CONNECT and RESOURCE roles as an easy way to grant privileges to your users is a bad practice.

DBA

Another important role that is commonly granted is the DBA role, which has every system privilege known to the database either directly granted or inherited through another role. It's not unusual for this role to be granted to the data or procedural schemas used for an application. This is a gross misuse of privileges.

Granting the DBA role abides by the most privilege principle, rather than the preferred least privilege principle. Chapter 7 shows how to effectively manage privileges and roles. When it comes to ensuring the default roles aren't abused, there are three actions you can take. However, I suggest you only do the last:

1. Revoke all privileges assigned to default roles. This can be useful in an application where you are checking to see if a user is a member of a role, but you don't want there to be associated privileges with that role, such as with the DBA role.
   
2. Drop the default roles. Check with support first, because this may have negative effects on your default applications. Be sure to make a sound backup of the database and test it on your development system first!
   
3. Don't grant the DBA, CONNECT, or RESOURCE roles to users.

PUBLIC Privileges

One of the principle techniques for securing an Oracle Database involves the careful analysis of the use of the user group PUBLIC. The user group PUBLIC, as the name implies, represents every user in the database; therefore, a grant to PUBLIC is a grant to everyone in the database. This shorthand way of granting and revoking privileges can be a very useful feature. It also can create huge security risks especially when trying to ensure the database is operating in a least privileges manner.

There are many occasions when grants to PUBLIC are sensible and don't create security risks. For example, most Oracle database application developers recognize that the DUAL table is both very useful and contains absolutely no sensitive information. This is true of other procedures and functions as well--the SYSDATE function is a good example of a useful function that doesn't have security risks associated with it. Therefore, PUBLIC access to the DUAL table and the SYSDATE function don't represent a security risk.

Unfortunately, it's difficult to know whether a grant to PUBLIC is really a security risk. As you develop your applications, you should carefully decide what, if anything, is granted to PUBLIC.

You also should consider what may not appear to be a risk today, could be a risk tomorrow. For example, suppose you have a table that stores user preferences for a web application. Initially, you allow users to save their preferences for the foreground and background colors as well as the font style that will be used in creating a personalized web page for them. Since none of this information is sensitive, you decide that it can be viewed by anyone.

scott@KNOX10g> CREATE TABLE user_prefs
  2 (background_color VARCHAR2(6),
  3 foreground_color VARCHAR2(6),
  4 font_style VARCHAR2(20));
Table created.
scott@KNOX10g> GRANT SELECT ON user_prefs TO PUBLIC;
Grant succeeded.

Later, you might add a sensitive attribute. For example, you may want to allow the user to store hyperlinks to their favorite web sites and applications.

scott@KNOX10g> ALTER TABLE user_prefs ADD favorite_links VARCHAR2(250);
Table altered.

The addition of this attribute changes the overall sensitivity of the table. The grant to PUBLIC should now be removed. The security rule for governing PUBLIC privileges is: when in doubt, do not grant access to PUBLIC.