| Security Advisory |
Capture privileges used in Oracle defined PL/SQL packages.
The purpose of this project, #32973, is to capture privileges used for an operation. Privileges checked in the kernel(e.g, through KZP layer) have been collected.
However, many Oracle defined PL/SQL packages query privilege related dictionary tables/views(for example, session_privs, session_roles, sysauth$, objauth$, etc.) to check whether a user has a given privilege.
For such cases, APIs in this package have been used to replace original check. For queries that cannot be replaced, privileges are collected directly by calling dbms_priv_capture.capture_privilege_use. |
| |
| Recommended Security Rules |
NEVER
- Let any user or schema without documented justification or escalated privileges gain access to this package by revoking EXECUTE from PUBLIC
WITH GREAT CARE
- Identify legitimate requirements for access to this package and grant EXECUTE explicitly to only justified schemas
- Query the data dictionary after EXECUTE has been revoked from PUBLIC to verify the equivalence created is the equivalence approved by IT management and your CISO
CAUTIONS
- Some usage may be in the form of dynamic SQL so carefully verify usage requirements in source code as well as in DBA_DEPENDENCIES
|
| |
| How Oracle Works |
| Part of Oracle's security design is fine grained granting of object privileges. This package, in theory, assists in identifying granted privileges in use. |
Sounds like a great piece of code and something you would want to use to identify who has what privileges and to be able to correctly manage privilege allocation.
And you'd be correct.
But the ability to identify who has what privileges is also an asset to someone attempting an attack.
Be sure that this tool is only available to those that actually need it, audit its usage, and review the list no less frequently than once a year. |
| |
| DBMS_PRIV_CAPTURE Package Information |
| AUTHID |
CURRENT_USER |
| Dependencies |
| DBMS_AQADM_SYS |
DBMS_PARALLEL_EXECUTE |
KUPF$FILE |
| DBMS_CMP_INT |
DBMS_RULE_EXP_UTLI |
KUPP$PROC |
| DBMS_COMPARISON |
DBMS_SCHED_ARGUMENT_IMPORT |
KUPV$FT |
| DBMS_COMPRESSION |
DBMS_SCHED_JOB_EXPORT |
KUPW$WORKER |
| DBMS_CSX_ADMIN |
DBMS_SCHED_MAIN_EXPORT |
LBAC_EXP |
| DBMS_CUBE |
DBMS_SMB |
LBAC_SERVICES |
| DBMS_CUBE_ADVISE |
DBMS_SNAPSHOT_COMMON |
LBAC_SYSDBA |
| DBMS_DATAPUMP |
DBMS_SQLTUNE |
LOGMNR_EM_SUPPORT |
| DBMS_DATA_MINING |
DBMS_STATS |
LOGSTDBY_INTERNAL |
| DBMS_DDL |
DBMS_STATS_ADVISOR |
OLS_ENFORCEMENT |
| DBMS_EDITIONS_UTILITIES |
DBMS_STREAMS_ADM_UTL_INVOK |
PRIV_CAPTURE$ |
| DBMS_FILE_GROUP |
DBMS_STREAMS_PUB_RPC |
PRIV_PROFILE_LIB |
| DBMS_FILE_GROUP_IMP |
DBMS_TRANSFORM |
RDF_APIS |
| DBMS_HPROF |
DBMS_XDB_CONFIG |
ROLENAME_ARRAY |
| DBMS_ILM |
DRIACC |
ROLE_ARRAY |
| DBMS_LOGREP_IMP |
DRIIMP |
SDO_RDF |
| DBMS_LOGREP_UTIL_INVOK |
DRVDDL |
SEM_RDFSA_DR |
| DBMS_METADATA |
ISXMLTYPETABLE |
XS_DATA_SECURITY_UTIL |
|
| Documented |
No |
| First Available |
12.1.0 |
| Security Model |
Owned by SYS with EXECUTE granted to EXFSYS, LBACSYS, OLAPSYS, and XDB |
| Source |
{ORACLE_HOME}/rdbms/admin/catprofp.sql |
| Subprograms |
|
| |
| CAPTURE_PRIVILEGE_USE |
Capture a privilege usage, if a privilege capture condition is met. This procedure is called when a privilege is used in PL/SQL or JAVA.
Overload 1 |
dbms_priv_capture.capture_privilege_use(
userid IN NUMBER,
syspriv IN NUMBER DEFAULT NULL,
role IN VARCHAR2 DEFAULT NULL,
objpriv IN NUMBER DEFAULT NULL,
obj IN NUMBER DEFAULT NULL,
domain IN role_array DEFAULT NULL,
domain_str IN rolename_array DEFAULT NULL); |
| TBD |
| Overload 2 |
dbms_priv_capture.capture_privilege_use(
username IN VARCHAR2,
syspriv IN VARCHAR2 DEFAULT NULL,
role IN VARCHAR2 DEFAULT NULL,
objpriv IN VARCHAR2 DEFAULT NULL,
owner IN VARCHAR2 DEFAULT NULL,
object IN VARCHAR2 DEFAULT NULL,
domain IN role_array DEFAULT NULL,
domain_str IN rolename_array DEFAULT NULL); |
| TBD |
| |
| SES_HAS_OBJ_PRIV |
Checks whether the current user has a given object privilege
Appears to have a bug as this returns FALSE with every attempt to product
a result of True |
dbms_priv_capture.ses_has_obj_priv(
objpriv IN VARCHAR2,
objowner IN VARCHAR2,
objname IN VARCHAR2,
nmspace IN PLS_INTEGER DEFAULT 1)
RETURN BOOLEAN; |
conn sys@pdbdev as sysdba
BEGIN
IF dbms_priv_capture.ses_has_obj_priv('SELECT', 'UWCLASS', 'CDB_USERS') THEN
dbms_output.put_line('T');
ELSE
dbms_output.put_line('F');
END IF;
END;
/
F
PL/SQL procedure successfully completed.
GRANT select ON cdb_users TO uwclass;
Grant succeeded.
-- verify that the object privilege has been granted
SELECT grantee
FROM dba_tab_privs
WHERE table_name = 'CDB_USERS'
ORDER BY 1;
GRANTEE
------------------------------
ORACLE_OCM
SELECT_CATALOG_ROLE
UWCLASS
-- this procedure fails to produce an accurate response
BEGIN
IF dbms_priv_capture.ses_has_obj_priv('SELECT', 'UWCLASS', 'CDB_USERS') THEN
dbms_output.put_line('T');
ELSE
dbms_output.put_line('F');
END IF;
END;
/
F
-- in a separate SQL*Plus session log on as a user
-- create and run a procedure that uses the granted privilege
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE PROCEDURE test_proc AUTHID DEFINER IS
x INTEGER;
BEGIN
SELECT COUNT(*) INTO x FROM cdb_users;
dbms_output.put_line(x);
END test_proc;
/
Procedure created.
SQL> exec test_proc
53
PL/SQL procedure successfully completed. |
-- and the procedure still returns "F" so it looks very broken
-- so let's read the docs more carefully and try it in a package
CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER IS
PROCEDURE test_proc;
END test_pkg;
/
CREATE OR REPLACE PACKAGE BODY test_pkg IS
PROCEDURE test_proc IS
x INTEGER;
BEGIN
SELECT COUNT(*) INTO x FROM cdb_users;
dbms_output.put_line(x);
END test_proc;
END test_pkg;
/
SQL> exec test_proc
53
PL/SQL procedure successfully completed. |
-- and still this procedure returns an "F" for Failure which it appears to be |
| |
| SES_HAS_ROLE_PRIV |
| Determines whether the current user has a given role |
dbms_priv_capture.ses_has_role_priv(rolename IN VARCHAR2)
RETURN PLS_INTEGER; |
SELECT dbms_priv_capture.ses_has_role_priv('DBHADOOP')
FROM dual;
DBMS_PRIV_CAPTURE.SES_HAS_ROLE_PRIV('DBHADOOP')
-----------------------------------------------
0 |
| |
| SES_HAS_SYS_PRIV |
| Determines whether the current user has a given system privilege |
dbms_priv_capture.ses_has_sys_priv(systempriv IN VARCHAR2)
RETURN PLS_INTEGER; |
SELECT dbms_priv_capture.ses_has_sys_priv('CREATE TABLE')
FROM dual;
DBMS_PRIV_CAPTURE.SES_HAS_SYS_PRIV('CREATETABLE')
-------------------------------------------------
1 |