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.CONNECT
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
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.RESOURCE
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
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';
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:
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.When to Grant Privileges to PUBLIC
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
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);
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.
blog comments powered by Disqus