Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Feb 24, 2004

Locking Down SQL*Plus Security

By Amar Kumar Padhi

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.

SQL: ALTER, AUDIT, ANALYZE, CREATE, DELETE, DROP, INSERT, LOCK, NOAUDIT, RENAME,

SELECT, UPDATE, VALIDATE, TRUNCATE, GRANT, REVOKE, SET ROLE, SET TRANSACTION

PL/SQL:DECLARE, BEGIN

SQL*PLUS: COPY, HOST, SET, EDIT, PASSWORD, SPOOL, EXECUTE, QUIT, START, EXIT, RUN, GET, SAVE

Examples

insert into product_user_profile(product, userid, attribute, char_value)
                          values('SQL*Plus', 'APPS', 'DELETE', 'DISABLED');

insert into product_user_profile(product, userid, attribute, char_value)
                          values('SQL*Plus', 'APPS', 'INSERT', 'DISABLED');

insert into product_user_profile(product, userid, attribute, char_value)
                          values('SQL*Plus', 'APPS', 'SELECT', 'DISABLED');

insert into product_user_profile(product, userid, attribute, char_value)
                          values('SQL*Plus', 'APPS', 'UPDATE', 'DISABLED');

insert into product_user_profile(product, userid, attribute, char_value)
                          values('SQL*Plus', 'APPS', 'CREATE', 'DISABLED');

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.

insert into product_user_profile(product, userid, attribute, char_value)
                            values('SQL*Plus', 'APPS', 'ROLES', 'DBA');

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.

   insert into system.product_profile (product, userid, attribute, char_value) 
                               values ('SQL*Plus', 'AMAR', 'DECLARE', 'DISABLED'); 

   insert into system.product_profile (product, userid, attribute, char_value) 
                             values ('SQL*Plus', 'AMAR', 'BEGIN', 'DISABLED'); 

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.

» See All Articles by Columnist Amar Kumar Padhi



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM