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.

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.

Back to DBAsupport.com