Featured Database Articles

Locking Down SQL*Plus Security

Is your data at risk? Users with valid database username/password combos can log into a database from SQL*Plus and view or edit
critical data not accessible from an application itself. The question is, how do you prevent
users from accessing data via SQL*Plus? Learn how to insert restrictions in the PRODUCT_USER_PROFILE
(PUP) table, owned by the SYSTEM user.

Users are provided access to an application via login ids.
If the username/password is a valid database user, it can also be used to log
in from SQL*Plus, which allows the user to view or edit critical data that is
not accessible from the application itself. The question is, how do you prevent
users from accessing data via SQL*Plus?

One way would be to write code to enable privileges in the application,
which would otherwise not be enabled from SQL*Plus. Another way would be to
remove access to SQL*Plus on the network. However, then strict security will
have to be maintained at the operating system and network level to disable the
access and prevent installation on all clients that connect to the database.
This is a cumbersome task and an alternative can be found in the feature
mentioned below.

Product-level Security

Users can be restricted based on the SQL*Plus product usage.
This is achieved by inserting the restrictions in the PRODUCT_USER_PROFILE
(PUP) table, owned by the SYSTEM user. SQL*Plus reads the restrictions from
the PUP table when a user logs in and applies those restrictions for the
session.

SQL*Plus will not read the PUP table if a user logs in with the
SYSDBA or SYSOPER privilege and therefore no restrictions will apply.

Setup

The table SQLPLUS_PRODUCT_PROFILE is automatically created
on installation in the SYSTEM schema.

Synonyms PRODUCT_USER_PROFILE and PRODUCT_PROFILE are
available to access this table. These were originally tables in earlier
releases, but have now been converted to synonyms on the table SQLPLUS_PRODUCT_PROFILE.
A view PRODUCT_PRIVS also exists for the same reason.

To explicitly create it, run pupbld.sql script. Most often,
this script is present in the $ORACLE_HOME/sqlplus/admin path, the exact
location is system dependent. Run this script logged in as SYSTEM user.

PUP table

The following are the key columns of the
PRODUCT_USER_PROFILE table.

PRODUCT - Name of the product,
"SQL*Plus".

USERID - User name in upper case.

ATTRIBUTE - Command to be disabled, in
upper case.

CHAR_VALUE - The word "DISABLED". For disabling roles,
this column should have the role name.

Privileges for this table are reserved with SYSTEM and all
users have SELECT privilege on this table. Avoid granting DML access on this
table to other users.

Commands that can be prevented

This feature allows disabling of SQL, PL/SQL and SQL*PLUS
commands. The following commands can be prevented by using this feature.

If a Role needs to be disabled for a SQL*Plus session, it should
be added to the PUB table as given below. The ATTRIBUTE COLUMN should have the
value 'ROLES' and the CHAR_VALUE should be the name of the role to be disabled.
The below statement internally fires the set role command, except the roles
mentioned to be excluded.

Preventing access using PL/SQL

For example, the DELETE privilege disabled for user AMAR can
easily be executed through a PL/SQL block! This can be avoided by removing the
PL/SQL block creation access itself. The DECLARE and BEGIN statements could be
locked using this feature to prevent execution of PL/SQL.

Limitations

As easy as it may look, there are limitations in this
feature.

1. This feature is only for SQL*Plus! This does not disable
access for other tools that could be used instead of SQL*Plus. Oracle provides a
Fine Grain Access Control (FGAC) mechanism for data level security that can be
used to restrict data access irrespective of the tool being used.

2. The PUP table applies to the local database. Using a database
link will not implement the PUP table restrictions defined in the remote
database.

Conclusion

I use this security to lock application users that should
not be allowed access to data from SQL*Plus. However, as this feature has its
own disadvantages; care should be taken to prevent the wrong use of data. As I mentioned
earlier, in my DDL Event Security article, such features are additional
measures you can take. They do not necessary mean that the basic security
established by password, roles and privileges take a back seat--these should
not be compromised for any other alternatives. Use the above feature to suit
your setup requirements.