agentmail/run_as_admin.sql at main · ethanpost/agentmail · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118

-- ============================================================
-- IMPORTANT: The following steps MUST be run as a DBA or user
-- with sufficient privileges (typically a database administrator).
-- ============================================================
-- These statements grant network ACL permissions so
-- AgentMail can connect to the AgentMail API over HTTPS.
-- ============================================================


PROMPT *******************************************************************
PROMPT *                                                                 *
PROMPT *  >>> IMPORTANT: Set the &principal variable below to the schema *
PROMPT *      that owns the AGENTMAIL package and run this script as DBA.*
PROMPT *                                                                 *
PROMPT *  EXAMPLE:                                                       *
PROMPT *    define principal = 'MY_AGENTMAIL_SCHEMA'                     *
PROMPT *                                                                 *
PROMPT *  See README.md: "Grant network access" for details.             *
PROMPT *******************************************************************

define principal = 'USER_WHO_OWNS_AGENTMAIL';

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl         => 'agentmail_http_acl.xml',
    description => 'Allow AgentMail package to fetch attachment download URLs',
    principal   => '&principal',
    is_grant    => TRUE,
    privilege   => 'connect'
  );
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -29466 THEN NULL;
    ELSE RAISE;
    END IF;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl       => 'agentmail_http_acl.xml',
    principal => '&principal',
    is_grant  => TRUE,
    privilege => 'resolve'
  );
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -29465 THEN NULL;
    ELSE RAISE;
    END IF;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl  => 'agentmail_http_acl.xml',
    host => 'api.agentmail.to'
  );
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -29464 THEN NULL; -- host already assigned
    ELSE RAISE;
    END IF;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host       => 'api.agentmail.to',
    lower_port => 443,
    upper_port => 443,
    ace        => xs$ace_type(
                    privilege_list => xs$name_list('http'),
                    principal_name => '&principal',
                    principal_type => xs_acl.ptype_db
                  )
  );
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host       => 'storage.agentmail.to',
    lower_port => 443,
    upper_port => 443,
    ace        => xs$ace_type(
                    privilege_list => xs$name_list('http'),
                    principal_name => '&principal',
                    principal_type => xs_acl.ptype_db
                  )
  );
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host       => 'cdn.agentmail.to',
    lower_port => 443,
    upper_port => 443,
    ace        => xs$ace_type(
                    privilege_list => xs$name_list('http'),
                    principal_name => '&principal',
                    principal_type => xs_acl.ptype_db
                  )
  );
END;
/

SELECT host,
       lower_port,
       upper_port,
       principal,
       privilege,
       grant_type
FROM   dba_host_aces
WHERE  principal IN ('&principal')
ORDER  BY principal, host, lower_port, upper_port, privilege;