SQLDev Web：OAuth2によるOracle Autonomous DatabaseでのRESTfulサービスのセキュリティ確保 (2020/11/30)

https://www.thatjeffsmith.com/archive/2020/11/sqldev-web-securing-restful-services-in-oracle-autonomous-database-with-oauth2/

これまで、SQL Developer Web を使って RESTful Web サービスを扱うことについてお話してきました。これは、SQL Developer Web 20.3 の主要な新機能です。この記事を読み進める前に、これらの記事に目を通しておいてください。

まだお話ししていないのは、Oracle Cloud（Autonomous Database）での動作と、セキュリティ機能です。

では、それを解決しましょう。

Oracle Autonomous Databaseでの動作

Oracle Autonomous Databaseでの動作は、他のORDSやOracle Databaseでの動作とほぼ同じです。REST Enabled Schemaにログインすると、ホーム画面にRESTカードが表示されます。

なお、Oracle Autonomous Sharedサービスは一両日中にアップデートされており、Autonomous Dedicated環境も間もなくアップグレードされる予定です。バージョン（20.3）が[バージョン情報]ボックスに表示されていることを確認してください。

この記事のすべては、オンプレミス環境のORDSにも適用されます。

ORDS/REST/OAuth2メカニズムは、Oracle CloudのOracle Autonomous Databaseにとって新しいものではありません。当社の開発者支援者であるTodd Sharpeが投稿した、OAuth2を使用してRESTful Webサービスを保護する方法を紹介しています。

Todd氏は、例題や空想のhello worldアプリケーションに使用するすべてのコードを常に共有している点が素晴らしいです。その中には、サービスのセキュリティを確保し、ORDS OAuth2クライアントを作成するために行ったPL/SQL ORDS APIコールも含まれています。

今でもそれは可能ですし、おそらくスクリプトやソースコードで管理すべきでしょう。

新しいのは、ユーザーインターフェース

REST開発インターフェースに初めて入ると、ツアーに参加することになります。

ここでは、お見せしたいものがあります。

RESTFulなWebサービスの確保
ロールの作成
OAuth2クライアントの作成
セキュリティで保護されたRESTful Webサービスにアクセス

RESTful Webサービスのセキュリティ確保

私はRESTfulなWebサービスを持っています。それは、指定された郵便番号の売上を集計するシンプルなGETです。このサービスは、最新のデータベース機能であるSQLマクロ（LiveSQLのチュートリアル）を利用しています。

RESTful Web Service/REST Enabled Objectは何を使っても構いませんが、ORDSの権限を作成する際には、あなたのモジュールやリソースのテンプレートパターンを私のものに置き換えてください。

このコードは、REST Enabled スキーマで SQL ワークシートを使って実行できます。販売履歴（SH）データは、すでにすべてのOracle Autonomousインスタンスで利用可能です。

CREATE OR REPLACE EDITIONABLE FUNCTION "F_AVG_SALES_MEN_WOMEN" (n in integer) return sys_refcursor as
 f_results sys_refcursor;
-- n is number of decimal points precision for amount sold and t_observed stats
-- takes advantage of the statisticall function ROLLUP command

begin
 open f_results for
 select SUBSTR(CUST_INCOME_LEVEL, 1, 22) INCOME_LEVEL,
       round(avg(DECODE(CUST_GENDER, 'M', AMOUNT_SOLD,
                  null)), n)                             SOLD_TO_MEN,
       round(avg(DECODE(CUST_GENDER, 'F', AMOUNT_SOLD,
                  null)), n)                            SOLD_TO_WOMEN,
       round(stats_t_test_indep(CUST_GENDER, AMOUNT_SOLD, 'STATISTIC',
                          'F'), n)                           T_OBSERVED,
       stats_t_test_indep(CUST_GENDER, AMOUNT_SOLD) TWO_SIDED_P_VALUE
  from SH.CUSTOMERS    C,
       SH.SALES        S
 where C.CUST_ID = S.CUST_ID
 group by rollup(CUST_INCOME_LEVEL)
 order by INCOME_LEVEL,
          SOLD_TO_MEN,
          SOLD_TO_WOMEN,
          T_OBSERVED;
 return f_results;
end f_avg_sales_men_women;
/

CREATE OR REPLACE EDITIONABLE FUNCTION "TOTAL_SALES" (zip_code varchar2) return varchar2 SQL_MACRO(TABLE) is
begin
  return q'{
   select cust.cust_postal_code as zip_code,
             sum(amount_sold) as revenue
   from sh.customers cust, sh.sales s
   where cust.cust_postal_code = total_sales.zip_code 
   and s.cust_id = cust.cust_id
   group by cust.cust_postal_code
   order by cust.cust_postal_code
  }';
end;
/

BEGIN
ORDS.DEFINE_MODULE(
      p_module_name    => 'sales_history',
      p_base_path      => '/sales_history/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'sales_history',
      p_pattern        => 'mf_breakdowns/:n',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'sales_history',
      p_pattern        => 'mf_breakdowns/:n',
      p_method         => 'GET',
      p_source_type    => 'json/query',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select F_AVG_SALES_MEN_WOMEN(:N)
  from DUAL');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'sales_history',
      p_pattern        => 'total_sales/:zipcode',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'sales_history',
      p_pattern        => 'total_sales/:zipcode',
      p_method         => 'GET',
      p_source_type    => 'json/item',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select * from total_sales(:zipcode)');

END;
/

簡単な例として、郵便番号での売上に対する関数を披露するためにSQLを実行してみましょう。

select * from total_sales(60332)

そして、もしあなたが実行計画に興味があるなら...。

SQLマクロを使えば、このクエリを様々なPL/SQLの実装で簡単に使うことができます。

上で紹介したスクリプトを実行したら、REST開発に入り、Modulesカードをクリックして、「total_sales/:zipcode」テンプレートにドリルダウンしてください。GET ハンドラが定義されているのがわかります。

URLをコピーして、プライベート/シークレットブラウザに貼り付けると、GETリクエストの結果を見ることができます。

まだ保護されていないのです。

「保護されている」といえば、もしRESTful Webサービスが保護されていなければ、そのURLに到達できる誰もが、そのサービスを使用してデータを取得することができるということです。

RESTコンソールページでは、以下の点に注意してください。

何かが保護されているが、すべてが保護されているわけではない。

そこで、「イエロー」から「グリーン」に変えてみましょう。REST APIでは、認証および許可されたリクエストを必要とするようにします。

ORDSロールの作成

REST画面の「セキュリティ」メニューから「ロール」をクリックし、「ロールの作成」ボタンをクリックします。

ORDSの役割は非常にシンプルで、その名前で100％定義されます。

ORDSロールはデータベースロールではありません。データベース・ロールは、データベース・ユーザーに割り当てられるデータベースの権限を含みます。ORDSの役割は、HTTP(S)で認証されたユーザーやクライアントに割り当てることができます。ORDSロールはORDS権限を持ちます。

Roleが作成されたら、ORDS Privilegeの作成に移りましょう。

ORDS 特権の作成

RESTツールバーの「Security」をクリックします。ドロップダウンをクリックして、「Privileges」を選択します。

ここでは新しい Privilege を作成します。「Create」ボタンをクリックします。

名前は重要ではありませんが、後で認識して理解できるような名前を使ってください。コメントを使うと、仲間の開発者が喜びます。

先に作成した「ロール」を、新しい権限に結びつけます。そして最後に、この特権を sales_history モジュール全体に関連付けます。

名前をつけ、ロールに割り当て、1つ以上のモジュールやテンプレート・パターンに結びつけます。

これでREST APIがPrivilegeで保護されたので、再びアクセスしようとすると、次のようになります。

たとえORDSがあなたのことを知っていても、あなたが必要な役割を持っていなければ、アクセスできません。

これまでに起こったこと

動作するRESTful Webサービスを作成しました。それを使用しました。ORDS RoleとPrivilegeを作成し、そのPrivilegeはRESTful Web Serviceのモジュールに関連付けられています。現在、APIにアクセスしようとすると、401が返ってきます。

これは認証されていないか、認証されていたとしても権限がないためです。ORDSがこれらの保護されたリソースへのアクセスを許可する唯一の方法は、認証されたユーザーが適切な役割（「SALES」）を持っている場合です。

OAuth2クライアントの作成

「セキュリティ」メニューに戻り、「OAuthクライアント」をクリックします。

まだ何もありません。「Create」ボタンをクリックしてください。

スライダーの仕組みが出てきますので、詳細を記入していきましょう。私のウェブサイトやメールを使わないでください。さもないと私があなたを見つけて、私は...満足しません。

Client DefとRolesページの*は、クライアントを作成するための必須要素を示しています。

ここで重要なのは、クライアントにRoleが割り当てられていることを確認することです。これを忘れてしまうと、RESTful Web Serviceにアクセスしようとしても、アクセスを拒否されてしまいます。

新しいOAuth2クライアントの確認

クライアントカードが表示されているはずです。ここでは

クライアントIDの取得
シークレットの取得
ベアラートークンの取得
ベアラートークンを取得するためのcURLコードの取得

そしてこれがGIFデモです🙂。

ORDS と OAuth2 に関するブログ記事をご覧になったことがある方は、最後のステップで、データベースにクエリを実行して Client Id と Client Secret を取得していることがわかると思います。この方法も可能ですが、こちらの方がより便利だと思います。

保護されたリソースへのアクセス

これをすべてcURLで行うこともできますが、私はcURLが嫌いです。私が使っているRESTクライアント（Insomnia）は、統合された素晴らしいセキュリティメカニズムを提供しているので、それを利用するつもりです。

Basic Authを使ってデータベースの認証情報を提供し、「SQL Developer」ORDSロールに依存する代わりに、SALES ORDSロールを持つOAuth2クライアントを使うことができます。データベースの認証情報を、公開したAPIにアクセスしたい誰かと共有することもなく、はるかに良い結果となっています。