Free Web Hosting by Netfirms
 Web Hosting by Netfirms | Free Domain Names by Netfirms

AmZone

A m Z o n e

Preventing user access to data from SQL*Plus

Users are normally provided access to an application via login ids. This login id allows user to connect to the database to continue working in the front-end application. The same username/password can also be used to log in from SQL*plus that can allow user to view or edit critical data that he/she is not allowed to access. How to prevent users from accessing data via SQL*plus?
E.g.: One way would be to remove access to SQL*Plus on the network. But this handicaps the Technical guys too.


Users can be restricted based on what product is being used against the database. This is done by defining user specific profiles. This is achieved by inserting the restrictions in PRODUCT_PROFILE table, owned by system. PRODUCT_USER_PROFILE/PRODUCT_PROFILE are public synonyms created on view PRODUCT_PRIVS, that is created on this table. 



 
   insert into system.product_profile (product, userid, attribute, char_value)
                               values ('SQL*Plus', 'AMAR', 'DELETE', 'DISABLED');
The above statement will prevent delete operation by user AMAR if SQL*Plus is used to do so. Even roles can be disabled for Users, like below. 



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

   insert into system.product_profile (product, userid, attribute, char_value) 
                             values ('SQL*Plus', 'AMAR', 'SET ROLE', 'DISABLED');
As easy as it may look, there are loop holes in this feature that need to be taken care of. Like 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. 



   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');

Press the Back button of you Browser to go to previous page
Home