RDBMS version: 11.2.0.4
In my production DB , i have a table named recon_data (shown below) in the application schema app_usr.
On an hourly basis, the application support team will send several batches of INSERT statements like below to the application DBAs which needs to be executed in app_usr.recon_data table.
On each batch, the application support team will send around 5 to 10 INSERT statements.
Each Application support team member has an individual DB user ID. But, these DB users should not be granted INSERT access directly due to security reasons.
So, I created the below stored procedure which will enable the application support team to do these INSERTs.
This stored proc is created with Definers right (Idea provided by Billy Verreyne in a community post for similair requirement).
So, the individual DB user id can execute the procedure owned by app_usr which will INSERT a row in RECON_DATA table in app_usr as shown below.
Since I am a beginner in PL/SQL and this stored proc is going to be deployed in a highly critical financial environment, could you guys suggest if there is any room for improvement in this stored proc . Like best practices..
Sample Table DDL , stored proc's code and sample data provided below . You can easily create these in a test environment
sqlplus app_usr/tiger
-- The table and procedure in the application schema.
create table recon_data
(
file_name VARCHAR2(2000) not null,
module_name VARCHAR2(2000) not null,
ems_number VARCHAR2(48) not null,
value VARCHAR2(2000) not null,
is_deleted VARCHAR2(1) not null,
mod_date_time TIMESTAMP(6) not null
);
create or replace procedure recon_insert
(
p_file_name in recon_data.file_name%type,
p_module_name in recon_data.module_name%type,
p_ems_number in recon_data.ems_number%type,
p_value in recon_data.value%type,
p_is_deleted in recon_data.is_deleted%type,
p_mod_date_time in recon_data.mod_date_time%type default sysdate
)
AUTHID definer
as
begin
INSERT INTO recon_data
(file_name,
module_name,
ems_number,
value,
is_deleted,
mod_date_time)
VALUES (
trim(p_file_name),
trim(p_module_name),
trim(p_ems_number),
trim(p_value),
trim(p_is_deleted),
p_mod_date_time );
commit;
end;
/
--- Application DB user is app_userid_7254
grant select on recon_data to app_userid_7254;
grant execute on recon_insert to app_userid_7254;
--- Now app_userid_7254 DB user can execute the procedure and INSERT the below row.
sqlplus app_userid_7254/tiger
So, instead of runng the below INSERT statement (which will fail any way bcoz app_userid_7254 doesn't insert privilege on RECON_DATA table in app schema)
INSERT INTO recon_data VALUES ('pallets:/acct/LGTMetaData/recon/CUSTOMERPARTY_BILLPROFILEID.recon','KBS_03','DMEBFA404FFC11E8BFBA03C600732558','362421474','N',SYSDATE);
he/she can run the below stored proc
begin
app_usr.recon_insert
(
p_file_name => 'pallets:/acct/LGTMetaData/recon/CUSTOMERPARTY_BILLPROFILEID.recon' ,
p_module_name => 'KBS_03' ,
p_ems_number => 'DMEBFA404FFC11E8BFBA03C600732558' ,
p_value => '362421474' ,
p_is_deleted => 'N' ,
p_mod_date_time => SYSDATE
);
end;
/
select * from app_usr.recon_data;