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.
Note: Oracle allows developers of object-oriented applications to extend the list of built-in relational operators (for example, +, -, /, *, LIKE, AND, OR) with domain specific operators
(for example, Contains, Within_Distance, Similar) called user-defined operators. A user-defined operator can be used anywhere built-in operators can be used, for example, in the select list or the where clause.
Similar to built-in operators, a user-defined operator may support arguments of different types, and that it may be evaluated using an index. Similar to built-in operators, user-defined operators allow efficient content-based querying and sorting on object data.
Data Dictionary Objects
ALL_OPERATORS
CDB_OPERATOR_COMMENTS
OPERATOR$
ALL_OPERATOR_COMMENTS
DBA_OPERATORS
USER_OPERATORS
CDB_OPERATORS
DBA_OPERATOR_COMMENTS
USER_OPERATOR_COMMENTS
Object Privileges
Privileges to tables and views granted through roles may not be valid within a procedure. See the section on AUTHID.
GRANT execute ON <object_name> TO <user_name>;
System Privileges
ALTER ANY OPERATOR
CREATE OPERATOR
EXECUTE ANY OPERATOR
CREATE ANY OPERATOR
DROP ANY OPERATOR
Create Single Bind Operator
CREATE OR REPLACE OPERATOR <operator_name>
BINDING (data_type_in) RETURN <data_type_out> USING <function_name>;
CREATE OR REPLACE FUNCTION fn_contains(stringin VARCHAR2, valuein VARCHAR2)
RETURN NUMBER AUTHID CURRENT_USER IS
BEGIN
IF INSTR(stringin, valuein, 1, 1) = 0 THEN
RETURN 0;
END IF;
RETURN 1;
END fn_contains;
/
CREATE OR REPLACE OPERATOR contains
BINDING(VARCHAR2, VARCHAR2)
RETURN NUMBER USING fn_contains;
Simple Operator Demo Table And Data
CREATE TABLE optab (
test VARCHAR2(20));
INSERT INTO optab VALUES ('Dan Morgan');
INSERT INTO optab VALUES ('J Sweet');
INSERT INTO optab VALUES ('Liz Scott');
INSERT INTO optab VALUES ('4242 W Main Street');
INSERT INTO optab VALUES ('Capable');
COMMIT;
Single Bind Operator Demonstration
SELECT *
FROM optab
WHERE contains(test, 'a') = 1;
SELECT *
FROM optab
WHERE contains(test, 'Morgan') = 1;
SELECT *
FROM optab
WHERE contains(test, ' ') = 1;
Multiple Binding Operator Demo
Multiple Binding Operator Creation
CREATE OR REPLACE OPERATOR contains
BINDING(data_type_in) RETURN <data_type_out>
USING <function_name>,
(data_type_in) RETURN <data_type_out>
USING <function_name>;
CREATE OR REPLACE FUNCTION fn_int_contains(numbin NUMBER, valuein NUMBER)
RETURN NUMBER AUTHID DEFINER IS
numinstr VARCHAR2(100);
valinstr VARCHAR2(100);
BEGIN
numinstr := TO_CHAR(numbin);
valinstr := TO_CHAR(valuein);
IF INSTR(numinstr, valinstr, 1, 1) = 0 THEN
RETURN 0;
END IF;
RETURN 1;
END fn_int_contains;
/
CREATE OR REPLACE FUNCTION fn_both_contains(stringin VARCHAR2, numbin NUMBER)
RETURN NUMBER AUTHID DEFINER IS
numinstr VARCHAR2(100);
BEGIN
numinstr := TO_CHAR(numbin);
IF INSTR(stringin, numinstr, 1, 1) = 0 THEN
RETURN 0;
END IF;
RETURN 1;
END fn_both_contains;
/
CREATE OR REPLACE OPERATOR contains BINDING
(VARCHAR2, VARCHAR2) RETURN NUMBER
USING fn_contains,
(NUMBER, NUMBER) RETURN NUMBER
USING fn_int_contains;
Multiple Bindings Operator Demo Table And Data
CREATE TABLE numtab (
test NUMBER(10));
INSERT INTO numtab VALUES (000010000);
INSERT INTO numtab VALUES (213567);
INSERT INTO numtab VALUES (9835456);
INSERT INTO numtab VALUES (27334);
COMMIT;
Multiple Binding Operator Demonstration
SELECT *
FROM optab
WHERE contains(test, 'a') = 1;
SELECT *
FROM optab
WHERE contains(test, 'Morgan') = 1;
SELECT *
FROM optab
WHERE contains(test, ' ') = 1;
SELECT *
FROM numtab
WHERE contains(test, 1) = 1;
SELECT *
FROM numtab
WHERE contains(test, 35) = 1;
ALTER OPERATOR <operator_name>
ADD BINDING <input_parameters> RETURN <data_type>
USING <function_name>;
CREATE OR REPLACE FUNCTION fn_both_contains(
stringin VARCHAR2, numbin NUMBER) RETURN NUMBER AUTHID CURRENT_USER IS
numinstr VARCHAR2(100);
BEGIN
numinstr := TO_CHAR(numbin);
IF INSTR(stringin, numinstr, 1, 1) = 0 THEN
RETURN 0;
END IF;
RETURN 1;
END fn_both_contains;
/
ALTER OPERATOR contains
ADD BINDING (VARCHAR2, NUMBER) RETURN NUMBER
USING fn_both_contains;
SELECT *
FROM optab
WHERE contains(test, 42) = 1;
Alter Operator Compile
Recompile
ALTER OPERATOR <operator_name> COMPILE [REUSE SETTINGS];
ALTER OPERATOR contains COMPILE;
Comment Operator
Comment an operator
COMMENT ON OPERATOR <operator_name> IS '<comment_text>';
COMMENT ON OPERATOR contains IS 'This is a user created operator';
set linesize 131
col comments format a60
SELECT *
FROM user_operator_comments;
Drop Operator
Drop Operator
DROP OPERATOR <operator_name>;
DROP OPERATOR contains;
And Not Demo
The AndNot Operator is intended to check a string or number to see if it contains one nested element but does not contain a second nested element.
Demo Table & Data
CREATE TABLE ant (
memo_fld VARCHAR2(100));
INSERT INTO ant VALUES
('The quick brown fox jumped over the lazy dogs.');
INSERT INTO ant VALUES
('I feel a lot more like I do now than I did at 11 last night.');
INSERT INTO ant VALUES
('There are three erors in this statment. True or false?');
INSERT INTO ant VALUES
('There are only 10 kinds of people in the world. Those who understand binary and those who don''t.');
INSERT INTO ant VALUES ('520-34-5678');
INSERT INTO ant VALUES ('206-555-1212');
COMMIT;
Function For String Handling
CREATE OR REPLACE FUNCTION AndNotStr (
evalstr VARCHAR2,
str1in VARCHAR2,
str2in VARCHAR2)
RETURN NUMBER IS
x BOOLEAN := FALSE;
NoGood EXCEPTION;
BEGIN
IF INSTR(evalstr, str1in, 1, 1) = 0 THEN
RAISE NoGood;
END IF;
IF INSTR(evalstr, str2in, 1, 1) > 0 THEN
RAISE NoGood;
END IF;
RETURN 1;
EXCEPTION
WHEN NoGood THEN
RETURN 0;
END AndNotStr;
/
Queries To Test String Handling Function
SELECT AndNotStr('Daniel Morgan', 'an', 'or') FROM dual;
SELECT AndNotStr('Daniel Morgan', 'an', 'bb') FROM dual;
Function For Number Handling
CREATE OR REPLACE FUNCTION AndNotNum (
evalnum NUMBER,
num1in NUMBER,
num2in NUMBER)
RETURN NUMBER IS
BEGIN
evalstr := TO_CHAR(evalnum);
num1str := TO_CHAR(num1in);
num2str := TO_CHAR(num2in);
IF INSTR(evalstr, num1str, 1, 1) = 0 THEN
RAISE NoGood;
END IF;
IF INSTR(evalstr, num2str, 1, 1) > 0 THEN
RAISE NoGood;
END IF;
RETURN 1;
EXCEPTION
WHEN NoGood THEN
RETURN 0;
END AndNotNum;
/
Queries To Test Number Handling Function
SELECT AndNotNum(1003402, 34,10) FROM dual;
SELECT AndNotNum(1003402, 34,11) FROM dual;
AndNot Operator
CREATE OR REPLACE OPERATOR AndNot
BINDING (VARCHAR2, VARCHAR2, VARCHAR2)
RETURN NUMBER USING AndNotStr,
(NUMBER, NUMBER, NUMBER)
RETURN NUMBER USING AndNotNum;
Test Operator
SELECT * FROM ant WHERE andnot(memo_fld, 'dog', 'cat') = 1;
SELECT * FROM ant WHERE andnot(memo_fld, 'are', 'dog') = 1;
SELECT * FROM ant WHERE andnot(memo_fld, '10', '11') = 1;
SELECT * FROM ant WHERE andnot(memo_fld, '0', '11') = 1;
