This article is relevant to versions < 18.1.
In Oracle 18c, the PRODUCT_USER_PROFILE table is deprecated, and will be completely removed in Oracle 19c.
For more information, consult the
documentation
In cases where you have an oracle client installed on a user's PC, skilled users can easily us sqlplus as a "backdoor" to the database by connecting directly through sqlplus instead of using their designated applications. Here is a trick to prevent that from happening, without too much effort for the DBA nor for the PC maintenance guys.
What is it?
-----------
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.
How does it work?
-----------------
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.
How to set up?
--------------
The table SQLPLUS_PRODUCT_PROFILE is automatically created on installation in the SYSTEM schema.
To explicitly create it, run pupbld.sql script
Then insert the following lines to prevent user SCOTT from doing DML and SELECT:
insert into product_user_profile(product,userid,attribute,char_value) values ('SQL*Plus','SCOTT','SELECT','DISABLED');
insert into product_user_profile (product,userid,attribute,char_value) values ('SQL*Plus','SCOTT','UPDATE','DISABLED');
insert into product_user_profile (product,userid,attribute,char_value) values ('SQL*Plus','SCOTT','INSERT','DISABLED');
insert into product_user_profile (product,userid,attribute,char_value) values ('SQL*Plus','SCOTT','DELETE','DISABLED');
Check with:
SELECT product,userid,attribute,char_value
FROM system.product_user_profile;
PRODUCT USERID ATTRIBUTE NUMERIC_VALUE
---------- --------- -------------------- -------------
SQL*Plus SCOTT DELETE DISABLED
SQL*Plus SCOTT INSERT DISABLED
SQL*Plus SCOTT SELECT DISABLED
NB!
To prevent access from using PL/SQL:
For example, the DELETE privilege disabled for user SCOTT can easily be executed through a PL/SQL block!
This can be avoided by removing the PL/SQL block creation access itself.
insert into system.product_profile (product, userid, attribute, char_value)
values ('SQL*Plus', 'SCOTT', 'DECLARE', 'DISABLED');
insert into system.product_profile (product, userid, attribute, char_value)
values ('SQL*Plus', 'SCOTT', 'BEGIN', 'DISABLED');
Result:
SQL> connect scott/tiger
Connected.
SQL> select * from user_tables;
SP2-0544: Command "select" disabled in Product User Profile
Restrictions:
- Can only be used for SQL*Plus
- Only applies to local databases. The PUP table restrictions will not be implemented using a database link to a remote database.
More info
here
