Question: How does
white-listing within Oracle 12c? Can you give an
example f the accessible by clause in PL/SQL in 12c?.
Answer: As the term denotes, "white listing"
involves specific allowing of access to a stored procedure
in PL/SQL:
create or replace procedure
protected_plsql_procedure
accessible
by (calling_plsql_1, calling_plsql_2,
calling_plsql_3)
as
begin
. . .
end;
/
Above we
see the white list calling_plsql_1 through calling_plsql_3
as defined in the accessible by clause.
This is
quite different to the other option, the "grant execute"
clause, which specifically allows execution-level privileges
on a PL/SQL stored procedure to a specific user.
White
listing further confuses the methods for Oracle
security by expanding the rights to execute a stored
procedure to a list of users or to a list of stored
procedures and/or functions.
If you try
to execute a PL/SQL from a PL/SQL procedure (or function)
that is not on the whitelist, you will get an PLS-00904
error:
create or replace
procedure
not_in_white_list as
begin
protected_pl_sql_1;
end;
/
Warning: Procedure created with
compilation errors.
SQL> show errors
Errors for NOT_IN_WHITE_LIST:
LINE/COL ERROR
--------
-----------------------------------------------------------------
4/3
PL/SQL: Statement ignored
4/3
PLS-00904: insufficient privilege to access object
PROTECTED_PL_SQL_1
