Oracle Security - session manipulation

[pradip_chanda]

Database: Oracle 8i 8.1.7
Application: Developed through IDS ( Forms & Report 6i, Query & PRO C)

The users logs on to the database through the above application. After logging on to the system the Application assigns them insert & update grants for the tables which otherwise the users doesn't have. When the users comes out of the application these grants are revoked.

But if a user opens another connection through 'sqlplus' during the connected period through my application, he gets to enjoy the rights to update/insert in tables through the 'sqlplus'. This way he is able to manipulate the data using another session.

Please give tips on prevention of such stuffs

 

[Marco Kalter]

You should not grant these rights at run-time by the application, but rather grant a non-default role when the user is created by the DBA, which needs to be enabled with a password. When the application connects, it enables the granted role for this session (!) with the password. This password is only known to the DBA and the application.

When the user connects through SQL*Plus, this creates a new session where the role is not enabled. The user does not know the role's password, so he/she cannot enable it, and cannot use the privileges that are granted through this role.

For more information about non-default roles with passwords, see the SQL Reference guide (CREATE ROLE IDENTIFIED BY, ALTER USER DEFAULT ROLE).

------------------
Marco Kalter
Allround Automations

 

[heuserj]

There is an excellent description on how to do this in the book Oracle Security by Theriault and Heney.

They propose having a table with the roles and associated passwords. Use use a stored procedure to build the string to be inserted into a dbms_session.set_role command.

It works real well inside Delphi and DOA and you do not have to embed passwords in your app.

John