Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Oracle APEX勉強会 - 認証と認可の実装を学ぶ

Oracle APEX勉強会 - 認証と認可の実装を学ぶ

Oracle APEXが提供するユーザー認証と認可の機能、それも主にソーシャル・サインインの実装について紹介しています。
1. Oracle APEXの認証スキーム
2. カスタム認証の実装
3. Googleを使った認証の実装
4. Facebookを使った認証の実装
5. LinkedInを使った認証の実装
6. LINEを使った認証の実装
7. Yahoo! Japanを使った認証の実装
8. 複数の認証の切り替え
9. Oracle APEXの認可スキーム
10. 認証に関するトピック

Yuji Nakakoshi

October 24, 2019
Tweet

More Decks by Yuji Nakakoshi

Other Decks in Technology

Transcript

  1. Safe harbor statement The following is intended to outline our

    general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. Copyright © 2019 Oracle and/or its affiliates.
  2. Oracle APEX 1 2 3 4 5 Google Facebook LinkedIn

    Copyright © 2019 Oracle and/or its affiliates. 6 LINE 8 Oracle APEX 9 10 Yahoo! Japan 7
  3. Oracle APEX 1 2 3 4 5 Google Facebook LinkedIn

    Copyright © 2019 Oracle and/or its affiliates. 6 LINE 8 Oracle APEX 9 10 Yahoo! Japan 7
  4. Oracle APEX • APP_USER • APP_USER ( ) Copyright ©

    2019 Oracle and/or its affiliates. 選択可能な認証スキーム 英語表記
  5. (1) • Application Express (Application Express Accounts) • Application Express

    Application Express ( Cookie ) • HTTP (HTTP Header Variable) • Web HTTP • LDAP (LDAP Directory) • LDAP / • Oracle Application Server Single Sign-On • Oracle AS Single Sign-On (SSO) SSO Copyright © 2019 Oracle and/or its affiliates.
  6. (2) • (Custom) • • (Social Sign-In) • Google Facebook

    OpenID Connect OAuth2 • (Database Accounts) • ( ) • (Open Door Credentials) • • (No Authentication) • mod_plsql DAD Copyright © 2019 Oracle and/or its affiliates.
  7. Autonomous Database Autonomous Database (Autonomous Data Warehouse/Autonomous Transaction Processing) HTTP

    Header Variable LDAP Directory Oracle Application Server Single Sign-On https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/apex- restrictions.html#GUID-E13D5044-B9DD-4168-8A12-C99532940DA9 Copyright © 2019 Oracle and/or its affiliates.
  8. Oracle APEX 1 2 3 4 5 Google Facebook LinkedIn

    Copyright © 2019 Oracle and/or its affiliates. 6 LINE 8 Oracle APEX 9 10 Yahoo! Japan 7
  9. • • Sentry Function • true false • • Sentry

    Function false • • true, false • • Copyright © 2019 Oracle and/or its affiliates.
  10. / Copyright © 2019 Oracle and/or its affiliates. ID VARCHAR2(32)

    RAWTOHEX(SYS_GUID()) USER_NAME VARCHAR2(30) PASSWORD VARCHAR2(256) || ID || USER_NAME SHA-512
  11. • ( )DBMS_CRYPTO STANDARD_HASH Copyright © 2019 Oracle and/or its

    affiliates. function my_authentication ( p_username in varchar2, p_password in varchar2 ) return boolean is l_user my_users.user_name%type := upper(p_username); l_pwd my_users.password%type; l_id my_users.id%type; l_hash my_users.password%type; begin select id , password into l_id, l_pwd from my_users where user_name = l_user; select rawtohex(standard_hash(p_password||l_id||l_user, 'SHA512')) into l_hash from dual; return l_pwd = l_hash; exception when NO_DATA_FOUND then return false; end;
  12. 認証ファンクション(追加コード) Copyright © 2019 Oracle and/or its affiliates. CREATE TABLE

    "MY_USERS" ( "ID" VARCHAR2(32), "USER_NAME" VARCHAR2(30) NOT NULL ENABLE, "PASSWORD" VARCHAR2(256), PRIMARY KEY ("ID") USING INDEX ENABLE) ; declare l_id my_users.id%type; begin l_id := rawtohex(sys_guid()); insert into my_users(id, user_name) values(l_id,'TESTUSER'); update my_users set password = rawtohex(standard_hash('mypass7777'||id||user_name, 'SHA512')) where id = l_id; end; ユーザー情報を保持する表を作成 初期ユーザーの投入
  13. Oracle APEX 1 2 3 4 5 Google Facebook LinkedIn

    Copyright © 2019 Oracle and/or its affiliates. 6 LINE 8 Oracle APEX 9 10 Yahoo! Japan 7
  14. Google • Google APIs Developer Console • • Copyright ©

    2019 Oracle and/or its affiliates. https://console.developers.google.com
  15. Google • OAuth 2.0 ID • • URI • https://apex.oracle.com/pls/apex/apex_a

    uthentication.callback • https:// /ords/apex_authentication_callback • JavaScript Copyright © 2019 Oracle and/or its affiliates.
  16. Google Web • Web OAuth2 ID • Web Copyright ©

    2019 Oracle and/or its affiliates.
  17. Google • PL/SQL • post_auth • URL Google URL •

    https://accounts.google.com/Logout Copyright © 2019 Oracle and/or its affiliates.
  18. Google • G_AUTH_SCHEME • G_DISPLAY_NAME • Google, Facebook, YahooJ name

    • LinkedIn localizedFirstName, localizedLastName • LINE - displayName Copyright © 2019 Oracle and/or its affiliates. procedure post_auth is k varchar2(32767); v apex_json.t_value; t apex_json.t_kind; begin -- Set APP_NAME for unique id and G_DISPLAY_NAME for display :G_AUTH_SCHEME := 'GOOGLE'; apex_custom_auth.set_user(:G_AUTH_SCHEME || '+' || :APP_USER); :G_DISPLAY_NAME := apex_json.get_varchar2('name'); -- Inspect User Info data k := apex_json.g_values.FIRST; while k is not null loop :G_USER_INFO := :G_USER_INFO || '<p>' || k || ':'; v := apex_json.get_value(k); if v.kind = 1 then :G_USER_INFO := :G_USER_INFO || 'NULL'; elsif v.kind = 2 then :G_USER_INFO := :G_USER_INFO || 'TRUE'; elsif v.kind = 3 then :G_USER_INFO := :G_USER_INFO || 'FALSE'; elsif v.kind = 4 then :G_USER_INFO := :G_USER_INFO || v.number_value; elsif v.kind = 5 then :G_USER_INFO := :G_USER_INFO || v.varchar2_value; elsif v.kind = 6 then :G_USER_INFO := :G_USER_INFO || 'Object'; elsif v.kind = 7 then :G_USER_INFO := :G_USER_INFO || 'Array'; else :G_USER_INFO := :G_USER_INFO || 'Other'; end if; :G_USER_INFO := :G_USER_INFO || '</p>' || chr(10); k := apex_json.g_values.NEXT(k); end loop; end post_auth;
  19. Oracle APEX 1 2 3 4 5 Google Facebook LinkedIn

    Copyright © 2019 Oracle and/or its affiliates. 6 LINE 8 Oracle APEX 9 10 Yahoo! Japan 7
  20. Facebook • Facebook for Developers • Copyright © 2019 Oracle

    and/or its affiliates. https://developers.facebook.com/apps
  21. Facebook Facebook • Facebook • OAuth URI • https://apex.oracle.com/pls/apex/apex _authentication.callback

    • https:// /ords/apex_authentication.callback Copyright © 2019 Oracle and/or its affiliates.
  22. Facebook Web • Facebook ID ID • Facebook app secret

    • Web Copyright © 2019 Oracle and/or its affiliates.
  23. Oracle APEX 1 2 3 4 5 Google Facebook LinkedIn

    Copyright © 2019 Oracle and/or its affiliates. 6 LINE 8 Oracle APEX 9 10 Yahoo! Japan 7
  24. LinkedIn • LinkedIn for Developers • Create app Copyright ©

    2019 Oracle and/or its affiliates. https://www.linkedin.com/developers/apps
  25. LinedIn - Settings • Application name Company Business email Privary

    policy URL App logo Privarcy policy URL • Company Page Admin Copyright © 2019 Oracle and/or its affiliates.
  26. LinedIn - Auth • Client ID Client Secret Web •

    OAuth 2.0 settings Redirect URIs • https://apex.oracle.com/pls/apex/apex_authentica tion.callback • https:// /ords/apex_authentication.callback Copyright © 2019 Oracle and/or its affiliates.
  27. LinkedIn Web • LinkedIn Client ID ID • LinkedIn Client

    Secret • Web Copyright © 2019 Oracle and/or its affiliates.
  28. LinkedIn • • Web • OAuth2 • URL URL URL

    Copyright © 2019 Oracle and/or its affiliates.
  29. Oracle APEX 1 2 3 4 5 Google Facebook LinkedIn

    Copyright © 2019 Oracle and/or its affiliates. 6 LINE 8 Oracle APEX 9 10 Yahoo! Japan 7
  30. LINE • LINE Developers ( ) • LINE • •

    • LINE Developers ( ) • • • Copyright © 2019 Oracle and/or its affiliates. https://developers.line.biz/console/
  31. LINE • ( ) • Channel ID Channel Secret Web

    ID Copyright © 2019 Oracle and/or its affiliates.
  32. LINE • g Callback URL • https://apex.oracle.com/pls/apex/apex_authentica tion.callback • https://

    /ords/apex_authentication.callback Copyright © 2019 Oracle and/or its affiliates.
  33. LINE Web • LINE Channel ID ID • LINE Client

    Secret • Web Copyright © 2019 Oracle and/or its affiliates.
  34. LINE • • Web • OAuth2 • URL URL URL

    Copyright © 2019 Oracle and/or its affiliates.
  35. Oracle APEX 1 2 3 4 5 Google Facebook LinkedIn

    Copyright © 2019 Oracle and/or its affiliates. 6 LINE 8 Oracle APEX 9 10 Yahoo! Japan 7
  36. Yahoo! Japan • Yahoo! • Function( ) Copyright © 2019

    Oracle and/or its affiliates. https://developer.yahoo.co.jp/
  37. Yahoo! Japan • • • • URL • • Copyright

    © 2019 Oracle and/or its affiliates.
  38. Yahoo! Japan • URL • https://apex.oracle.com/ • https:// • URL

    • https://apex.oracle.com/pls/apex/apex_authentica tion.callback • https:// /ords/apex_authenticzatication.callback Copyright © 2019 Oracle and/or its affiliates.
  39. Yahoo! Japan Web • Channel ID ID • • Web

    Copyright © 2019 Oracle and/or its affiliates.
  40. Yahoo! Japan • • Web • OpenID Connect • URL

    URL URL Copyright © 2019 Oracle and/or its affiliates.
  41. Oracle APEX 1 2 3 4 5 Google Facebook LinkedIn

    Copyright © 2019 Oracle and/or its affiliates. 6 LINE 8 Oracle APEX 9 10 Yahoo! Japan 7
  42. • GitHub • access token GitHub JSON APEX => APEX

    • : https://developer.github.com/apps/building-oauth-apps/authorizing- oauth-apps/ • • API access_token • : https://webservice.rakuten.co.jp/document/oauth • OpenID Connect OAuth2 Copyright © 2019 Oracle and/or its affiliates.
  43. Oracle APEX 1 2 3 4 5 Google Facebook LinkedIn

    Copyright © 2019 Oracle and/or its affiliates. 6 LINE 8 Oracle APEX 9 10 Yahoo! Japan 7
  44. Oracle APEX 1 2 3 4 5 Google Facebook LinkedIn

    Copyright © 2019 Oracle and/or its affiliates. 6 LINE 8 Oracle APEX 9 10 Yahoo! Japan 7
  45. • APEX URL ID • f?p=APP_ID:PAGE_ID:SESSION_ID: . • Cookie ID

    • URL ID Cookie ID • URL ID • Copyright © 2019 Oracle and/or its affiliates.
  46. • URL • • URL • • APEX_UTIL.PREPARE_URL, APEX_PAGE.GET_URL API

    Copyright © 2019 Oracle and/or its affiliates. f?p=113:3:121906806886617::NO:RP:P3_EMPNO:%5C7698%5C&cs=3OwFUEXzPowKY….
  47. • • • • • • / / • •

    / / • Copyright © 2019 Oracle and/or its affiliates.