PUBLIC Privileges | Effective Oracle Database 10g Security by Design

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     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.

Oracle Supplied Objects

In efforts to secure the Oracle database, you also have to consider the privileges that already have been granted to PUBLIC by both the applications you develop, or purchase, and the Oraclesupplied database objects.

There are two areas you should be concerned with respecting default grants to PUBLIC on Oracle objects:

Access to data dictionary views There are several data dictionary views that will give a user information that could be used to aid in a database attack.
Execute on procedures This includes PL/SQL functions and procedures and packages, as well as any Java procedures. These procedures perform many useful functions—such as opening network connections, reading files from the operating system, and setting identifier information about the user or application—all of which might be used in subsequent security processes, such as access control and auditing.

PUBLIC Access to Dictionary Views

The Oracle database already provides some security to the database dictionary metadata by restricting access to the sensitive data. Over time, the definition of “sensitive data” has evolved. Originally, sensitive data referred to items such as the encrypted user passwords. Today, even the list of all usernames in the database is considered sensitive. However, some of this data is still available to PUBLIC.

As an example, the ALL_USERS view is accessible to PUBLIC and it lists the username of every database schema. A technique often used by hackers is to obtain and use a list of valid user accounts to try to access those accounts. Privileged database option schemas (such as MDYS), default application accounts, and user accounts will be listed by the ALL_USERS view as valid targets to a nefarious user. The list of valid database users then becomes a list of valid database targets. A malicious user could easily say, “Oh look, the <insert option name or your application here> is installed. Let me use the default password and try to access this privileged account.”

Therefore, you should consider revoking PUBLIC access to certain database metadata. Looking at SYS objects that start with ALL is a good place to start:

SELECT table_name     FROM dba_tab_privs  WHERE grantee = 'PUBLIC'     AND owner = 'SYS'     AND PRIVILEGE = 'SELECT'    AND table_name LIKE 'ALL%';

Broken Objects

Before revoking PUBLIC access to default database objects, you should know that the revocation may break existing programs or applications. The following example shows 20 database objects that become invalid after the PUBLIC privileges have been removed from the ALL_USERS view.

sys@KNOX10g> SELECT count(*) FROM all_objects     2    WHERE status = 'INVALID';    COUNT(*)  -----------       0   1 row selected.    sys@KNOX10g> REVOKE SELECT ON all_users FROM PUBLIC;    Revoke succeeded.    sys@KNOX10g> SELECT count(*) FROM all_objects     2     WHERE status = 'INVALID';    COUNT(*)  ---------       20   1 row selected.

The damage isn’t unrepairable. If an application relies on a revoked privilege that was once granted to PUBLIC, it can be fixed by granting the privilege directly to the application. To do this for the data dictionary views, simply list the schemas that require the direct grant.

sys@KNOX10g> -- Show whose objects are broken.  sys@KNOX10g> SELECT distinct owner     2      FROM all_objects    3    WHERE status = 'INVALID';    OWNER  ----------------- DMSYS  EXFSYS  LBACSYS  SYS  SYSMAN  XDB    6 rows selected.

Of these schemas, some have the system privilege SELECT ANY DICTIONARY, which already provides access to the ALL_USERS view. The objects in those schemas will recompile without requiring any grants; however, the other schemas will require a direct grant on the ALL_ USERS view. You can display the list of remaining schemas that require the direct grant by using the SQL minus function bolded in the following code. This code wraps the result set in the DDL you’ll use to issue the grants:

sys@KNOX10g> -- create list of users who require  sys@KNOX10g> -- direct select privileges on ALL_USERS  sys@KNOX10g> SELECT DISTINCT 'grant select on all_users to '    2                  || owner   3                  || ';' sql_command   4           FROM ( SELECT DISTINCT owner   5                            FROM all_objects   6                           WHERE status =    7                                          'INVALID'   8                           AND owner != 'SYS'    9                  MINUS   10                 SELECT grantee   11                   FROM dba_sys_privs   12                  WHERE PRIVILEGE =   13                             'SELECT ANY DICTIONARY');    SQL_COMMAND  --------------------------- grant select on all_users to DMSYS;  grant select on all_users to EXFSYS;  grant select on all_users to LBACSYS;  grant select on all_users to XDB;    4 rows selected.

Using copy and paste technology for the values in the SQL_COMMAND, issue the direct grant to the users that require it. After the grant has been made, the invalid objects in those schemas will recompile.

Unfortunately, the consequences of the revocation are nearly impossible to predict. This is why Oracle hasn’t already removed PUBLIC privileges to the database metadata views. The Oracle Database Security Guide also warns that revoking DML privileges from PUBLIC can be nontrivial:

Revoking a privilege from PUBLIC can cause significant cascading effects. If any privilege related to a DML operation is revoked from PUBLIC, all procedures in the database, including functions and packages, must be reauthorized before they can be used again. Therefore, exercise caution when granting and revoking DML-related privileges to PUBLIC.

PUBLIC Privileges on Programs

Next, analyze the execute privileges on programs granted to PUBLIC. Again, there are too many specific programs to list and the programs will constantly be changing. The same principle applies to securing these programs as to the preceding views. That is, knowing what the programs do is important to understanding what risks, if any, are present.

The programs you should be most concerned with are the ones that start with DBMS% and UTL%:

SELECT table_name            FROM dba_tab_privs       WHERE     grantee = 'PUBLIC'          AND owner = 'SYS'          AND PRIVILEGE = 'EXECUTE'          AND table_name LIKE 'DBMS%'       OR table_name LIKE 'UTL%'  ORDER BY 1;

Caution

Don’t limit your evaluation to just these programs or SYS-owned objects. All options and applications in your database should be evaluated.

The Oracle Database Security Guide suggests revoking execute privileges on UTL_SMTP, UTL_TCP, UTL_HTTP, and UTL_FILE from PUBLIC. You should not only do this, but also remember the point of this exercise is to restrict access to procedures to only those applications, users, and objects requiring access.

Just as in the metadata example, there are often application dependencies created upon the PUBLIC grants to these programs. To successfully revoke privileges, you need to understand the dependencies and be able to rectify any problems that are created by the revocation process. Chapter 6 provides an example of this process to revoking the execute privileges on the DBMS_ SESSION package.