Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose
Multiple APIs for writing application information to V$SESSION, monitoring application progress, and creating parameterized views.
SELECT schemaname, osuser, client_info
FROM gv$session
WHERE service_name NOT LIKE '%BACK%';
DECLARE
x VARCHAR2(100);
BEGIN
dbms_application_info.read_client_info(x);
dbms_output.put_line(x);
END;
/
-- the following will not work but try it so that you understand why
-- you can not use a stored procedure in a WHERE clause
CREATE OR REPLACE VIEW airplanes_view AS
SELECT *
FROM airplanes
WHERE program_id = dbms_application_info.read_client_info(x);
-- wrap the stored procedure so that it presents itself as a function
CREATE OR REPLACE FUNCTION app_info_wrapper RETURN VARCHAR2 IS
x VARCHAR2(64);
BEGIN
dbms_application_info.read_client_info(x);
RETURN x;
END app_info_wrapper;
/
-- now you can create the view
CREATE OR REPLACE VIEW airplanes_view AS
SELECT *
FROM airplanes
WHERE program_id = app_info_wrapper;
dbms_application_info.set_session_longops(
rindex IN OUT BINARY_INTEGER,
slno IN OUT BINARY_INTEGER,
op_name IN VARCHAR2(64) DEFAULT NULL,
target IN BINARY_INTEGER DEFAULT 0,
context IN BINARY_INTEGER DEFAULT 0,
sofar IN NUMBER DEFAULT 0,
totalwork IN NUMBER DEFAULT 0,
target_desc IN VARCHAR2(32) DEFAULT 'unknown_target',
units IN VARCHAR2(32) DEFAULT NULL);
rindex constant to start a new row
set_session_longops_nohint constant BINARY_INTEGER := -1;
use returned value from previous call to reuse a row
do not use slno ... for internal use by Oracle
target is the object number being worked on
sofar is any number indicating progress ... so far
totalwork a best guess as to the 100% value ... on completion
units used for sofar and totalwork
CREATE TABLE test (
testcol NUMBER(10));
-- Session 1
SELECT sid FROM v$mystat WHERE rownum = 1;
-- use this sid number in the session 2 query below
DECLARE
rindex BINARY_INTEGER := dbms_application_info.set_session_longops_nohint;
slno BINARY_INTEGER;
sofar NUMBER(6,2);
target BINARY_INTEGER;
totwork NUMBER := 100;
BEGIN
SELECT object_id
INTO target
FROM all_objects
WHERE object_name = 'TEST';
FOR i IN 1 .. totwork
LOOP
sofar := i;
dbms_application_info.set_session_longops(rindex, slno,
'MLIB', target, 0, sofar, 100, 'Pct Complete');
INSERT INTO test VALUES (i);
dbms_lock.sleep(0.25);
END LOOP;
COMMIT;
END;
/
-- Session 2 substitute the sid returned above from session 1
SELECT sid, serial#, schemaname
FROM gv$session;
SELECT start_time, sofar, totalwork, time_remaining, elapsed_seconds
FROM gv$session_longops
WHERE sid = 132
AND serial# = 1571;
FOR x IN 1..5
LOOP
FOR i IN 1 ..60
LOOP
INSERT INTO test VALUES (i);
COMMIT;
dbms_lock.sleep(1);
END LOOP;
act_name := 'deleting';
dbms_application_info.set_action(act_name);
FOR i IN 1 ..60
LOOP
DELETE FROM test WHERE testcol = i;
COMMIT;
dbms_lock.sleep(1);
END LOOP;
END LOOP;
END;
/
-- session 2
col module format a20
col action format a20
SELECT module, action
FROM gv$session;
SELECT module, action
FROM gv$sqlarea;
SELECT sql_text, disk_reads, module, action
FROM gv$sqlarea
WHERE action = 'deleting';