pl/sql user session control

pl/sql user session control

Post by DooHee Kim » Fri, 02 Jul 1999 04:00:00



I'm using pl/sql and oracle databse for online banking and need help in
controlling the user session.
The way the program works, once a user logs in and enters password
correctly, packet called usersession holds a temporary ID.

Every pages in online banking checks at beginning whether usersession
packet has an ID or not, if there is not, then the page writes that
session expired and if there is one, the page loads.
However, I can't get the ID to be available to every procedures.
The value is visible at the current procedures and the value is destroyed
when another page is loaded.

Can someone tell me what I'm doing wrong or if there is a better way to
controld a user session?  

 
 
 

1. Kill user sessions from PL/SQL

Hi!
I need to kill all user sessions from a stored proc.(I'm implementing an
advanced version of DROP USER command that would be able to drop a user
irrespective of existing user sessions )

Below it's shown what I do (but this does not work):

Create user Killer ....;
Grant DBA to Killer;
Grant Alter system to Killer;
Grant select any table to Killer;

connect Killer...;
-- a helper proc
Create Or Replace procedure
ExecSQL(s_sql in long)
as
c integer;
begin
  c := dbms_sql.open_cursor;
  Begin
    dbms_sql.parse(c, s_sql, dbms_sql.NATIVE );
  Exception When Others Then
   dbms_sql.close_cursor(c);
    raise;
  End;
  dbms_sql.close_cursor(c);
end;
/

-- my proc
CREATE OR REPLACE procedure pr_killusersessions(as_user in varchar2)
as
ls_sql long;
Begin
For rec In (select sid,serial#,osuser,program from v$session
            Where Upper(as_user) in (username, schemaname))
Loop
        ls_sql:= 'alter system kill session
'''||rec.sid||','||rec.serial#||'''';
        dbms_output.put_line(ls_sql);
        ExecSql(ls_sql);
End Loop;
Commit;
End;
/

-- then we're trying to kill all sessions of user TEST
SVRMGR>
set serveroutput on
Begin
 pr_KillUserSessions('test');
End;
/
Statement processed.
alter system kill session '11,6974'
SVRMGR>
-- there's no errors but the session wasn't killed
-- but if I just execute from console:
SVRMGR>alter system kill session '11,6974';
-- it will work
What did I miss of? Probably, the proc. owner does not have enough system
rights. But which?
Thanks.

Bye

2. Postgres95: retention

3. share PL/SQL package variable across sessions?

4. SQL Server 6.5 post SP5a

5. OAS Session variables (PL/SQL)

6. Access to SQL Server

7. Kill session from PL/SQL

8. Analysing / reporting software?

9. Anybody know howto alter session sort size from a PL/SQL stored procedure

10. Session-object in WebToolkit PL/SQL

11. help:alter session set current_schema does not work in pl/sql

12. pl/sql oracleweb session