Skip to content

Instantly share code, notes, and snippets.

@PhilippSalvisberg

PhilippSalvisberg/Install_demo_apps.sql

Last active August 7, 2020 03:10
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save PhilippSalvisberg/052b1321b0207bd71f1f1b44eb03a8fc to your computer and use it in GitHub Desktop.
Save PhilippSalvisberg/052b1321b0207bd71f1f1b44eb03a8fc to your computer and use it in GitHub Desktop.
Download ZIP
Check if an Oracle user is ready to be used by the connection pool of the middle tier for applications guarding data behind a hard shell PL/SQL API as defined by Bryn Llewellyn. See also https://www.salvis.com/blog/2017/12/17/how-to-prove-that-your-smartdb-app-is-secured-against-sql-injection-attacks/
Raw
compile_missing_plscope_metadata.sql
/*
* Copyright 2017 Philipp Salvisberg <philipp.salvisberg@trivadis.com>
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
DECLARE
PROCEDURE enable_plscope IS
BEGIN
EXECUTE IMMEDIATE q'[ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL, STATEMENTS:ALL']';
END enable_plscope;
--
PROCEDURE compile_defs_without_plscope IS
BEGIN
<<compile_definition>>
FOR r IN (
WITH
-- application users in scope of the analysis
-- other users are treated as if they were not istalled
app_user AS (
SELECT username
FROM dba_users
WHERE oracle_maintained = 'N'
),
-- objects for which PL/Scope metadata is required
obj AS (
SELECT o.owner, o.object_type, o.object_name
FROM dba_objects o
WHERE object_name IN ('DBMS_UTILITY', 'OWA_UTIL')
AND object_type IN ('PACKAGE', 'SYNONYM')
UNION ALL
SELECT o.owner, o.object_type, o.object_name
FROM dba_objects o
JOIN app_user u ON u.username = o.owner
WHERE object_type IN ('PACKAGE BODY', 'TYPE BODY', 'FUNCTION',
'PROCEDURE', 'TRIGGER')
),
-- objects without PL/Scope metadata
missing_plscope_obj AS (
SELECT o.owner, o.object_type, o.object_name
FROM obj o
LEFT JOIN dba_identifiers i
ON i.owner = o.owner
AND i.object_type = o.object_type
AND i.object_name = o.object_name
AND i.usage_context_id = 0
WHERE i.usage_context_id IS NULL
),
-- all objects to recompile and (re)gather PL/Scope metadata
compile_scope AS (
SELECT o.owner, o.object_type, o.object_name
FROM obj o
WHERE EXISTS (
SELECT 1
FROM missing_plscope_obj o2
WHERE o2.owner = 'SYS'
)
UNION ALL
SELECT owner, object_type, object_name
FROM missing_plscope_obj
WHERE NOT EXISTS (
SELECT 1
FROM missing_plscope_obj o2
WHERE o2.owner = 'SYS'
)
),
-- compile statement required to produce PL/Scope metadata
compile_stmt AS (
SELECT 'ALTER ' || replace(object_type, ' BODY')
|| ' "' || owner || '"."' || object_name || '" COMPILE'
|| CASE
WHEN object_type LIKE '%BODY' THEN
' BODY'
END AS stmt
FROM compile_scope
)
-- main
SELECT stmt
FROM compile_stmt
) LOOP
EXECUTE IMMEDIATE r.stmt;
END LOOP compile_definition;
END compile_defs_without_plscope;
--
PROCEDURE recompile_invalids IS
BEGIN
<<schemas_with_invalids>>
FOR r IN (
SELECT DISTINCT owner
FROM dba_objects
WHERE status != 'VALID'
ORDER BY CASE owner
WHEN 'SYS' THEN
1
WHEN 'SYSTEM' THEN
2
ELSE
3
END,
owner
) LOOP
utl_recomp.recomp_serial(r.owner);
END LOOP schemas_with_invalids;
END recompile_invalids;
BEGIN
enable_plscope;
compile_defs_without_plscope;
recompile_invalids;
END;
/
Raw
Install_demo_apps.sql
/*
* Copyright 2017 Philipp Salvisberg <philipp.salvisberg@trivadis.com>
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
SET DEFINE OFF
SET SCAN OFF
SET ECHO OFF
PROMPT ======================================================================
PROMPT This script installs three demo applications for analysis via
PROMPT - is_connect_user_ready.sql
PROMPT - is_user_sql_injection_free.sql
PROMPT
PROMPT The applications are
PROMPT a) the_good - hard-shell PL/SQL API app using static SQL only
PROMPT table in THE_GOOD_DATA
PROMPT PL/SQL package in THE_GOOD_API
PROMPT access API via THE_GOOD_USER
PROMPT b) the_bad - hard-shell PL/SQL API app using dynamic SQL
PROMPT table in THE_BAD_DATA
PROMPT PL/SQL package in THE_BAD_API
PROMPT access API via THE_BAD_USER
PROMPT c) the_ugly - no hard-shell PL/SQL API
PROMPT table in THE_UGLY_USER
PROMPT access to table via THE_UGLY_USER
PROMPT
PROMPT This script must be run as SYS.
PROMPT Oracle Database 12.2 or higher is required.
PROMPT ======================================================================
PROMPT
PROMPT ======================================================================
PROMPT drop all demo users, if existing
PROMPT ======================================================================
PROMPT
BEGIN
<<demo_users>>
FOR r IN (
SELECT username
FROM dba_users
WHERE username IN (
'THE_GOOD_USER', 'THE_GOOD_API', 'THE_GOOD_DATA',
'THE_BAD_USER', 'THE_BAD_API', 'THE_BAD_DATA',
'THE_UGLY_USER', 'THE_UGLY_DATA'
)
) LOOP
EXECUTE IMMEDIATE 'DROP USER ' || r.username || ' CASCADE';
END LOOP demo_users;
END;
/
PROMPT ======================================================================
PROMPT THE GOOD
PROMPT ======================================================================
PROMPT
CREATE USER the_good_data identified by the_good_data
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO the_good_data;
CREATE TABLE the_good_data.t (
c1 INTEGER PRIMARY KEY,
c2 VARCHAR2(1000 CHAR) NOT NULL
);
INSERT INTO the_good_data.t(c1, c2) VALUES(1, 'Hello world!');
INSERT INTO the_good_data.t(c1, c2) VALUES(2, 'I like SQL.');
INSERT INTO the_good_data.t(c1, c2) VALUES(3, 'And JSON is part of SQL and PL/SQL.');
COMMIT;
CREATE USER the_good_api identified by the_good_api
TEMPORARY TABLESPACE temp;
GRANT CONNECT, CREATE PROCEDURE TO the_good_api;
GRANT SELECT, INSERT, UPDATE, DELETE ON the_good_data.t TO the_good_api;
CREATE OR REPLACE PACKAGE the_good_api.pkg AS
FUNCTION f1 (p_c1 INTEGER) RETURN VARCHAR2;
FUNCTION f2 (p_c2 VARCHAR2) RETURN CLOB;
END pkg;
/
CREATE OR REPLACE PACKAGE BODY the_good_api.pkg AS
FUNCTION f1 (p_c1 IN INTEGER) RETURN VARCHAR2 IS
l_result VARCHAR2(4000 BYTE);
BEGIN
SELECT JSON_OBJECT('c1' value c1, 'c2' value c2)
INTO l_result
FROM the_good_data.t
WHERE c1 = p_c1;
RETURN l_result;
END f1;
FUNCTION f2 (p_c2 IN VARCHAR2) RETURN CLOB IS
l_result CLOB;
l_c2 the_good_data.t.c2%TYPE;
BEGIN
l_c2 := p_c2;
SELECT JSON_ARRAYAGG(
JSON_OBJECT('c1' value c1, 'c2' value c2)
RETURNING CLOB
)
INTO l_result
FROM the_good_data.t
WHERE lower(c2) LIKE '%' || lower(l_c2) || '%';
RETURN l_result;
END f2;
END pkg;
/
CREATE USER the_good_user identified by the_good_user
TEMPORARY TABLESPACE temp;
GRANT CONNECT TO the_good_user;
GRANT EXECUTE ON the_good_api.pkg TO the_good_user;
PROMPT ======================================================================
PROMPT THE BAD
PROMPT ======================================================================
PROMPT
CREATE USER the_bad_data identified by the_bad_data
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO the_bad_data;
CREATE TABLE the_bad_data.t (
c1 INTEGER PRIMARY KEY,
c2 VARCHAR2(1000 CHAR) NOT NULL
);
INSERT INTO the_bad_data.t(c1, c2) VALUES(1, 'Hello world!');
INSERT INTO the_bad_data.t(c1, c2) VALUES(2, 'I like SQL.');
INSERT INTO the_bad_data.t(c1, c2) VALUES(3, 'And JSON is part of SQL and PL/SQL.');
COMMIT;
CREATE USER the_bad_api identified by the_bad_api
TEMPORARY TABLESPACE temp;
GRANT CONNECT, CREATE PROCEDURE TO the_bad_api;
GRANT SELECT, INSERT, UPDATE, DELETE ON the_bad_data.t TO the_bad_api;
GRANT EXECUTE ON dbms_sys_sql to the_bad_api; -- most dangerous package in the DB
GRANT CREATE DATABASE LINK TO the_bad_api;
CREATE OR REPLACE PACKAGE the_bad_api.pkg AS
FUNCTION f1 (p_c1 INTEGER) RETURN VARCHAR2;
FUNCTION f2 (p_c2 VARCHAR2) RETURN CLOB;
END pkg;
/
CREATE OR REPLACE PACKAGE BODY the_bad_api.pkg AS
FUNCTION f1 (p_c1 IN INTEGER) RETURN VARCHAR2 IS
co_sql_template CONSTANT CLOB := q'[
SELECT JSON_OBJECT('c1' value c1, 'c2' value c2)
FROM the_bad_data.t
WHERE c1 = :c1_bind
]';
l_result VARCHAR2(4000 BYTE);
BEGIN
EXECUTE IMMEDIATE co_sql_template INTO l_result USING p_c1;
RETURN l_result;
END f1;
FUNCTION f2 (p_c2 IN VARCHAR2) RETURN CLOB IS
co_sql_template CONSTANT CLOB := q'[
SELECT JSON_ARRAYAGG(
JSON_OBJECT('c1' value c1, 'c2' value c2)
RETURNING CLOB
)
FROM the_bad_data.t
WHERE lower(c2) LIKE '%' || lower(:c2_bind) || '%'
]';
l_result CLOB;
l_c2 the_bad_data.t.c2%TYPE;
BEGIN
l_c2 := p_c2;
EXECUTE IMMEDIATE co_sql_template INTO l_result USING l_c2;
RETURN l_result;
END f2;
END pkg;
/
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "THE_BAD_API"."C" AS
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.driver.OracleDriver;
public class C {
public static void m() throws SQLException {
Connection conn = new OracleDriver().defaultConnection();
Statement stmt = conn.createStatement();
stmt.executeUpdate("DELETE FROM the_bad_data.t WHERE c1 = 1");
conn.rollback();
}
}
/
CREATE OR REPLACE PACKAGE the_bad_api.pkg2 AS
PROCEDURE p1;
PROCEDURE p2;
PROCEDURE p3;
PROCEDURE p4;
PROCEDURE p5;
PROCEDURE p6;
PROCEDURE p7;
PROCEDURE p8 IS LANGUAGE JAVA NAME 'C.m()';
END pkg2;
/
CREATE OR REPLACE PACKAGE BODY the_bad_api.pkg2 AS
PROCEDURE p1 IS
co_sql_template CONSTANT CLOB := q'[
SELECT c1, c2
FROM the_bad_data.t
WHERE lower(c2) LIKE '%' || lower(:c2_bind) || '%'
]';
c SYS_REFCURSOR;
r the_bad_data.t%ROWTYPE;
BEGIN
OPEN c FOR co_sql_template USING 'SQL';
<<read_all>>
LOOP
FETCH c INTO r;
EXIT WHEN c%NOTFOUND;
END LOOP read_all;
CLOSE c;
END p1;
PROCEDURE p2 IS
c SYS_REFCURSOR;
r the_bad_data.t%ROWTYPE;
BEGIN
OPEN c FOR q'[
SELECT c1, c2
FROM the_bad_data.t
WHERE lower(c2) LIKE '%' || lower(:c2_bind) || '%'
]' USING 'SQL';
<<read_all>>
LOOP
FETCH c INTO r;
EXIT WHEN c%NOTFOUND;
END LOOP read_all;
CLOSE c;
END p2;
PROCEDURE p3 IS
l_cursor INTEGER;
l_rows INTEGER;
BEGIN
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(
l_cursor,
'DELETE FROM the_bad_data.t WHERE c1 = 1',
dbms_sql.native
);
l_rows := dbms_sql.execute(l_cursor);
dbms_sql.close_cursor(l_cursor);
ROLLBACK;
END p3;
PROCEDURE p4 IS
l_user_id INTEGER;
l_cursor INTEGER;
l_rows INTEGER;
BEGIN
EXECUTE IMMEDIATE q'[
SELECT user_id
FROM all_users
WHERE username = 'SYS'
]'
INTO l_user_id;
l_cursor := sys.dbms_sys_sql.open_cursor;
sys.dbms_sys_sql.parse_as_user(
l_cursor,
'GRANT SELECT ANY TABLE TO the_ugly_user',
dbms_sql.native,
l_user_id
);
sys.dbms_sys_sql.close_cursor(l_cursor);
END p4;
PROCEDURE p5 IS
l_instance VARCHAR2(1000 CHAR);
BEGIN
l_instance := dbms_assert.enquote_literal(
sys_context('userenv', 'instance_name')
);
dbms_utility.exec_ddl_statement('
CREATE DATABASE LINK loopback
CONNECT TO the_bad_api
IDENTIFIED BY the_bad_api
USING ' || l_instance
);
END p5;
PROCEDURE p6 IS
BEGIN
dbms_ddl.create_wrapped(q'[
CREATE OR REPLACE PROCEDURE the_bad_api.p IS
BEGIN
NULL;
END p;
]');
END p6;
PROCEDURE p7 IS
BEGIN
OWA_UTIL.LISTPRINT('SELECT c1, c2, NULL FROM the_bad_data.t', 'aName', 3);
END p7;
END pkg2;
/
CREATE USER the_bad_user identified by the_bad_user
TEMPORARY TABLESPACE temp;
GRANT CONNECT TO the_bad_user;
GRANT EXECUTE ON the_bad_api.pkg TO the_bad_user;
PROMPT ======================================================================
PROMPT THE UGLY
PROMPT ======================================================================
PROMPT
CREATE USER the_ugly_data identified by the_ugly_data
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
GRANT CONNECT, RESOURCE TO the_ugly_data;
CREATE TABLE the_ugly_data.t (
c1 INTEGER PRIMARY KEY,
c2 VARCHAR2(1000 CHAR) NOT NULL
);
INSERT INTO the_ugly_data.t(c1, c2) VALUES(1, 'Hello world!');
INSERT INTO the_ugly_data.t(c1, c2) VALUES(2, 'I like SQL.');
INSERT INTO the_ugly_data.t(c1, c2) VALUES(3, 'And JSON is part of SQL and PL/SQL.');
COMMIT;
CREATE USER the_ugly_user identified by the_ugly_user
TEMPORARY TABLESPACE temp;
GRANT CONNECT TO the_ugly_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON the_ugly_data.t TO the_ugly_user;
Raw
is_connect_user_ready.sql
/*
* Copyright 2017 Philipp Salvisberg <philipp.salvisberg@trivadis.com>
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
COLUMN username FORMAT A30
COLUMN owner FORMAT A13
COLUMN object_type FORMAT A12
COLUMN object_name FORMAT A20
COLUMN is_connect_user_ready FORMAT A21
COLUMN issue_count FORMAT 99,999,999
COLUMN not_recommended_object_use FORMAT A30
SET PAGESIZE 200
SET PAGESIZE 200
WITH
-- roles as recursive structure
role_base AS (
-- roles without parent (=roots)
SELECT r.role, NULL AS parent_role
FROM dba_roles r
WHERE r.role NOT IN (
SELECT p.granted_role
FROM role_role_privs p
)
UNION ALL
-- roles with parent (=children)
SELECT granted_role AS role, role AS parent_role
FROM role_role_privs
),
-- roles tree, calculate role_path for every hierarchy level
role_tree AS (
SELECT role,
parent_role,
sys_connect_by_path(ROLE, '/') AS role_path
FROM role_base
CONNECT BY PRIOR role = parent_role
),
-- roles graph, child added to all ancestors including self
-- allows simple join to parent_role to find all descendants
role_graph AS (
SELECT DISTINCT
role,
regexp_substr(role_path, '(/)(\w+)', 1, 1, 'i', 2) AS parent_role
FROM role_tree
),
-- application users in scope of the analysis
-- other users are treated as if they were not istalled
app_user AS (
SELECT username
FROM dba_users
WHERE oracle_maintained = 'N' -- SYS, SYSTEM, SYSAUX, ...
AND username NOT IN ('FTLDB', 'PLSCOPE')
),
-- user system privileges
sys_priv AS (
-- system privileges granted directly to users
SELECT u.username, p.privilege
FROM dba_sys_privs p
JOIN app_user u ON u.username = p.grantee
UNION
-- system privileges granted directly to PUBLIC
SELECT u.username, p.privilege
FROM dba_sys_privs p
CROSS JOIN app_user u
WHERE p.grantee = 'PUBLIC'
AND p.privilege NOT IN (
SELECT r.role
FROM dba_roles r
)
UNION
-- system privileges granted to users via roles
SELECT u.username, p.privilege
FROM dba_role_privs r
JOIN app_user u ON u.username = r.grantee
JOIN role_graph g ON g.parent_role = r.granted_role
JOIN dba_sys_privs p ON p.grantee = g.role
UNION
-- system privileges granted to PUBLIC via roles
SELECT u.username, p.privilege
FROM dba_role_privs r
JOIN role_graph g ON g.parent_role = r.granted_role
JOIN dba_sys_privs p ON p.grantee = g.role
CROSS JOIN app_user u
WHERE r.grantee = 'PUBLIC'
),
-- user object privileges
obj_priv AS (
-- objects granted directly to users
SELECT u.username, p.owner, p.type AS object_type, p.table_name AS object_name
FROM dba_tab_privs p
JOIN app_user u ON u.username = p.grantee
WHERE p.owner IN (
SELECT u2.username
FROM app_user u2
)
UNION
-- objects granted to users via roles
SELECT u.username, p.owner, p.type AS object_type, p.table_name AS object_name
FROM dba_role_privs r
JOIN app_user u ON u.username = r.grantee
JOIN role_graph g ON g.parent_role = r.granted_role
JOIN dba_tab_privs p ON p.grantee = g.role
WHERE p.owner IN (
SELECT u2.username
FROM app_user u2
)
-- objects granted to PUBLIC
UNION
SELECT u.username, p.owner, p.type AS object_type, p.table_name AS object_name
FROM dba_tab_privs p
CROSS JOIN app_user u
WHERE p.owner IN (
SELECT u2.username
FROM app_user u2
)
AND p.grantee = 'PUBLIC'
),
-- issues if user is configured in the connection pool of a middle tier
issues AS (
-- privileges not part of CONNECT role
SELECT username,
'SYS' AS owner,
'PRIVILEGE' AS object_type,
privilege AS object_name,
'Privilege is not part of the CONNECT role' AS issue
FROM sys_priv
WHERE privilege NOT IN ('CREATE SESSION', 'SET CONTAINER')
UNION ALL
-- access to non PL/SQL units
SELECT username,
owner,
object_type,
object_name,
'Access to non-PL/SQL unit'
FROM obj_priv
WHERE object_type NOT IN ('PACKAGE', 'TYPE', 'FUNCTION', 'PROCEDURE')
-- own objects
UNION ALL
SELECT u.username,
o.owner,
o.object_type,
o.object_name,
'Connect user must not own any object'
FROM app_user u
JOIN dba_objects o ON o.owner = u.username
-- missing CREATE SESSION privilege
UNION ALL
SELECT u.username,
'SYS',
'PRIVILEGE',
'CREATE SESSION',
'Privilege is missing, but required'
FROM app_user u
WHERE u.username NOT IN (
SELECT username
FROM sys_priv
WHERE privilege = 'CREATE SESSION'
)
),
-- aggregate issues per user
issue_aggr AS (
SELECT u.username, COUNT(i.username) issue_count
FROM app_user u
LEFT JOIN issues i ON i.username = u.username
GROUP BY u.username
),
-- user summary (calculate is_connect_user_ready)
summary AS (
SELECT username,
CASE
WHEN issue_count = 0 THEN
'YES'
ELSE
'NO'
END AS is_connect_user_ready,
issue_count
FROM issue_aggr
ORDER BY is_connect_user_ready DESC, username
)
-- main
SELECT * FROM summary;
Raw
is_user_sql_injection_free.sql
/*
* Copyright 2017 Philipp Salvisberg <philipp.salvisberg@trivadis.com>
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
COLUMN username FORMAT A30
COLUMN owner FORMAT A30
COLUMN object_name FORMAT A30
COLUMN is_user_sql_injection_free FORMAT A26
COLUMN issue_count FORMAT 99,999,999
COLUMN potential_sqli_risk FORMAT A40
SET PAGESIZE 200
WITH
app_user AS (
SELECT username
FROM dba_users
WHERE oracle_maintained = 'N'
),
obj AS (
SELECT o.owner, o.object_type, o.object_name
FROM dba_objects o
JOIN app_user u ON u.username = o.owner
WHERE object_type IN ('PACKAGE BODY', 'TYPE BODY', 'FUNCTION', 'PROCEDURE', 'TRIGGER')
),
missing_plscope_obj AS (
SELECT o.owner, o.object_type, o.object_name
FROM obj o
LEFT JOIN dba_identifiers i
ON i.owner = o.owner
AND i.object_type = o.object_type
AND i.object_name = o.object_name
AND i.usage_context_id = 0
WHERE i.usage_context_id IS NULL
),
stmt AS (
SELECT s.owner, s.object_type, s.object_name, s.type, s.line, s.col
FROM dba_statements s
JOIN app_user u ON u.username = s.owner
WHERE s.type IN ('EXECUTE IMMEDIATE', 'OPEN')
),
dep AS (
SELECT d.owner, d.name as object_name, d.type as object_type, d.referenced_name
FROM dba_dependencies d
JOIN app_user u ON u.username = d.owner
WHERE d.referenced_name IN (
'DBMS_SQL', 'DBMS_DDL', 'DBMS_HS_PASSTHROUGH', 'DBMS_SYS_SQL'
)
),
issues AS (
SELECT owner,
object_type,
object_name,
type AS potential_sqli_risk
FROM stmt
WHERE type = 'EXECUTE IMMEDIATE'
UNION
SELECT stmt.owner,
stmt.object_type,
stmt.object_name,
'OPEN-FOR WITH DYNAMIC SQL'
FROM stmt
JOIN dba_source src
ON src.owner = stmt.owner
AND src.type = stmt.object_type
AND src.name = stmt.object_name
AND src.line = stmt.line
WHERE stmt.type = 'OPEN'
AND regexp_substr(substr(src.text, stmt.col), '^open\s+', 1, 1, 'i') IS NULL
AND regexp_substr(substr(src.text, stmt.col), '^("?\w+"?|q?'')', 1, 1, 'i') IS NOT NULL
UNION
SELECT owner,
object_type,
object_name,
referenced_name
FROM dep
UNION
SELECT i.owner,
i.object_type,
i.object_name,
r.object_name || '.' || r.name
FROM dba_identifiers i
JOIN app_user u ON u.username = i.owner
JOIN dba_identifiers r
ON r.signature = i.signature
AND r.usage = 'DECLARATION'
WHERE i.usage = 'CALL'
AND r.owner = 'SYS'
AND r.object_type = 'PACKAGE'
AND (r.object_name, r.name) IN (
('DBMS_UTILITY', 'EXEC_DDL_STATEMENT'),
('OWA_UTIL', 'BIND_VARIABLES'),
('OWA_UTIL', 'LISTPRINT'),
('OWA_UTIL', 'TABLEPRINT')
)
UNION
SELECT o.owner,
o.object_type,
o.object_name,
'SQL FROM JAVA SUSPECTED'
FROM dba_objects o
JOIN app_user u ON u.username = o.owner
WHERE o.object_type = 'JAVA CLASS'
UNION
SELECT owner,
object_type,
object_name,
'PL/SCOPE METADATA MISSING'
FROM missing_plscope_obj
),
issue_aggr AS (
SELECT u.username AS owner, COUNT(i.owner) issue_count
FROM app_user u
LEFT JOIN issues i ON i.owner = u.username
GROUP BY u.username
),
summary AS (
SELECT owner,
CASE
WHEN issue_count = 0 THEN
'YES'
ELSE
'NO'
END AS is_user_sql_injection_free,
issue_count
FROM issue_aggr
ORDER BY is_user_sql_injection_free DESC, owner
)
-- main
SELECT * FROM summary;
Raw
public_table_view_privs.sql
/*
* Copyright 2017 Philipp Salvisberg <philipp.salvisberg@trivadis.com>
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
COLUMN owner FORMAT A17
COLUMN object_type FORMAT A11
WITH
public_privs AS (
SELECT p.owner,
p.type AS object_type,
p.privilege,
count(*) AS priv_count
FROM dba_tab_privs p
WHERE p.grantee = 'PUBLIC'
AND p.type IN ('VIEW', 'TABLE')
AND p.owner IN (
SELECT u2.username
FROM dba_users u2
WHERE u2.oracle_maintained = 'Y'
)
GROUP BY p.owner, p.type, p.privilege
),
public_privs_pivot AS (
SELECT owner,
object_type,
insert_priv,
update_priv,
delete_priv,
select_priv, -- allows SELECT ... FOR UPDATE ...
read_priv, -- does not allow SELECT ... FOR UPDATE ...
flashback_priv,
nvl(insert_priv,0) + nvl(update_priv,0) + nvl(delete_priv,0)
+ nvl(select_priv,0) + nvl(read_priv,0)
+ nvl(flashback_priv,0) AS total_priv
FROM public_privs
PIVOT (
sum(priv_count) FOR privilege IN (
'INSERT' AS insert_priv,
'UPDATE' AS update_priv,
'DELETE' AS delete_priv,
'SELECT' AS select_priv,
'READ' AS read_priv,
'FLASHBACK' AS flashback_priv
)
)
ORDER BY owner
),
public_privs_report AS (
SELECT owner,
object_type,
sum(insert_priv) AS "INSERT",
sum(update_priv) AS "UPDATE",
sum(delete_priv) AS "DELETE",
sum(select_priv) AS "SELECT",
sum(read_priv) AS "READ",
sum(flashback_priv) AS "FLASHBACK",
sum(total_priv) AS "TOTAL"
FROM public_privs_pivot
GROUP BY ROLLUP(owner, object_type)
HAVING (GROUPING(owner), GROUPING(object_type)) IN ((0,0), (1,1))
ORDER BY owner, object_type
)
-- main
SELECT * FROM public_privs_report;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment