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.
Data Dictionary Objects
ALL_SOURCE_AE
DBA_SOURCE_AE
USER_SOURCE_AE
CDB_SOURCE_AE
SOURCE$
Objects Utilizing Autonomous Transactions
-- code can only be identified as being an autonomous transaction by reviewing its source
conn / as sysdba
SELECT DISTINCT owner, name
FROM dba_source
WHERE UPPER(text) LIKE '%AUTONOMOUS_TRANSACTION%'
ORDER BY 1, 2;
Related Privileges
CREATE PROCEDURE
CREATE TRIGGER
conn / as sysdba
GRANT create procedure TO uwclass;
Autonomous Transaction Demo 1
Without Pragma Autonomous Transaction
conn uwclass/uwclass
CREATE TABLE t (
test_value VARCHAR2(25));
CREATE OR REPLACE PROCEDURE child_block AUTHID CURRENT_USER IS
BEGIN
INSERT INTO t
(test_value)
VALUES
('Child block insert');
COMMIT;
END child_block;
/
CREATE OR REPLACE PROCEDURE parent_block AUTHID CURRENT_USER IS
BEGIN
INSERT INTO t
(test_value)
VALUES
('Parent block insert');
child_block;
ROLLBACK;
END parent_block;
/
-- run the parent procedure
exec parent_block
-- check the results
SELECT * FROM t;
With Pragma Autonomous Transaction
CREATE OR REPLACE PROCEDURE child_block AUTHID CURRENT_USER IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO t
(test_value)
VALUES
('Child block insert');
COMMIT;
END child_block;
/
-- empty the test table
TRUNCATE TABLE t;
-- run the parent procedure
exec parent_block;
-- check the results
SELECT * FROM t;
Autonomous Transaction Demo 2
Without Pragma Autonomous Transaction
CREATE TABLE t (testcol NUMBER);
CREATE OR REPLACE FUNCTION howmanyrows RETURN INTEGER AUTHID CURRENT_USER IS
i INTEGER;
BEGIN
SELECT COUNT(*)
INTO i
FROM t;
RETURN i;
END howmanyrows;
/
CREATE OR REPLACE PROCEDURE testproc AUTHID CURRENT_USER IS
a INTEGER;
b INTEGER;
c INTEGER;
BEGIN
SELECT COUNT(*)
INTO a
FROM t;
INSERT INTO t VALUES (1);
COMMIT;
INSERT INTO t VALUES (2);
INSERT INTO t VALUES (3);
b := howmanyrows;
INSERT INTO t VALUES (4);
INSERT INTO t VALUES (5);
INSERT INTO t VALUES (6);
COMMIT;
SELECT COUNT(*)
INTO c
FROM t;
dbms_output.put_line(a);
dbms_output.put_line(b);
dbms_output.put_line(c);
END testproc;
/
set serveroutput on
exec testproc
With Pragma Autonomous Transaction
CREATE OR REPLACE FUNCTION howmanyrows AUTHID DEFINER RETURN INTEGER IS
i INTEGER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT COUNT(*)
INTO i
FROM t;
