Mail from Application Express with Access Control Enabled

One of the post installation task after installing Oracle 11 is the creation of Access Control List (ACL). Oracle provides a few scripts in order to allow flows_030000 to connect any host. What if you do not allow the database to connect any host but only one host. For instance, you want to send mail from Application Express (APEX) ?

First, set up the email settings in the administrative interface (Home>Manage Service>Instance Settings). Secondly, run the following statements as
system.

-- Create an access control list
BEGIN
  dbms_network_acl_admin.create_acl(acl => 'apexmail.xml',   
    description => 'Mail from Apex',   principal => 'FLOWS_030000',
    is_grant => TRUE,   privilege => 'connect',   start_date => NULL,
    end_date => NULL);
END;
/

-- Assign the list to your SMTP host, i.e. smtp.yyy.xx
BEGIN
  dbms_network_acl_admin.assign_acl(acl => 'apexmail.xml',
    host => 'smtp.yyy.xx',   lower_port => 25,   upper_port => 25);
END;
/

-- Uncomment to remove access control list
--BEGIN
--  dbms_network_acl_admin.drop_acl(acl => 'apexmail.xml');
--END;
--/

-- Please, do commit work.
COMMIT;

You can find more information about the usage of dbms_network_acl_admin in the Database Security Guide. For all the remaining network services you can apply the same technique.

Where is the Scott schema in APEX?

I decided to install Oracle 11g. And with Oracle 11g Application Express (APEX)is installed by default. Before importing an application based on the emp table, I decided to create a workspace based on the existing schema Scott. However the administrative interface did not allow me to select Scott. By not using the LOV but typing the schema name, APEX gave the following error: Schema is reserved or restricted. What is going on?

APEX uses the following query to populate the list of values:

SELECT name n,
 name r
FROM sys.user$ u
WHERE type# = 1
AND name <> 'FLOWS_030000'
AND name NOT IN('HTMLDB_PUBLIC_USER',   'PUBLIC_USER',   'FLOWS_FILES',
               'SCHEDULER',   'PEOPLE',   'APEX_PUBLIC_USER')
AND name NOT IN
(SELECT SCHEMA
 FROM wwv_flow_restricted_schemas
 WHERE NOT EXISTS
  (SELECT 1
   FROM wwv_flow_rschema_exceptions
   WHERE schema_id =
    (SELECT id
     FROM wwv_flow_restricted_schemas
     WHERE SCHEMA = u.name)
  )
)
ORDER BY 1
/

Beside some hard coded schema names, there are two flows_030000 tables used in the query, wwv_flow_restricted_schemas and wwv_flows_rschema_exception.

SQL>SELECT * FROM flows_030000.wwv_flow_restricted_schemas t
/
ID SCHEMA CREATED_BY CREATED_ON       LAST_UPDATED_BY LAST_UPDATED_ON
-- ------ ---------- ---------------- --------------- ---------------
...
38 SCOTT  SYS        3-8-2007 2:12:53
...

The wwv_flows_rschema_exception contains no rows.

Both tables are used by the package htmldb_site_admin_privs, which serves as an interface packages. Now remove the restriction on Scott by executing the following statements (as Sys).

SQL>exec flows_030000.htmldb_site_admin_privs.unrestrict_schema(p_schema => 'SCOTT');
SQL>commit;

And of course, this topic is covered by the Application Express User's Guide.