Database Reference
In-Depth Information
You will notice that this syntax is quite different from what we're used to when calling procedures.
These are, after all, simply calls to the
CREATE_ACL
and
ASSIGN_ACL
procedures in the
DBMS_NETWORK_ACL_ADMIN
package that is installed by default in the Oracle Database. In Listing 9-2, we are
specifying the parameters for these procedures using
named notation
, as opposed to our usual
positional notation
. (I've given named notation here because every example I've seen has used that
notation; we probably all just copy the example given in the Oracle documentation).
In named notation, the value being assigned to each parameter is given after the defined parameter
name and the assignment operator
=>
, e.g.,
acl => 'smtp_acl_file.xml'
. When we call a procedure
using positional notation, we list the parameters in the order they were defined in the procedure (hence
the name
positional
); but in named notation, the order is not important.
Another difference between positional and named notation is in how optional parameters are
handled. You will recall that our error logging table,
t_appsec_errors
, has two optional parameters,
msg_txt
and
update_ts
. They are optional because we configured them with default values. The default
value for
update_ts
is
SYSDATE
. In our
p_log_error
procedure, we never provide a value for
update_ts
. We
want the default SYSDATE.
Back to the topic at hand: in positional notation, only optional parameters that are defined after all
the required parameters may be ignored. With named notation, we can skip providing optional
parameters no matter where they occur in the parameters definition, as long as we supply values for all
the required parameters by name.
In the two procedures being called in Listing 9-2,
CREATE_ACL
and
ASSIGN_ACL
, the optional
parameters are the last two defined for each procedure, so there is no compelling reason to call these
procedures using named notation. In addition, the two calls that I have listed provide a value (or NULL)
for each parameter, including the optional ones.
Again as
SYS
user or
DBA
, you can list the ACLs to ensure that your entries were made. Execute these
queries:
SELECT * FROM sys.dba_network_acls;
SELECT * FROM sys.dba_network_acl_privileges;
The results of the first command will resemble this:
"HOST" "LOWER_PORT" "UPPER_PORT" "ACL" "ACLID"
"smtp.org.com" "25" "25" "/sys/acls/smtp_acl_file.xml" "004B...
The results of the second command will resemble this:
"ACL" "ACLID" "PRINCIPAL" "PRIVILEGE" "IS_GRANT" ...
"/sys/acls/smtp_acl_file.xml" "004B... "APPSEC" "connect" "true" ...
Testing Sending E-Mail
While we are here, we should execute a test to be sure we can send e-mail. We are going to execute the
test as our Application Security,
appsec
user. I apologize for bouncing around between these accounts,
but we are delegating tasks and checking our work at each step.
Note
You can find a script of the commands to follow in this section in the file named
Chapter9/AppSec.sql
.
