ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Provides a PL/SQL interface for accessing database functionality from Java.
Because this package contains 90 functions and procedures this page is not complete. Only the active subprogram links, below, have been documented here.
AUTHID
CURRENT_USER
Data Types
TYPE compiler_option_type IS RECORD(option_line VARCHAR2(128));
TYPE compiler_option_type_table IS TABLE OF compiler_option_type;
Grants Property Permissions. Create an active row in the policy table granting the Permission as specified to grantee/
This demo courtesy of Ralph Mintel. On was testing in 11.2.0.1 it was valid.
dbms_java.grant_permission(
grantee IN VARCHAR2,
permission_type IN VARCHAR2,
permission_name IN VARCHAR2,
permission_action IN VARCHAR2,
key OUT NUMBER)
AS LANGUAGE JAVA NAME 'oracle.aurora.rdbms.security.PolicyTableManager.grant(
java.lang.String, java.lang.String, java.lang.String, java.lang.String, long[])';
-- ==============================================
-- Oracle Java Functions
-- Oracle dba_java_policy
-- Variation on Tom Kyte's java system properties.
-- Ralph Mintel Mar. 21, 2008
-- ==============================================
col name format a40
col value format a60
col id format 999
-- =============================================
-- Function to retrieve all of the java properties.
-- =============================================
CREATE OR REPLACE FUNCTION jp RETURN VARCHAR2 AUTHID DEFINER IS LANGUAGE JAVA
NAME 'java.lang.System.getProperties()
return java.lang.String';
/
-- =============================================
-- the following will probably fail with ORA-29532:
-- Java call terminated by uncaught Java exception:
-- java.security.AccessControlException:
-- (java.util.PropertyPermission * read,write)
-- has not been granted to UWCLASS.
-- =============================================
SELECT get_java_system_property() FROM dual;
-- =============================================
-- Grant java PropertyPermission...
-- =============================================
conn sys@pdbdev as sysdba
-- =============================================
-- Back to our user...
-- =============================================
conn uwclass/uwclass@pdbdev
col name format a40
col value format a60
col id format 999
SELECT jp() FROM dual;
-- =============================================
-- Create a table for these java properties...
-- =============================================
CREATE TABLE java_properties (
rid NUMBER,
name VARCHAR2(40),
value VARCHAR2(660) DEFAULT NULL);
-- =============================================
-- Insert each java property name-value pair into a table.
--
-- The entire block of properties starts with '{'
-- and ends with '}'.
--
-- The property name=value sets are comma delimited.
-- '=' separates the name and value.
-- =============================================
set serveroutput on
DECLARE
s VARCHAR2(4000);
x PLS_INTEGER;
y PLS_INTEGER;
id PLS_INTEGER := 1;
lastone BOOLEAN := FALSE;
xname_size PLS_INTEGER;
xvalue_start PLS_INTEGER;
xvalue_size PLS_INTEGER;
BEGIN
SELECT jp() INTO s FROM dual;
x := 2; /* starting column in s, to ignore the opening { */
y := 1; /* column where comma separator is found. */
LOOP
y := INSTR(s, ',', x, 1);
IF (y < 1) THEN
y := INSTR(s, '}', x, 1);
lastone := true;
END IF;
IF (lastone) THEN
xvalue_size := INSTR(s, '}', xvalue_start, 1) - xvalue_start;
END IF;
IF (xvalue_size > 0) THEN
INSERT INTO java_properties
(rid, name, value)
VALUES (
id, SUBSTR(s, x, xname_size), SUBSTR(s, xvalue_start, xvalue_size));
ELSE
INSERT INTO java_properties (id, name)
(rid, name)
VALUES
(id, SUBSTR(s, x, xname_size));
END IF;
id := id + 1;
IF (lastone) THEN
EXIT;
END IF;
x := y + 2;
END LOOP;
END;
/
-- =============================================
-- A mystery...
-- The property 'user.region=US' disappeared.
-- It was there yesterday!
-- Is it on your system? Today? Tomorrow?
-- =============================================
SELECT * FROM java_properties ORDER BY name;
SELECT * FROM java_properties WHERE value IS NULL ORDER BY name;
set linesize 121
col grantee format a10
col type_schema format a5
col type_name format a30
col name format a20
col action format a20
col enabled format a10
SELECT * FROM dba_java_policy
WHERE grantee = 'UWCLASS';
SELECT COUNT(*) FROM dba_java_policy;
SELECT * FROM dba_java_policy;
-- =============================================
-- Back to our user...
-- =============================================
conn uwclass/uwclass@pdbdev
set pagesize 30
set linesize 121
col name format a40
col value format a60
col id format 999
set serveroutput on
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
-- =============================================
-- The following should fail because of revoked permission...
-- =============================================
SELECT jp() FROM dual;
-- =============================================
-- But we still have our java_properties table...
-- =============================================
Disables any permissions that might have been granted
dbms_java.revoke_permission(
grantee IN VARCHAR2,
permission_type IN VARCHAR2,
permission_name IN VARCHAR2,
permission_action IN VARCHAR2)
AS LANGUAGE JAVA NAME 'oracle.aurora.rdbms.security.PolicyTableManager.revoke(
java.lang.String, java.lang.String, java.lang.String, java.lang.String)';
dbms_java.set_execute_privilege(
object_name IN VARCHAR2,
object_schema IN VARCHAR2,
object_type IN VARCHAR2,
grantee_name IN VARCHAR2,
grant_if_nonero IN NUMBER);
Associate all database users with the OS account identified by credential pair 'osuser'/'ospass'. The association is in effect for any user that does not have credentials
Overload 1
dbms_java.set_runtime_exec_credentials(
dbuser IN VARCHAR2,
osuser IN VARCHAR2,
ospass IN VARCHAR2);
TBD
Overload 2
dbms_java.set_runtime_exec_credentials(
osuser IN VARCHAR2,
ospass IN VARCHAR2);
dbms_java.start_export(
short_name IN VARCHAR2,
schema IN VARCHAR2,
flags IN NUMBER,
type IN NUMBER,
properties OUT NUMBER,
raw_chunk_count OUT NUMBER,
total_raw_byte_count OUT NUMBER,
text_chunk_count OUT NUMBER,
total_text_byte_count OUT NUMBER)
RETURN NUMBER;