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.
Note | 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. |
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:
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.
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!
Don’t grant the DBA, CONNECT, or RESOURCE roles to users.