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:
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:
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
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.
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
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:
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%:
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.
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.
blog comments powered by Disqus