oracle security and dynamic role allocation

hi gurus,

We have an CAD graphics application which uses Oracle for its DB engine (Oracle 7.3 on AIX 4.1). The application users need S,I,U,D access to most tables comprising the application. We have grabbed these object priveleges into roles and are controlling access using Oracle roles.

As a DBA, I need to make sure people do not log into the database using SQL Plus or ODBC. I cannot use DBMS_SESSION(SET_ROLE()) because my connect string is in a compiled application. I would ideally like to tie a role to an application which needs the role. We also want to revoke roles or reduce it to a CONNECT role when the person quits the application.

Any suggestions would be most welcome. We can do OS ROLES authentication and tie Oracle roles to UNIX groups, it still means that people can get in using SQL PLus or any other tool.

Hope somebody out there has a fix!

Raman Batra, Oracle DBA Received on Wed Mar 11 1998 - 00:00:00 CST