Example 20-4 Granting Access Privileges Using a Role

This example script shows the commands necessary to enable DBMS_CLOUD functionality. Wrap these commands into a SQL script and run the script either in the CDB or the PDB as SYS where you want to provide DBMS_CLOUD functionality to your user or role.

The example script uses a local role, CLOUD_USER, and grants privileges to a local user, SCOTT. You can modify this script as needed for your PDB environment. Run the script as a privileged administrator within your PDB (for example, SYS or SYSTEM)

@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql
 
-- target sample roledefine cloudrole=CLOUD_USER
 
-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER
-- - SSL Wallet directory
define sslwalletdir=<Set SSL Wallet Directory>
 
---- UNCOMMENT AND SET THE PROXY SETTINGS VARIABLES IF YOUR ENVIRONMENT NEEDS PROXYS
--
-- define proxy_uri=<your proxy URI address>
-- define proxy_host=<your proxy DNS name>
-- define proxy_low_port=<your_proxy_low_port>
-- define proxy_high_port=<your_proxy_high_port>
 
-- Create New ACL / ACEs
begin
-- Allow all hosts for HTTP/HTTP_PROXY
    dbms_network_acl_admin.append_host_ace(
        host =>'*',
        lower_port => 443,
        upper_port => 443,
        ace => xs$ace_type(
            privilege_list => xs$name_list('http', 'http_proxy'),
            principal_name => upper('&cloudrole'),
            principal_type => xs_acl.ptype_db));
 
--
-- UNCOMMENT THE PROXY SETTINGS SECTION IF YOUR ENVIRONMENT NEEDS PROXYS
--
-- Allow Proxy for HTTP/HTTP_PROXY
-- dbms_network_acl_admin.append_host_ace(
-- host =>'&proxy_host',
-- lower_port => &proxy_low_port,
-- upper_port => &proxy_high_port,
-- ace => xs$ace_type(
-- privilege_list => xs$name_list('http', 'http_proxy'),
-- principal_name => upper('&cloudrole'),
-- principal_type => xs_acl.ptype_db));
--
-- END PROXY SECTION
--
 
-- Allow wallet access
    dbms_network_acl_admin.append_wallet_ace(
        wallet_path => 'file:&sslwalletdir',
        ace => xs$ace_type(
            privilege_list =>xs$name_list('use_client_certificates', 'use_passwords'),
            principal_name => upper('&cloudrole'),
            principal_type => xs_acl.ptype_db));
end;
/
 
@$ORACLE_HOME/rdbms/admin/sqlsessend.sql

Example 20-5 Granting Access Privileges to an Individual User

In this example script, we assume local user SCOTT has been created with DBMS_CLOUD privileges, as shown previously, and you are now granting access privileges to that user. You can modify this script as needed for your PDB environment.

@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql
 
-- target sample user
define clouduser=SCOTT
 
-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER
-- - SSL Wallet directory
define sslwalletdir=<Set SSL Wallet Directory>
 
-- Proxy definition
-- define proxy_uri=<your proxy URI address>
-- define proxy_host=<your proxy DNS name>
-- define proxy_low_port=<your_proxy_low_port>
-- define proxy_high_port=<your_proxy_high_port>
 
-- Create New ACL / ACEs
begin
-- Allow all hosts for HTTP/HTTP_PROXY
    dbms_network_acl_admin.append_host_ace(
        host =>'*',
        lower_port => 443,
        upper_port => 443,
        ace => xs$ace_type(
            privilege_list => xs$name_list('http', 'http_proxy'),
            principal_name => upper('&clouduser'),
            principal_type => xs_acl.ptype_db));
 
--
-- UNCOMMENT THE PROXY SETTINGS SECTION IF YOUR ENVIRONMENT NEEDS PROXYS
--
-- Allow Proxy for HTTP/HTTP_PROXY
-- dbms_network_acl_admin.append_host_ace(
-- host =>'&proxy_host',
-- lower_port => &proxy_low_port,
-- upper_port => &proxy_high_port,
-- ace => xs$ace_type(
-- privilege_list => xs$name_list('http', 'http_proxy'),
-- principal_name => upper('&clouduser'),
-- principal_type => xs_acl.ptype_db));
--
-- END PROXY SECTION
--
 
-- Allow wallet access
    dbms_network_acl_admin.append_wallet_ace(
        wallet_path => 'file:&sslwalletdir',
        ace => xs$ace_type(
            privilege_list =>xs$name_list('use_client_certificates', 'use_passwords'),
            principal_name => upper('&clouduser'),
            principal_type => xs_acl.ptype_db));
end;
/
 
@$ORACLE_HOME/rdbms/admin/sqlsessend.sql