Very interesting application of data dictionary.

Hello Tom,

my requirement is that if a speific user logs in, am inserting a row into a table. So, am setting a logon trigger to do this work.
=======================
create or replace trigger logontrig
after logon on database
DECLARE
asid NUMBER;
aprogram VARCHAR2(48);
ausername VARCHAR2(30);
aserial NUMBER;
BEGIN
execute immediate 'select v.sid, v.program, v.username, v.serial#
from v$session v
where sid = ( select sid from v$mystat where rownum = 1 )' INTO asid, aprogram, ausername, aserial;

if ausername='SHRAV' THEN
execute immediate 'insert into shrav.shrav_log values (100)';
end if;
COMMIT;
END;
/
=====================

The trigger is created fine(logged in as user sys). Now, when I log into sqlplus as user 'SHRAV', the row is not getting inserted.

I do not believe the trigger is getting fired. I am stuck here, can you please guide me in the right direction in troubleshooting this issue.

Hello Tom,
I removed dynamic sql and implemented static SQL. Peeped into the alert.log and corrected the error. It works fine now. Thanks for the pointer. Yes, Shrav is a DBA. Why do you recommened against the use of dynamic sql in this situation? Extra overhead, resources ???

My real requirement on using these triggers is that when a session gets connected from a specific program, I need to modify the user sessions' Resource Consumer Group. The Resource Consumer Group, Plan and Plan directives are created on the db already.
My logic is when a user logs into the db, a logon trigger checks if its coming from a spf. program and then execute grant_switch_consumer_group, set_initial_consumer_group and set the RESOURCE_MANAGER_PLAN to the new_plan.

Similarly, on logoff, a trigger revokes the privileges, set_initial_consumer_group to default and resets the RESOURCE_MANAGER_PLAN to null.

====== logon trigger =====
create or replace trigger logontrig
after logon on database
DECLARE
asid NUMBER;
aprocess VARCHAR2(12);
aprogram VARCHAR2(48);
ausername VARCHAR2(30);
aosuser VARCHAR2(30);
aserial NUMBER;
BEGIN
select v.sid, v.process, v.program, v.username, v.osuser, v.serial# INTO asid, a
process, aprogram, ausername, aosuser, aserial
from v$session v
where sid = ( select sid from v$mystat where rownum = 1);

insert into shrav.shrav_log values(100); -- debug stt.

IF aprogram='jrew.exe' THEN
dbms_resource_manager_privs.grant_switch_consumer_group
(ausername, 'UI_RESOURCE_GROUP', TRUE );
dbms_resource_manager.set_initial_consumer_group
(ausername, 'UI_RESOURCE_GROUP' );

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

insert into shrav.shrav_log values(101); -- debug stt.
execute immediate 'alter system set resource_manager_plan = '||'UI_PLAN';

END IF;
END;
/
============
===== logoff trigger =====
create or replace trigger logouttrig
before logoff on database
DECLARE
asid NUMBER;
aprocess VARCHAR2(12);
aprogram VARCHAR2(48);
ausername VARCHAR2(30);
aosuser VARCHAR2(30);
aserial NUMBER;
BEGIN
select v.sid, v.process, v.program, v.username, v.osuser, v.serial# INTO asid, a
process, aprogram, ausername, aosuser, aserial
from v$session v
where sid = ( select sid from v$mystat where rownum = 1);
insert into shrav.shrav_log values(200); -- debug stt.

IF aprogram='jrew.exe' THEN
dbms_resource_manager_privs.revoke_switch_consumer_group
(ausername, 'UI_RESOURCE_GROUP');
dbms_resource_manager.set_initial_consumer_group
(ausername, 'DEFAULT_CONSUMER_GROUP' );

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

insert into shrav.shrav_log values(201); -- debug stt.
execute immediate 'alter system set resource_manager_plan = ''';
END IF;
END;
/
===============

This is my first stab at resource consumer groups. If there is any flaw in my logic and understanding, please let me know. I would greatly appreciate it.

Thanks much
-Sravan

Tom,
thanks for the info. on dynamic sql. I will try to read the book.

You are absolutely right, I would have that problem of the user loging in from non-spf program with the grant. Alternateively, I was going to try to set the RCG to a session instead of the user. That way, am controlling the grant at the session level and not the user level -

dbms_session.switch_current_consumer_group(newg,oldg,flag);

I will try this option.
Also, I have been getting the pending area not active error, ORA-27391. And when I run clear the pending area and create the pending area stts., then am getting the ORA-29370: pending area is already active. I went through all the articles on metalink on this error but to noavail. Am little lost, if you have seen this error before, pelase let me know.

-Sravan

Would it be possible to make a likewise function that checks if the user does not only have the role, but also checks if the role is currently active?

ops$tkyte%ORA10GR2> create or replace function is_role_active( p_name in varchar2 ) return varchar2
  2  authid current_user
  3  as
  4          l_str   varchar2(50);
  5  begin
  6          select 'Role ' || role || ' is active'
  7            into l_str
  8            from session_roles
  9           where role = upper(p_name);
 10
 11          return l_str;
 12  exception
 13          when no_data_found
 14          then
 15                  return 'Role ' || p_name || ' is not active';
 16  end;
 17  /

Function created.

ops$tkyte%ORA10GR2> create or replace procedure p
  2  as
  3  begin
  4       dbms_output.put_line( is_role_active( 'DBA' ) );
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA10GR2> exec p
Role DBA is not active

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_output.put_line( is_role_active( 'DBA' ) );
Role DBA is active

PL/SQL procedure successfully completed.

Hi tom

can we logoff a user forcely from a pl/sql block ?