Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: How Can I create my stored procedure?

    Hi,
    I've this table AFM_USERS.

    CREATE TABLE AFM_USERS
    (
    USER_PWD VARCHAR2(64 BYTE),
    USER_NAME VARCHAR2(64 BYTE),
    BAD_LOGIN NUMBER DEFAULT 0
    )

    USER_NAME.......USER_PSWD........COUNTER
    ADAMS.............ADAMS$............0
    JOHN..............JOHN$.............0
    SMITH.............SMITH$............0
    KOSTER............KOSTER$...........0
    ........................................
    ........................................

    I've my application that connect Oracle via ODBC.

    When I connect, for example, with USER_NAME=ADAMS PASSWORD=ADAMS$ into sys.fga_log$ table I get 2 records:

    OSUID.................OSHST......OBJ$SCHEMA....OBJ $NAME.....POLICYNAME.......TIMESTAMP.............. LSQLTEXT.......................................... .....................LSQLBIND
    191.164.2.34\Sam...191.164.2.34...AFM_SECURE....AF M_USERS....AFM_LOGIN..01/12/2008 7:15:23,7030..SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):ADAMS
    191.164.2.34\Sam...191.164.2.34.....AFM........AFM _MODS....AFM_MODS.....01/12/2008 7:15:26,7044....SELECT afm_module,button,afm_module_it FROM afm_mods.................................


    When I connect, for example, with USER_NAME=SMITH PASSWORD=SMITH$ into sys.fga_log$ table I get others 2 records:

    OSUID.................OSHST......OBJ$SCHEMA....OBJ $NAME.....POLICYNAME.....TIMESTAMP..............LS QLTEXT............................................ ...................LSQLBIND
    191.164.2.34\Sam...191.164.2.34....AFM_SECURE....A FM_USERS....AFM_LOGIN..01/12/2008 7:15:23,7030..SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):ADAMS
    191.164.2.34\Sam...191.164.2.34....AFM........AFM_ MODS.......AFM_MODS....01/12/2008 7:15:26,7044....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

    191.164.2.34\Sam...191.164.2.34...AFM_SECURE....AF M_USERS....AFM_LOGIN....01/12/2008 7:15:35,7070...SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5)MITH
    191.164.2.34\Sam...191.164.2.34....AFM........AFM_ MODS......AFM_MODS....01/12/2008 7:15:38,7067.....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

    When I connect, for example, with USER_NAME=SMITH PASSWORD=AAAA(Bad Password) into sys.fga_log$ table I get just one record:

    OSUID.................OSHST......OBJ$SCHEMA....OBJ $NAME.....POLICYNAME.....TIMESTAMP..............LS QLTEXT............................................ ...................LSQLBIND
    191.164.2.34\Sam...191.164.2.34....AFM_SECURE....A FM_USERS....AFM_LOGIN..01/12/2008 7:15:23,7030..SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):ADAMS
    191.164.2.34\Sam...191.164.2.34....AFM........AFM_ MODS.......AFM_MODS....01/12/2008 7:15:26,7044....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

    191.164.2.34\Sam...191.164.2.34...AFM_SECURE....AF M_USERS....AFM_LOGIN....01/12/2008 7:15:35,7070...SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5)MITH
    191.164.2.34\Sam...191.164.2.34....AFM........AFM_ MODS......AFM_MODS....01/12/2008 7:15:38,7067.....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

    191.164.2.34\Sam...191.164.2.34....AFM_SECURE....A FM_USERS....AFM_LOGIN..01/12/2008 7:16:01,7067.......SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5)MITH

    When I connect, for example, with USER_NAME=KOSTER PASSWORD=BBBBB(Bad Password) into sys.fga_log$ table I get just one record:

    OSUID.................OSHST......OBJ$SCHEMA....OBJ $NAME.....POLICYNAME.....TIMESTAMP..............LS QLTEXT............................................ ...................LSQLBIND
    191.164.2.34\Sam...191.164.2.34....AFM_SECURE....A FM_USERS....AFM_LOGIN..01/12/2008 7:15:23,7030..SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):ADAMS
    191.164.2.34\Sam...191.164.2.34....AFM........AFM_ MODS.......AFM_MODS....01/12/2008 7:15:26,7044....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

    191.164.2.34\Sam...191.164.2.34...AFM_SECURE....AF M_USERS....AFM_LOGIN....01/12/2008 7:15:35,7070...SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5)MITH
    191.164.2.34\Sam...191.164.2.34....AFM........AFM_ MODS......AFM_MODS....01/12/2008 7:15:38,7067.....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

    191.164.2.34\Sam...191.164.2.34....AFM_SECURE....A FM_USERS....AFM_LOGIN..01/12/2008 7:16:01,7067.......SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5)MITH

    191.164.2.34\Sam...191.164.2.34....AFM_SECURE....A FM_USERS....AFM_LOGIN...01/12/2008 7:17:04,7067.......SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):KOSTER

    Now I'd like to create a stored procedure on sys.fga_log$ table that increase or decrease of 1 the COUNTER column of table AFM_USERS with these conditions:

    if I connect not correctly (user o password incorrect) I increase COUNTER of 1 (+1)
    if I connect correctly (user and password correct) I decrease COUNTER of 1 (-1)

    In my case I'd like to get like this:

    When I connect with USER_NAME=ADAMS PASSWORD=ADAMS$ I'd like to get:

    execute my_stored_procedure;

    select *
    from AFM_USERS;

    USER_NAME.......USER_PSWD........COUNTER
    ADAMS.............ADAMS$.................-1 -- (-1)
    JOHN..............JOHN$......................0
    SMITH.............SMITH$...................0
    KOSTER............KOSTER$................0
    ........................................
    ........................................

    When I connect with USER_NAME=SMITH PASSWORD=SMITH$ I'd like to get:

    execute my_stored_procedure;

    select *
    from AFM_USERS;

    USER_NAME.......USER_PSWD........COUNTER
    ADAMS.............ADAMS$................-1
    JOHN..............JOHN$.....................0
    SMITH.............SMITH$.................-1 -- (-1)
    KOSTER............KOSTER$................0

    When I connect with USER_NAME=SMITH PASSWORD=AAAA(Bad Password) I'd like to get:

    execute my_stored_procedure;

    select *
    from AFM_USERS;

    USER_NAME.......USER_PSWD........COUNTER
    ADAMS.............ADAMS$................-1
    JOHN..............JOHN$.....................0
    SMITH.............SMITH$..................0 -- (+1)
    KOSTER............KOSTER$................0


    When I connect with USER_NAME=SMITH PASSWORD=BBBBB(Bad Password) I'd like to get:

    execute my_stored_procedure;

    select *
    from AFM_USERS;

    USER_NAME.......USER_PSWD........COUNTER
    ADAMS.............ADAMS$..............-1
    JOHN..............JOHN$...................0
    SMITH.............SMITH$................1 -- (+1)
    KOSTER............KOSTER$.............0

    When I connect with USER_NAME=KOSTER PASSWORD=BBBBB(Bad Password) I'd like to get:

    execute my_stored_procedure;

    select *
    from AFM_USERS;

    USER_NAME.......USER_PSWD........COUNTER
    ADAMS.............ADAMS$................-1
    JOHN..............JOHN$......................0
    SMITH.............SMITH$....................1
    KOSTER............KOSTER$.................1 -- (+1)


    How can I create my stored procedure?


    Thanks in advance.

  2. #2
    Join Date
    Jul 2002
    Posts
    227
    In attach I posted my 2 tables: AFM_USERS and FGA_LOG$

    I'd like to create a procedure like this:

    Create or replace procedure update_counter
    IS

    cursor c1 is
    select SESSIONID,DBUID,OSUID,OSHST,OBJ$SCHEMA,OBJ$NAME,PO LICYNAME,SCN,NTIMESTAMP#,PROCESS#,STATEMENT,ENTRYI D,LSQLBIND
    from FGA_LOG$;

    PROG NUMBER := 0;

    begin
    for a in c1 loop

    IF I connect not correctly then

    PROG := PROG + 1;

    UPDATE AFM_SECURE.AFM_USERS
    SET COUNTER = PROG
    WHERE USER_NAME = a.substr(LSQLBIND,instr(LSQLBIND,':')+1);

    END IF;

    IF I connect correctly then

    PROG := PROG - 1;

    UPDATE AFM_SECURE.AFM_USERS
    SET COUNTER = PROG
    WHERE USER_NAME = a.substr(LSQLBIND,instr(LSQLBIND,':')+1);

    END IF;

    end loop;
    commit;

    EXCEPTION
    WHEN OTHERS THEN
    raise;
    END;

    But I don't know how can I found the correct connection.

    When I connect correctly I get 2 records with OBJ$NAME = (AFM_USERS and AFM_MODS)
    and 2 records with POLICY_NAME = (AFM_LOGIN and AFM_MODS)

    When I connect not correctly I get ust one record with OBJ$NAME = AFM_USERS and POLICY_NAME = AFM_LOGIN

    Have someone any idea??
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •