6.4. Applying the Principle of Least Privilege

The principle of least privilege states that each user should only be given the minimal privileges needed to perform their job. This principle is a central tenet to the initially closed philosophy whereby all access is initially closed or unavailable and access is opened on a need-to-know basis. Highly secure environments typically operate under an initially closed philosophy. The contrasting philosophy is an initially open philosophy, whereby all access is by default open to all users and only sensitive areas are closed. Academic or learning environments typically operate under an initially open philosophy.

Many IT organizations want the most secure policies for production systems, which calls for the initially closed approach to security. To support the need for administrators and programmers to quickly learn new technology, these shops frequently create "sandbox" systems that follow the initially open philosophy. These sandbox systems afford their limited users the learning benefit of the initially open approach, while not storing or giving gateway access to any sensitive information elsewhere in the enterprise.

To implement the principle of least privilege on your production or development systems, you should take several actions, or best practices, while setting up or locking down the database.

Protect the data dictionary. Ensure that users with the SELECT ANY TABLE privilege cannot access the tables that underlie the data dictionary by setting O7_DICTIONARY_ACCESSIBILITY = FALSE. This is the default setting.

Revoke unnecessary privileges from PUBLIC. By default, several packages and roles are granted to the special user PUBLIC. Review these privileges and revoke the EXECUTE privilege from PUBLIC if these packages are not necessary. Some of these packages include the following:

UTL_TCP Permits the grantee to establish a network connection to any waiting TCP/IP network service. Once a connection is established, arbitrary information can be sent and received directly from the database to and from the other TCP services on your network. If your organization is concerned about information exchange over TCP/IP, revoke the EXECUTE privilege on this package from PUBLIC. Grant privileges on this package only to those users who need it.

UTL_SMTP Permits the grantee to send arbitrary e-mail. If your organization is concerned about information exchange via e-mail, revoke the EXECUTE privilege on this package from PUBLIC. Grant privileges on this package only to those users who need it.

UTL_HTTP Permits the grantee to send and receive arbitrary data via the HTTP protocol. If your organization is concerned about information exchange via HTTP, revoke the EXECUTE privilege on this package from PUBLIC. Grant privileges on this package only to those users who need it.

UTL_FILE Permits the grantee to read and write text data to and from arbitrary operating system files that are in the designated directories. UTL_FILE does not manage concurrency, so multiple user sessions can step on each other, overwriting changes via UTL_FILE. Consider revoking the EXECUTE privilege on this package from PUBLIC.

DBMS_OBFUSCATION_TOOLKIT and DBMS_CRYPTO Permit the grantee to employ encryption technologies. In a managed environment using encryption, the keys are stored and managed. If encryption keys are lost, the encrypted data is undecipherable. Consider revoking the EXECUTE privilege on these packages from PUBLIC.

You can revoke the EXECUTE privileges like this:

REVOKE EXECUTE ON utl_tcp FROM PUBLIC;
REVOKE EXECUTE ON utl_smtp FROM PUBLIC;
REVOKE EXECUTE ON utl_http FROM PUBLIC;
REVOKE EXECUTE ON utl_file FROM PUBLIC;
REVOKE EXECUTE ON dbms_obfuscation_toolkit
    FROM PUBLIC;
REVOKE EXECUTE ON dbms_crypto FROM PUBLIC;

You can query the data dictionary to see what other packages may need to be locked down by revoking the EXECUTE privilege from PUBLIC. Here is a query to list the packages, owned by user SYS, that have EXECUTE privilege granted to PUBLIC:

SELECT table_name
FROM dba_tab_privs p
    ,dba_objects   o
WHERE p.owner=o.owner

AND   p.table_name = o.object_name
AND   p.owner = 'SYS'
AND   p.privilege = 'EXECUTE'
AND   p.grantee = 'PUBLIC'
AND   o.object_type='PACKAGE';

Limit the users who have administrative privileges. Grant administrative privileges to user accounts cautiously. Some powerful administrative privileges and roles to exercise caution with include the following:

SYSDBA Gives the grantee the highest level of privileges with the Oracle database software. A clever user with the SYSDBA role can circumvent most database security measures. There is usually no good reason to grant this role to any account except SYS, and the SYS password should be both cautiously guarded and changed regularly. Also guard operating system accounts carefully. If you are logged on to the database server using a privileged operating system account, you might be able to connect to the database with SYSDBA authority and no password by entering connect / as sysdba in SQL*Plus.

DBA Permits the grantee to assign privileges and manipulate data throughout the database. A clever user with the DBA role can circumvent most database security measures. Grant this role only to those users who need it.

The ANY system privileges SELECT ANY TABLE, GRANT ANY ROLE, DELETE ANY TABLE, and so on permit the grantee to assign privileges and manipulate data throughout the database. A malicious user with the one of these roles can wreak havoc in your database. Grant these privileges only to those users who need them.

Do not enable REMOTE_OS_AUTHENT. The default setting for the initialization parameter REMOTE_OS_AUTHENT is FALSE. There is rarely a reason to enable this feature. When set to TRUE, this parameter tells the database to trust any client to authenticate externally authenticated accounts. For example, if you have an externally identified account named ORACLE that has DBA privileges for use in administrative scripts running on the database server (a common practice), setting this parameter to TRUE will allow someone with a notebook or desktop PC with a locally created ORACLE account to connect to your database with DBA credentials and no password.