controlling users' access 1

[erolaivas]

we want our users not to be able to access oracle with any other tool than the one we deliver. i know how to find the program used during the session. the users are granted a certain role that allows them the complete access to about 2000 tables (really needed by the application). i'd like to be able at the login moment to analyze the user's program and, if not the application we allow them to use, to revoke this role and grant another role that grants them read-only access to those about 2000 tables.
can anybody help?
i tried with a logon trigger but "set role" or dbms_session.set_role doesn't work inside a trigger.
thank you
erol aivas
973-882-2000/3005
erolaivas@yahoo.com

 

[carp]

What if you assigned everybody the read-only role as a default role, then do the 'SET ROLE' to provide them with complete access when they enter via your application?

 

[MikeJones]

What stops the user logging in under SQL Plus and then executing the same code as the application would. Hey Presto - the same access.

 

[Kenrae]

We don't give final users Oracle usernames and pswds. Instead, each application has one username and pswd, and it's the app the one which controls the security.
Well, not exactly, we've centralized it.
Users don't know any Oracle pswd. Then, they can't log in under SQL Plus or whatever, only the application.

 

[carp]

Mike -
You can password protect the role and bury the password into the application (preferrably in a place the user can't find it). Hey presto - they're stuck with read-only unless they come in through the application.