Here is a after logon trigger that can be used to prevent the Discoverer clients logins.
This might come in handy in a saturated environment where heavy Discoverer reports consume all the CPU and IO resources.. Instead of killing the Discoverer sessions or telling the clients to not to use their Discoverer reports during the peak hours, we can use this trigger to prevent the Discoverer client logins .. We can even let some domain users to use Discoverer while preventing the others..
We were using it in the year 2009, it was saving us those days.. Then we invented the limiter..(http://ermanarslan.blogspot.com.tr/p/self-developed-software.html#limiter) Later , we have used this trigger in conjuction with the storage replication.. We were redirecting some discoverer users to the replicated environment... Nowadays, we dont need this kind of solutions as the speed of the systems has increased . On the other hand; things like Resource Manager still works for the moderate hardware.. In Exadata we use IORM(although we did not need)..
Everyting aside, Sql tuning is always the best solution for this..
Anyways, just came to my mind to share it :)
/*Here we get the Os username and Program name from SYS CONTEXT and checking the allowed_users table, which has only one column (osuser) using the osuer we get from the sys_context.. If there is a row there, we let the user in.. If there is no rows, we reject the Discoverer client coming to the database from that Domain user... If we need to give an access right to a new user, we just add a row with the value of the relevant Osuser name , to the "allowed_users" table..*/
CREATE TABLE ALLOWED_USERS
(
OSUSER VARCHARR2(500)
);
/*Here we get the Os username and Program name from SYS CONTEXT and checking the allowed_users table, which has only one column (osuser) using the osuer we get from the sys_context.. If there is a row there, we let the user in.. If there is no rows, we reject the Discoverer client coming to the database from that Domain user... If we need to give an access right to a new user, we just add a row with the value of the relevant Osuser name , to the "allowed_users" table..*/
/*Just using the SYSTEM user create the allowed_users table and then create this trigger */
/*You can disable this trigger if you want to let all the users use Discoverer Client to connect to the database*/
/*Discover client version used by the clients in this example has a program name as dis51.exe, you can change it according to your environment*/
CREATE OR REPLACE TRIGGER DISCO_PREVENT_TRG
AFTER LOGON
ON DATABASE
DECLARE
v_osuser v$session.osuser%TYPE;
v_program v$session.program%TYPE;
v_count NUMBER;
CURSOR user_prog
IS
SELECT osuser, program
FROM v$session
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID');
BEGIN
OPEN user_prog;
FETCH user_prog
INTO v_osuser, v_program;
CLOSE user_prog;
SELECT COUNT (1)
INTO v_count
FROM APPS.allowed_users
WHERE UPPER (TRANSLATE (v_osuser, 'iiI', 'III')) =
UPPER (TRANSLATE (osuser, 'iiI', 'III'));
IF LOWER (v_program) LIKE ('%dis51%') AND v_count = 0
THEN
raise_application_error (-20001,'<<<<< You do not have the access right for using Discoverer in this environment.>>>>>');
END IF;
END;
This might come in handy in a saturated environment where heavy Discoverer reports consume all the CPU and IO resources.. Instead of killing the Discoverer sessions or telling the clients to not to use their Discoverer reports during the peak hours, we can use this trigger to prevent the Discoverer client logins .. We can even let some domain users to use Discoverer while preventing the others..
We were using it in the year 2009, it was saving us those days.. Then we invented the limiter..(http://ermanarslan.blogspot.com.tr/p/self-developed-software.html#limiter) Later , we have used this trigger in conjuction with the storage replication.. We were redirecting some discoverer users to the replicated environment... Nowadays, we dont need this kind of solutions as the speed of the systems has increased . On the other hand; things like Resource Manager still works for the moderate hardware.. In Exadata we use IORM(although we did not need)..
Everyting aside, Sql tuning is always the best solution for this..
Anyways, just came to my mind to share it :)
/*Here we get the Os username and Program name from SYS CONTEXT and checking the allowed_users table, which has only one column (osuser) using the osuer we get from the sys_context.. If there is a row there, we let the user in.. If there is no rows, we reject the Discoverer client coming to the database from that Domain user... If we need to give an access right to a new user, we just add a row with the value of the relevant Osuser name , to the "allowed_users" table..*/
CREATE TABLE ALLOWED_USERS
(
OSUSER VARCHARR2(500)
);
/*Here we get the Os username and Program name from SYS CONTEXT and checking the allowed_users table, which has only one column (osuser) using the osuer we get from the sys_context.. If there is a row there, we let the user in.. If there is no rows, we reject the Discoverer client coming to the database from that Domain user... If we need to give an access right to a new user, we just add a row with the value of the relevant Osuser name , to the "allowed_users" table..*/
/*Just using the SYSTEM user create the allowed_users table and then create this trigger */
/*You can disable this trigger if you want to let all the users use Discoverer Client to connect to the database*/
/*Discover client version used by the clients in this example has a program name as dis51.exe, you can change it according to your environment*/
CREATE OR REPLACE TRIGGER DISCO_PREVENT_TRG
AFTER LOGON
ON DATABASE
DECLARE
v_osuser v$session.osuser%TYPE;
v_program v$session.program%TYPE;
v_count NUMBER;
CURSOR user_prog
IS
SELECT osuser, program
FROM v$session
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID');
BEGIN
OPEN user_prog;
FETCH user_prog
INTO v_osuser, v_program;
CLOSE user_prog;
SELECT COUNT (1)
INTO v_count
FROM APPS.allowed_users
WHERE UPPER (TRANSLATE (v_osuser, 'iiI', 'III')) =
UPPER (TRANSLATE (osuser, 'iiI', 'III'));
IF LOWER (v_program) LIKE ('%dis51%') AND v_count = 0
THEN
raise_application_error (-20001,'<<<<< You do not have the access right for using Discoverer in this environment.>>>>>');
END IF;
END;
