There are a number of options available for limiting user access to the database. You can grant/restrict access of a specific user based on IP address as discussed in the article Tricky Oracle Access by IP Address!! However sometimes we need to limit user access instead of restricting it completely. In this article we will discuss some great tips that will help you limit your user access to the database.
Oracle PRODUCT_PROFILE Table:
Oracle PRODUCT_PROFILE table can be used for restricting a user or group of users from running a specific command. You can use it to disable roles and report the current restrictions in the profile table. If PRODUCT_PROFILE does not exist, then you can create it by running the PUBBLD.SQL script.
Limiting User from running Specific Commands:
Consider a scenario where you own a large database of some hundred tables and you do not want Schema Owner to run the DELETE command on any of the tables. One solution is to use the GRANT / REVOKE commands. However the simple solution is to insert a row in the PRODUCT_PROFILE table. Below command will restrict user SCOTT from running DELETE command on any table.
INSERT INTO PRODUCT_PROFILE (product, userid, attribute, char_value) VALUES('SQL*Plus','SCOTT','DELETE','DISABLED');
You can re-enable the command by simply deleting that row from the PRODUCT_PROFILE table.
DELETE FROM PRODUCT_PROFILE WHERE (product='SQLPlus' AND userid='SCOTT' AND attribute=' DELETE ' AND char_value=' DISABLED')
Note that you must be logged on as SYSTEM in order to delete any row from the PRODUCT_PROFILE table.
Limiting User Roles:
Sometimes it happens that we want to assign some specific role to user for the purpose of running application but we don't want user to have that role while running ad-hoc commands from SQLPlus. However you can achieve this goal by inserting below row into the PRODUCT_PROFILE table.
INSERT INTO PRODUCT_PROFILE (product, userid, attribute, char_value) VALUES ('SQLPlus','SCOTT','ROLES','PAYROLL_ADMINISTRATOR');
You can re-enable the command by simply deleting that row from the PRODUCT_PROFILE table.
DELETE FROM PRODUCT_PROFILE WHERE (product='SQLPlus' AND userid='SCOTT' AND command='ROLES' AND char_value='PAYROLL_ADMINISTRATOR')
Assigning only one Oracle session to User:
Sometimes our requirement is to allow only one session to each user in the database. One cumbersome solution to this table is to run a batch job that analyzes V$SESSION table and kills sessions of those users who have more than 1 session. Another approach is to create a LOGIN table and insert a row into it whenever user logs in and remove the row with log out. However this approach has lots of problems especially when a row does not gets removed due to application crash. A simple solution to this problem is described below whereby any user with the ONE_SESSION profile can log on only once.
SQL> CREATE PROFILE ONE_SESSION LIMIT SESSIONS_PER_USER 1;
PROFILE CREATED.
SQL> ALTER USER SCOTT PROFILE ONE_SESSION;
USER ALTERED.
SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
SYSTEM ALTERED. Read Again!!
Best regards,
Abdoul Rahaman
IT Consultant
Globe Marine Services Co
Jeddah
