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.
Without further specification bind variables are assumed to be of character type. Replace :b1 with TO_DATE(:b1) if working with date values: TO_NUMBER(:b1) if numeric values.
Data Dictionary Objects
DBA_HIST_SQLBIND
V$SQL_BIND_CAPTURE
V$SQL_BIND_METADATA
DBMS_ASSERT
V$SQL_BIND_DATA
WRH$_SQL_BIND_METADATA
Bind Variable Usage
set linesize 121
col sql_text format a100
SELECT sql_text
FROM gv$sql
WHERE sql_text LIKE '%:B%'
AND rownum < 21;
Bind Variable Values
col value_string format a60
SELECT DISTINCT hash_value, value_string
FROM gv$sql_bind_capture
WHERE rownum < 501
ORDER BY 1;
Bind Variable Demo
This demonstration is a merging of demos developed independently by Dan Morgan and Tom Kyte
conn / as sysdba
GRANT select ON gv_$statname TO uwclass;
GRANT select ON gv_$latch TO uwclass;
GRANT select ON gv_$sql TO uwclass;
GRANT select ON gv_$sqltext_with_newlines TO uwclass;
GRANT select ON v_$mystat TO uwclass;
GRANT alter system TO uwclass;
conn uwclass/uwclass
CREATE TABLE run_stats (
runid VARCHAR2(15),
name VARCHAR2(80),
value INT);
CREATE OR REPLACE VIEW stats AS
SELECT 'STAT...' || a.name NAME, b.value
FROM gv$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
UNION ALL
SELECT 'LATCH.' || NAME, gets
FROM gv$latch;
CREATE TABLE t (x INT);
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;
INSERT INTO run_stats
SELECT 'before', stats.*
FROM stats;
set timing on
-- not using bind variables
DECLARE
x NUMBER(10);
BEGIN
FOR i IN 1 .. 50000
LOOP
EXECUTE IMMEDIATE 'SELECT ' || i || ' INTO :b1 FROM dual'
INTO x;
END LOOP;
END;
/
INSERT INTO run_stats
SELECT 'after 1', stats.*
FROM stats;
-- using bind variables
DECLARE
x NUMBER(10);
BEGIN
FOR i IN 1 .. 50000
LOOP
EXECUTE IMMEDIATE 'SELECT :b1 FROM dual'
INTO x
USING i;
END LOOP;
END;
/
set timing off
INSERT INTO run_stats
SELECT 'after 2', stats.*
FROM stats;
col name format a35
SELECT a.name, b.value-a.value RUN1, c.value-b.value RUN2,
((c.value-b.value)-(b.value-a.value)) DIFF
FROM run_stats a, run_stats b, run_stats c
WHERE a.name = b.name
AND b.name = c.name
AND a.runid = 'before'
AND b.runid = 'after 1'
AND c.runid = 'after 2'
AND (c.value-a.value) > 0
AND (c.value-b.value) <> (b.value-a.value)
ORDER BY ABS((c.value-b.value)-(b.value-a.value));
SELECT sql_fulltext
FROM gv$sql s, gv$sqltext_with_newlines n
WHERE s.hash_value = n.hash_value
AND n.sql_text LIKE '%dual%'
ORDER BY last_active_time;
INSERT INTO user_table
(username, password)
VALUES
('Tom Kyte', 'top_secret_password');
COMMIT;
SELECT * FROM user_table;
-- not using bind variables: valid attempt
SQL> Accept Uname prompt "Enter username: "
Enter username: Tom Kyte
SQL> Accept Pword prompt "Enter pass: "
Enter pass: top_secret_password
SELECT COUNT(*)
FROM user_table
WHERE username = '&Uname'
AND password = '&Pword';
-- not using bind variables: SQL injection
SQL> Accept Uname prompt "Enter username: "
Enter username: Tom Kyte
SQL> Accept Pword prompt "Enter pass: "
Enter pass: i_dont_know ' or 'x ' ='x
SELECT COUNT(*)
FROM user_table
WHERE username = '&Uname'
AND password = '&Pword';
-- using bind variables
CREATE OR REPLACE PROCEDURE validate_user(uname VARCHAR2, upasswd VARCHAR2) AUTHID DEFINER IS
i NATURAL;
BEGIN
SELECT COUNT(*)
INTO i
FROM user_table
WHERE username = uname
AND password = upasswd;
IF i > 0 THEN
dbms_output.put_line('Access Granted');
ELSE
dbms_output.put_line('Access Denied');
END IF;
END validate_user;
/
INSERT INTO pwd (userid, password) VALUES (1, 'Ellison');
INSERT INTO pwd (userid, password) VALUES (2, 'Loaiza');
INSERT INTO pwd (userid, password) VALUES (3, 'Catz');
CREATE OR REPLACE PROCEDURE get_pwd(uid
IN pwd.userid%TYPE, tab IN user_tables.table_name%TYPE, retval OUT pwd.password%TYPE) AUTHID DEFINER IS
ret pwd.password%TYPE;
str CONSTANT VARCHAR2(60) := 'SELECT a.password FROM sqlobj a WHERE a.userid = :b1';
stmt CONSTANT VARCHAR2(90) := REPLACE(str, 'sqlobj', SYS.DBMS_ASSERT.SQL_OBJECT_NAME(tab));
BEGIN
dbms_output.put_line(stmt);
EXECUTE IMMEDIATE stmt INTO retval USING uid;
END get_pwd;
/
DECLARE
x VARCHAR2(200);
BEGIN
get_pwd(2, 'PWD', x);
dbms_output.put_line(x);
END;
/
DECLARE
x VARCHAR2(200);
BEGIN
get_pwd(2, 'BAD', x);
dbms_output.put_line(x);
END;
/