Oracle Supplied Objects - Oracle

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

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%';

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.

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;

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.