There is a common requirement of restricting access of users connecting
to the database from sql*plus. Say a username and password was given
to access forms or reports, the same user should not be able to connect
using sql*plus.
In case of 8i, ON LOGON database trigger can be used. One way is to use PRODUCT_USER_PROFILE to restrict all the commands.
Another way is to use sql statements in glogin.sql which would validate
the users. Additional validations can be done based using V$SESSION.
Enabling or disabling roles can also be done using the follwing method.
1. Create table valid_users(USERNAME VARCHAR2(30));
2. Insert valid OS users who can connect to database into this table.
3. Create a file validate.sql as follows -
set head off echo off feed off
spool /tmp/excit.sql
select decode(count(*),0,'EXIT;',NULL) from valid_users
where username=user;
spool off;
4. Add this file name in $ORACLE_HOME\sqlplus\admin\glogin.sql
Explanation
-----------
glogin.sql executes everytime sql*plus is invoked.
The brain is a wonderful organ; it starts working the moment you get up in the morning and does not stop until you get into the office.