Oracle added in 12c the ACCESSIBLE BY clause that added a similar idea to the PL/SQL language. So we can define a piece of PL/SQL and limit where that piece of PL/SQL can be called from with the ACCESSIBLE BY clause. This is a great feature BUT my method still has some advantages as we have more control by using the call stack in our own code so we can even check the line number we are calling from. This is good as it can help enforce that the caller has not been modified as the check is in the callee.
The main advantage of ACCESSIBLE BY though is that it is baked into the PL/SQL as part of the language whereas my method is code in user space.
There are advantages for each method.
The earlier versions of this syntax were limited to the object level but we can now add these clauses for individual procedures and functions in a package now.
There is no dictionary data for what has been set as far as I can tell. So if we need to analyse the PL/SQL in a database for use of the ACCESSIBLE BY clause then we need to parse the PL/SQL source code and extract the use of this syntax. I have created a simple script that does this and its use is here in 23c:
SQL> @sc_access
Owner Name Type Sub-Object Line Accessible List
================== ======================= ================== ================== ======== =================================================
SYS CDBVIEW_INTERNAL PACKAGE . 2 PACKAGE SYS.CDBVIEW
SYS DBMS_SQLTUNE_UTIL0 PACKAGE CHECK_DV_ACCESS 431 PACKAGE SYS.DBMS_SQLTUNE
SYS DBMS_SQLTUNE_UTIL1 PACKAGE GET_SEQ_REMOTE 697 PACKAGE SYS.DBMS_SQLTUNE_INTERNAL
SYS DBMS_RULE_INTERNAL PACKAGE I_EVALUATE 46 PACKAGE DBMS_CHAIN_INVOKER
SYS DBMS_PLUGTS PACKAGE GETDEBUGENABLE 121 PACKAGE SYS.DBMS_PLUGTSP
SYS DBMS_PLUGTS PACKAGE KCP_SETPIRENA 529 PACKAGE SYS.KUPW$WORKER
SYS DBMS_TTS PACKAGE PUT_PROTECTED_TSE_KEY 289 PACKAGE SYS.KUPW$WORKER
SYS DBMS_TTS PACKAGE GET_AFN_DBID 310 PACKAGE SYS.KUPW$WORKER
SYS DBMS_TTS PACKAGE GET_AFN_DBIDXENDIAN 331 PACKAGE SYS.KUPW$WORKER
SYS DBMS_TTS PACKAGE CONVERTENCRYPTEDDATAFILECOPY 386 PACKAGE SYS.KUPW$WORKER
SYS UTL_XML PACKAGE PARSEEXPR 129 PACKAGE SYS.DBMS_METADATA
SYS UTL_XML PACKAGE PARSEQUERY 143 PACKAGE SYS.DBMS_METADATA
SYS UTL_XML PACKAGE XMLPARSE 206 PACKAGE SYS.DBMS_METADATA_INT
SYS UTL_XML PACKAGE ISNAMEOMF 293 PACKAGE SYS.DBMS_METADATA_UTIL
SYS UTL_XML PACKAGE SSCPARSE 382 PACKAGE SYS.DBMS_METADATA_INT
GSMADMIN_INTERNAL DBMS_GSM_DBADMIN PACKAGE GETSHARDINGMETHOD 72 PACKAGE SYS.DBMS_GSM_FIXED
SYS DBMS_METADATA_INT PACKAGE ADD_TRANSFORM 298 PACKAGE SYS.DBMS_METADATA
SYS DBMS_METADATA_UTIL PACKAGE SET_MARKER 818 PACKAGE SYS.DBMS_METADATA_INT
SYS DBMS_UPG_JSON0 PACKAGE . 2 PACKAGE SYS.DBMS_UPG_JSON
SYS DBMS_UPG_JSON_INT PACKAGE . 2 PACKAGE SYS.DBMS_UPG_JSON, SYS.DBMS_SODA
CTXSYS DRIACCHELP PACKAGE . 2 PACKAGE DRIACC
CTXSYS DRIOPT PACKAGE GET_P_INV_COUNT 10 PACKAGE CTXSYS.DRVDDL
CTXSYS DRIOPT PACKAGE SET_P_INV_COUNT 14 PACKAGE CTXSYS.DRVDDL
CTXSYS DRIOPT PACKAGE RESET_AGGMERGE_HANDLE 215 PACKAGE CTXSYS.DRVDML
CTXSYS DRIXMD PACKAGE SETUPXMLSEARCHPREFS 1103 PACKAGE CTXSYS.DRIXMD
CTXSYS DRIXMD PACKAGE INSERT_INDEX 1445 PACKAGE CTXSYS.DRIIMP
CTXSYS DRIXMD PACKAGE INSERT_PARTITION 1466 PACKAGE CTXSYS.DRIIMP
CTXSYS DRIXMD PACKAGE INSERT_OBJECT 1473 PACKAGE CTXSYS.DRIIMP
CTXSYS DRIXMD PACKAGE INSERT_VALUE 1479 PACKAGE CTXSYS.DRIIMP
CTXSYS DRIXMD PACKAGE INSERT_SUB_VALUE 1488 PACKAGE CTXSYS.DRIIMP
CTXSYS DRIXMD PACKAGE INSERT_PENDING 1494 PACKAGE CTXSYS.DRIIMP
CTXSYS DRIXMD PACKAGE INSERT_CDICOL 1507 PACKAGE CTXSYS.DRIIMP
CTXSYS DRIXMD PACKAGE INSERT_IDX_DICTIONARY 1513 PACKAGE CTXSYS.DRIIMP
CTXSYS DRIXMD PACKAGE INSERT_USER_EXTRACT_RULE 1526 PACKAGE CTXSYS.DRIIMP
CTXSYS DRIXMD PACKAGE INSERT_USER_EXTRACT_TYPE 1534 PACKAGE CTXSYS.DRIIMP
CTXSYS DRIXMD PACKAGE VALIDATE_ATTR 1549 PACKAGE DRIVAL, PROCEDURE VALIDATE_ATTR
CTXSYS DRIXMD PACKAGE GETOBJECTATTID 1566 PACKAGE CTXSYS.DRIXMD
CTXSYS DRIXMD PACKAGE SECONDARYINDEXEXISTS 1574 PACKAGE CTXSYS.DRVXTAB
CTXSYS DRIXMD PACKAGE GETWALLETPATH 1599 PACKAGE CTXSYS.DRVDML
CTXSYS DRITHSC PACKAGE SET_PV_LAST_ID 368 PACKAGE CTX_THES
CTXSYS DRIUTL PACKAGE PARSE_OBJECT_NAME 38 PACKAGE DRVUTL, PROCEDURE PARSE_OBJECT_NAME
CTXSYS DRVDDL PACKAGE GET_PV_IDX_MEM 937 TYPE TEXTINDEXMETHODS, TYPE RULEINDEXMETHODS
CTXSYS DRVDDL PACKAGE SET_PV_IDX_MEM 942 TYPE TEXTINDEXMETHODS, TYPE RULEINDEXMETHODS, TYPE XPATHINDEXMETHODS
CTXSYS DRVDDL PACKAGE GET_PV_IDX_SYNC_TYPE 946 TYPE TEXTINDEXMETHODS, TYPE RULEINDEXMETHODS
CTXSYS DRVDDL PACKAGE SET_PV_IDX_SYNC_TYPE 951 TYPE TEXTINDEXMETHODS, TYPE RULEINDEXMETHODS, TYPE XPATHINDEXMETHODS
CTXSYS DRVDDL PACKAGE GET_PV_IS_IMPORT 955 TYPE TEXTINDEXMETHODS, TYPE RULEINDEXMETHODS, TYPE XPATHINDEXMETHODS
CTXSYS DRVDDL PACKAGE SET_PV_IS_IMPORT 960 TYPE TEXTINDEXMETHODS, TYPE RULEINDEXMETHODS, TYPE XPATHINDEXMETHODS
CTXSYS DRVDDL PACKAGE GET_PV_MASTER_PARAMS 964 TYPE TEXTINDEXMETHODS
CTXSYS DRVDDL PACKAGE SET_PV_MASTER_PARAMS 969 TYPE TEXTINDEXMETHODS, TYPE RULEINDEXMETHODS, TYPE XPATHINDEXMETHODS
CTXSYS DRVDDL PACKAGE GET_PV_PARTITION_POPULATE 973 TYPE TEXTINDEXMETHODS, CTXSYS.DRIPARSE
CTXSYS DRVDDL PACKAGE SET_PV_PARTITION_POPULATE 978 TYPE TEXTINDEXMETHODS
CTXSYS DRVDDL PACKAGE GET_PV_TABLESPACE 982 TYPE TEXTINDEXMETHODS
CTXSYS DRVDDL PACKAGE SET_PV_TABLESPACE 987 TYPE TEXTINDEXMETHODS
CTXSYS DRVDDL PACKAGE GET_OPT_REBUILD_ERROR_TEXT_COUNT 991 PACKAGE CTXSYS.DRVXMD
CTXSYS DRVDDL PACKAGE SET_OPT_REBUILD_ERROR_TEXT 997 PACKAGE CTXSYS.DRVXMD
CTXSYS DRVDDL PACKAGE SET_OPT_REBUILD_ERROR_TYPE 1003 PACKAGE CTXSYS.DRVXMD
CTXSYS DRVDDL PACKAGE REMOVESEARCHON 1011 TYPE TEXTINDEXMETHODS
CTXSYS DRVODM PACKAGE SVM_TRAIN 59 PACKAGE CTXSYS.CTX_CLS
CTXSYS DRVXMD PACKAGE INDEXHASPATHHASHINDEX 439 PACKAGE CTXSYS.DRVXTAB
SYS WWV_DBMS_SQL_APEX_220200 PACKAGE . 3 PACKAGE APEX_220200.WWV_FLOW_CODE_EXEC_MLE,PACKAGE APEX_220200.WWV_FLOW_DYNAMIC_EXEC,PACKAGE APEX_220200.WWV_FLOW_SESSION_RAS
APEX_220200 WWV_FLOW_CODE_EXEC_MLE PACKAGE . 3 PACKAGE WWV_FLOW_CODE_EXEC
APEX_220200 WWV_FLOW_CODE_EXEC_PLSQL PACKAGE . 3 PACKAGE WWV_FLOW_CODE_EXEC
APEX_220200 WWV_FLOW_APP_INSTALL_INT PACKAGE . 3 PACKAGE WWV_FLOW_IMP_PARSER
APEX_220200 WWV_FLOW_REGION_LIST PACKAGE . 3 PACKAGE WWV_FLOW_REGION_NATIVE
APEX_220200 WWV_FLOW_ZIP_INT PACKAGE . 1 PACKAGE WWV_FLOW_ZIP
APEX_220200 WWV_FLOW_ADVISOR_CHECKS_I PACKAGE . 3 PACKAGE WWV_FLOW_ADVISOR_CHECKS_API
APEX_220200 WWV_FLOW_SPATIAL_INT PACKAGE . 3 PACKAGE WWV_FLOW_SPATIAL_API
PL/SQL procedure successfully completed.
SQL>
This shows the lines of code that have ACCESSIBLE BY and also the object name, owner and procedure or function if it's a package. It also shows the line number for the ACCESSIBLE BY clause and also the allowed caller list.
There are some limits to this script and its naive at this stage. I extract the package procedure and function names from the source and match to the line number of the ACCESSIBLE BY clause. There are probably some bugs in it and it doesn't handle the case of wrapped code should the package header be wrapped. It has also only been tested on the default 23c Free install. But its a useful start point to see what PL/SQL code already uses this clause. it doesn't handle perfectly the case where the ACCESSIBLE BY is on the same line as the PROCEDURE or FUNCTION and also doesn't handle multi line cases.
Its a simple script and here it is:
-- -----------------------------------------------------------------------------
-- WWW.PETEFINNIGAN.COM LIMITED
-- -----------------------------------------------------------------------------
-- Script Name : sc_access.sql
-- Author : Pete Finnigan
-- Date : December 2023
-- -----------------------------------------------------------------------------
-- Description : This script checks for ACCESSIBKLE BHY clauses
-- -----------------------------------------------------------------------------
-- Maintainer : Pete Finnigan (http://www.petefinnigan.com)
-- Copyright : Copyright (C) 2023 PeteFinnigan.com Limited. All rights
-- reserved. All registered trademarks are the property of their
-- respective owners and are hereby acknowledged.
-- -----------------------------------------------------------------------------
-- License : This software is licensed to you by PeteFinnigan.com Limited. All
-- rights and ownership and copyright in the software are retained by
-- PeteFinnigan.com Limited in all possible cases. Posession of this
-- software does not infer any additonal rights. If you receive this
-- software without these copyright notices and license text, this text,
-- license and copyright still applies. This text, copyright and license
-- must not be removed under any circumstances. This applies to all
-- text displayed as comments up until and including the version history,
-- This text also applies to any copyright, banner or other text
-- refering to PeteFinnigan.com Limited ownership that is output by
-- the program.
--
-- This software is free to use BUT it is NOT open source and NOT GPL
-- or any similar license and it is NOT in the public domain.
--
-- You are permitted to use this software commercially or privately
-- provided these notices or banners as described are not removed. You
-- may modify the software and use it internally but this does not
-- infer any additonal rights in the software. i.e. if you delete
-- some of our code or change variable names or add features that does
-- not make it your code and does not give you the right to remove
-- our ownership in this software shown in these notices. This software must
-- not be made available or published in anyway, any language, any
-- modified form or original form except by PeteFinnigan.com Limited.
-- You must not incorporate this software into any free or commercial
-- product or software and you must not sell or give away any software that
-- includes this sofware.
--
--
-- In short this text is not written by a lawyer so please respect the
-- intent that you can use or modify it freely but not give it away
-- yourself or take away our right to attribution.
-- If someone else needs a copy please ask them to come to PeteFinnigan.com
-- Limited and we will happily let them also have a free copy. Also
-- as you may expect freely use does not include adding this software to
-- a commercial or free product (without our permission) but you
-- can use it internally in projects. We put our time into the free scripts
-- on our website or training courses and give these tools away for free and
-- in return we expect our copyright and ownership to always remain.
-- We like to help people but we also want to benefit from the fact
-- our name becomes known through these scripts and tools and software
-- that we make. We hope this makes sense.
--
--
-- -----------------------------------------------------------------------------
-- Version History
-- ===============
--
-- Who version Date Description
-- === ======= ====== ======================
-- P.Finnigan 1.0 Dec 2023 First Issue.
-- -----------------------------------------------------------------------------
set serveroutput on
set lines 500
declare
--
lv_t varchar2(4000);
--
cursor c_main is
select o.owner,
o.name,
o.type,
o.line,
substr(o.text,instr(o.text,'(',1)+1,((instr(o.text,')',-1))-instr(o.text,'(',1))-1) text
from (
select i.owner,
i.name,
i.type,
i.line,
upper(i.text) text
from dba_source i
where upper(i.text) like '%ACCESSIBLE%BY%(%)%'
and i.text not like '%--%') o;
--
function getsub(pv_owner in varchar2,
pv_name in varchar2,
pv_type in varchar2,
pv_line in number) return varchar2
is
cursor c_code (cv_owner in varchar2,
cv_name in varchar2,
cv_type in varchar2) is
select c.text,
c.line
from dba_source c
where c.owner=cv_owner
and c.name=cv_name
and c.type=cv_type
and (upper(c.text) like '%PROCEDURE%' or upper(c.text) like '%FUNCTION%')
and upper(c.text) not like '%--%';
--
lv_text varchar2(4000);
--
begin
--
for lv_code in c_code(pv_owner,pv_name,pv_type) loop
if(lv_code.line>pv_line) then
exit;
end if;
lv_text:=lv_code.text;
end loop;
--
return(lv_text);
--
end;
--
function extract(pv_text in varchar2) return varchar2
is
lv_ret varchar2(4000);
begin
if(instr(upper(pv_text),'PROCEDURE',1)>0) then
lv_ret:=replace(replace(ltrim(substr(upper(pv_text),instr(upper(pv_text),'PROCEDURE',1)+9)),chr(10)),chr(13));
if(instr(upper(lv_ret),'(',1)>0) then
lv_ret:=substr(upper(lv_ret),1,instr(upper(lv_ret),'(',1)-1);
end if;
if(instr(upper(lv_ret),' ',1)>0) then
lv_ret:=substr(upper(lv_ret),1,instr(upper(lv_ret),' ',1)-1);
end if;
if(instr(upper(lv_ret),')',1)>0) then
lv_ret:=substr(upper(lv_ret),1,instr(upper(lv_ret),')',1)-1);
end if;
--
elsif(instr(upper(pv_text),'FUNCTION',1)>0) then
lv_ret:=replace(replace(ltrim(substr(upper(pv_text),instr(upper(pv_text),'FUNCTION',1)+8)),chr(10)),chr(13));
if(instr(upper(lv_ret),'(',1)>0) then
lv_ret:=substr(upper(lv_ret),1,instr(upper(lv_ret),'(',1)-1);
end if;
if(instr(upper(lv_ret),' ',1)>0) then
lv_ret:=substr(upper(lv_ret),1,instr(upper(lv_ret),' ',1)-1);
end if;
if(instr(upper(lv_ret),'RETURN',1)>0) then
lv_ret:=substr(upper(lv_ret),1,instr(upper(lv_ret),'RETURN',1));
end if;
if(instr(upper(lv_ret),')',1)>0) then
lv_ret:=substr(upper(lv_ret),1,instr(upper(lv_ret),')',1)-1);
end if;
else
lv_ret:='';
end if;
return(lv_ret);
end;
--
begin
--
dbms_output.put_line(rpad('Owner',20,' ')||chr(9)||
rpad('Name',25,' ')||chr(9)||
rpad('Type',20,' ')||chr(9)||
rpad('Sub-Object',35,' ')||chr(9)||
rpad('Line',10,' ')||chr(9)||
'Accessible List');
dbms_output.put_line(rpad('==================',20,' ')||chr(9)||
rpad('=======================',25,' ')||chr(9)||
rpad('==================',20,' ')||chr(9)||
rpad('==================',35,' ')||chr(9)||
rpad('========',10,' ')||chr(9)||
'=================================================');
for lv_main in c_main loop
--
lv_t:=extract(getsub(lv_main.owner,lv_main.name,lv_main.type,lv_main.line));
if(lv_t is null) then
lv_t:='.';
end if;
dbms_output.put_line(rpad(lv_main.owner,20,' ')||chr(9)||
rpad(lv_main.name,25,' ')||chr(9)||
rpad(lv_main.type,20,' ')||chr(9)||
rpad(lv_t,35,' ')||chr(9)||
rpad(lv_main.line,10,' ')||chr(9)||
lv_main.text);
--
end loop;
--
end;
/
We implement the same checks in our PL/SQL security code scanner differently as it has a proper PL/SQL parser for evaluating PL/SQL code for issues. I may spend some more time on this above script to iron out the edge cases and I will post here again when thats done.
#oracleace #sym_42 #oracle #database #security #plsql #securecode
