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....AFM_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....AFM_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....AFM_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):SMITH
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....AFM_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....AFM_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):SMITH
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....AFM_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):SMITH

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....AFM_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....AFM_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):SMITH
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....AFM_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):SMITH

191.164.2.34\Sam...191.164.2.34....AFM_SECURE....AFM_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.

What do you mean by "how can I create my stored procedure"?

Could you please elaborate?

Quote:

Originally Posted by PAVB

What do you mean by "how can I create my stored procedure"?

Could you please elaborate?

I'd like to know what steps must follow to create my stored procedure.

you write the code - but you havent written anything useful here about what you actually want to do

Quote:

Originally Posted by raf

I'd like to know what steps must follow to create my stored procedure.

1- Write your storedproc in a file like yourfilename.sql
2- Compile your storedproc

Follows the basic syntax for your storedproc...

Code:

CREATE OR REPLACE PROCEDURE procedure_name IS BEGIN put your code logic here END procedure_name; /

...then log into sqlplus and do @yourfilename.sql

Quote:

Originally Posted by PAVB

1- Write your storedproc in a file like yourfilename.sql
2- Compile your storedproc

Follows the basic syntax for your storedproc...

Code:

CREATE OR REPLACE PROCEDURE procedure_name IS BEGIN put your code logic here END procedure_name; /

...then log into sqlplus and do @yourfilename.sql

Thanks but I don't Know write the code to update the column COUNTER with my conditions.

Have someone any idea?

2 Attachment(s)

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,POLICYNAME,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??

Exercise in futility

raf,

The main issue is that we see no purpose or need or stated requirement provided by you to justify querying and/or recording information from a SYStem table.

Maybe it's just an exercise in futility. Could't you just query the Oracle supplied DBA_FGA_AUDIT_TRAIL view?
:rolleyes: