Oracle Autonomous Databaseのプロダクト・マネージャTodd Bottgerのブログ記事Oracle Autonomous Database Vanity URLs Part 2: Blocking Developer and Administrator Toolsの内容を確認してみました。
確認に使用した環境は元記事とは異なり(プライベート・エンドポイントでADBを構成するには費用がかかるため)、こちらの記事で作成した環境を使っています。
サンプル・アプリケーションを準備する
Oracle APEXの環境およびワークスペースが作成された直後を想定していますが、すでにワークスペースにアプリケーションが作成済みであれば、それをサンプルとして使うこともできます。その場合は、以下のサンプル・アプリケーションの作成作業は実施不要です。
今のところVanity URL経由でも管理ツールや開発ツールにアクセスできますが、Oracle CloudのコンソールからOracle APEXにアクセスして作業を進めます。
デフォルトのアプリケーションを変更する
APEXのツールをブロックする
begin
apex_instance_admin.set_parameter('RESTRICT_DEV_HEADER', 'ADB-Public-Access');
commit;
end;
/
begin
dbms_output.put_line(apex_instance_admin.get_parameter('RESTRICT_DEV_HEADER'));
end;
/
データベース・アクションをブロックする
REST対応SQLをブロックする
curl -X "POST" "https://<ホスト名>/ords/apexdev/_/sql" --header "Content-Type: application/sql" --user APEXDEV:APEXDEVのパスワード --data $'SELECT * FROM emp'
(以下は出力行数を制限しています)
% curl -X "POST" "https://<hostname>/ords/apexdev/_/sql" --header "Content-Type: application/sql" --user APEXDEV:************* --data $'SELECT * FROM emp where rownum < 2'
{"env":{"defaultTimeZone":"UTC"},"items":[{"statementId":1,"statementType":"query","statementPos":{"startLine":1,"endLine":2},"statementText":"SELECT * FROM emp where rownum < 2","resultSet":{"metadata":[{"columnName":"EMPNO","jsonColumnName":"empno","columnTypeName":"NUMBER","precision":4,"scale":0,"isNullable":0},{"columnName":"ENAME","jsonColumnName":"ename","columnTypeName":"VARCHAR2","precision":50,"scale":0,"isNullable":1},{"columnName":"JOB","jsonColumnName":"job","columnTypeName":"VARCHAR2","precision":50,"scale":0,"isNullable":1},{"columnName":"MGR","jsonColumnName":"mgr","columnTypeName":"NUMBER","precision":4,"scale":0,"isNullable":1},{"columnName":"HIREDATE","jsonColumnName":"hiredate","columnTypeName":"DATE","precision":0,"scale":0,"isNullable":1},{"columnName":"SAL","jsonColumnName":"sal","columnTypeName":"NUMBER","precision":7,"scale":2,"isNullable":1},{"columnName":"COMM","jsonColumnName":"comm","columnTypeName":"NUMBER","precision":7,"scale":2,"isNullable":1},{"columnName":"DEPTNO","jsonColumnName":"deptno","columnTypeName":"NUMBER","precision":2,"scale":0,"isNullable":1}],"items":[{"empno":7839,"ename":"中島 亜希子","job":"社長","mgr":null,"hiredate":"1981-11-17T00:00:00Z","sal":5000,"comm":null,"deptno":10}],"hasMore":false,"limit":10000,"offset":0,"count":1},"response":[],"result":0}]}
%
% curl -v -X "POST" "https://<hostname>/ords/apexdev/_/sql" --header "Content-Type: application/sql" --user APEXDEV:********* --data $'SELECT * FROM emp where rownum < 2'
Note: Unnecessary use of -X or --request, POST is already inferred.
* Trying 158.101.94.209...
* TCP_NODELAY set
* Connected to www.**********.dev (158.101.94.209) port 443 (#0)
* ALPN, offering h2
* ALPN, offering http/1.1
* successfully set certificate verify locations:
* CAfile: /etc/ssl/cert.pem
CApath: none
* TLSv1.2 (OUT), TLS handshake, Client hello (1):
* TLSv1.2 (IN), TLS handshake, Server hello (2):
* TLSv1.2 (IN), TLS handshake, Certificate (11):
* TLSv1.2 (IN), TLS handshake, Server key exchange (12):
* TLSv1.2 (IN), TLS handshake, Server finished (14):
* TLSv1.2 (OUT), TLS handshake, Client key exchange (16):
* TLSv1.2 (OUT), TLS change cipher, Change cipher spec (1):
* TLSv1.2 (OUT), TLS handshake, Finished (20):
* TLSv1.2 (IN), TLS change cipher, Change cipher spec (1):
* TLSv1.2 (IN), TLS handshake, Finished (20):
* SSL connection using TLSv1.2 / ECDHE-RSA-AES128-GCM-SHA256
* ALPN, server accepted to use http/1.1
* Server certificate:
* subject: CN=*******.dev
* start date: Oct 4 00:00:00 2021 GMT
* expire date: Jan 2 23:59:59 2022 GMT
* subjectAltName: host "www.*********.dev" matched cert's "www.********.dev"
* issuer: C=AT; O=ZeroSSL; CN=ZeroSSL RSA Domain Secure Site CA
* SSL certificate verify ok.
* Server auth using Basic with user 'APEXDEV'
> POST /ords/apexdev/_/sql HTTP/1.1
> Host: www.apexugj.dev
> Authorization: Basic QVBF*********************zkyTg==
> User-Agent: curl/7.64.1
> Accept: */*
> Content-Type: application/sql
> Content-Length: 34
>
* upload completely sent off: 34 out of 34 bytes
< HTTP/1.1 302 Moved Temporarily
< Date: Wed, 20 Oct 2021 06:34:34 GMT
< Content-Type: text/html
< Content-Length: 133
< Connection: keep-alive
< Location: https://www.*********.dev:443/ords/blocked
<
<html>
<head><title>302 Found</title></head>
<body>
<center><h1>302 Found</h1></center>
<hr><center></center>
</body>
</html>
* Connection #0 to host www.apexugj.dev left intact
* Closing connection 0
ynakakoshi@yujis-macbook-pro Downloads %
