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

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

1. Oracle APEX 2019 10 24 Oracle APEX Copyright © 2019 Oracle and/or its affiliates.
2. 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.
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 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
5. Oracle APEX • APP_USER • APP_USER ( ) Copyright © 2019 Oracle and/or its affiliates. 選択可能な認証スキーム 英語表記
6. (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.
7. (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.
8. 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.
9. 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
10. • • Sentry Function • true false • • Sentry Function false • • true, false • • Copyright © 2019 Oracle and/or its affiliates.
11. / 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
12. • ( )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;
13. 認証ファンクション(追加コード) 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; ユーザー情報を保持する表を作成 初期ユーザーの投入
14. • • • https://raw.githubusercontent.com/ujnak/apexapps/master/exports/custom- auth-sample.sql Copyright © 2019 Oracle and/or its affiliates.
15. 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
16. Google • Google APIs Developer Console • • Copyright © 2019 Oracle and/or its affiliates. https://console.developers.google.com
17. 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.
18. Google Web • Web OAuth2 ID • Web Copyright © 2019 Oracle and/or its affiliates.
19. Google • • Web • Google • Copyright © 2019 Oracle and/or its affiliates.
20. Google • PL/SQL • post_auth • URL Google URL • https://accounts.google.com/Logout Copyright © 2019 Oracle and/or its affiliates.
21. 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;
22. Google • OAuth2 • Google • APEX 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. Facebook • Facebook for Developers • Copyright © 2019 Oracle and/or its affiliates. https://developers.facebook.com/apps
25. Facebook • • ID app secret Web Copyright © 2019 Oracle and/or its affiliates.
26. 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.
27. Facebook Web • Facebook ID ID • Facebook app secret • Web Copyright © 2019 Oracle and/or its affiliates.
28. Facebook • • Web • Facebook • 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. LinkedIn • LinkedIn for Developers • Create app Copyright © 2019 Oracle and/or its affiliates. https://www.linkedin.com/developers/apps
31. 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.
32. 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.
33. LinkedIn Web • LinkedIn Client ID ID • LinkedIn Client Secret • Web Copyright © 2019 Oracle and/or its affiliates.
34. LinkedIn • • 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. LINE • LINE Developers ( ) • LINE • • • LINE Developers ( ) • • • Copyright © 2019 Oracle and/or its affiliates. https://developers.line.biz/console/
37. LINE • ( ) • Channel ID Channel Secret Web ID Copyright © 2019 Oracle and/or its affiliates.
38. 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.
39. LINE Web • LINE Channel ID ID • LINE Client Secret • Web Copyright © 2019 Oracle and/or its affiliates.
40. LINE • • Web • OAuth2 • 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. Yahoo! Japan • Yahoo! • Function( ) Copyright © 2019 Oracle and/or its affiliates. https://developer.yahoo.co.jp/
43. Yahoo! Japan • • • • URL • • Copyright © 2019 Oracle and/or its affiliates.
44. 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.
45. Yahoo! Japan Web • Channel ID ID • • Web Copyright © 2019 Oracle and/or its affiliates.
46. Yahoo! Japan • • Web • OpenID Connect • URL URL URL Copyright © 2019 Oracle and/or its affiliates.
47. 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
48. • • APEX_AUTHENTICATION= Copyright © 2019 Oracle and/or its affiliates.
49. • • Copyright © 2019 Oracle and/or its affiliates.
50. • 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.
51. 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
52. Oracle APEXの認可スキーム • Copyright © 2019 Oracle and/or its affiliates.
53. • • • Copyright © 2019 Oracle and/or its affiliates.
54. • • Copyright © 2019 Oracle and/or its affiliates.
55. • • Copyright © 2019 Oracle and/or its affiliates.
56. • • • Copyright © 2019 Oracle and/or its affiliates.
57. • • APEX_ACL API • ADD_USER_ROLE • REMOVE_USER_ROLE Copyright © 2019 Oracle and/or its affiliates.
58. • • Copyright © 2019 Oracle and/or its affiliates.
59. • • Copyright © 2019 Oracle and/or its affiliates.
60. • Copyright © 2019 Oracle and/or its affiliates.
61. 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
62. • • • • Cookie • Copyright © 2019 Oracle and/or its affiliates.
63. • 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.
64. • • URL • • URL Copyright © 2019 Oracle and/or its affiliates.
65. • 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….
66. • • • • • • / / • • / / • Copyright © 2019 Oracle and/or its affiliates.
67. APEX Copyright © 2019 Oracle and/or its affiliates.