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

Slide 1

Slide 1 text

Slide 2

Slide 2 text

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.

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

Slide 6

Slide 6 text

(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.

Slide 7

Slide 7 text

(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.

Slide 8

Slide 8 text

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.

Slide 9

Slide 9 text

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Slide 12

Slide 12 text

• ( )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;

Slide 13

Slide 13 text

認証ファンクション(追加コード) 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; ユーザー情報を保持する表を作成初期ユーザーの投入

Slide 14

Slide 14 text

Slide 15

Slide 15 text

Slide 16

Slide 16 text

Slide 17

Slide 17 text

Slide 18

Slide 18 text

Slide 19

Slide 19 text

Slide 20

Slide 20 text

Slide 21

Slide 21 text

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 || '

' || 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 || '

' || chr(10); k := apex_json.g_values.NEXT(k); end loop; end post_auth;

Slide 22

Slide 22 text

Slide 23

Slide 23 text

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Slide 26

Slide 26 text

Slide 27

Slide 27 text

Slide 28

Slide 28 text

Slide 29

Slide 29 text

Slide 30

Slide 30 text

Slide 31

Slide 31 text

Slide 32

Slide 32 text

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.

Slide 33

Slide 33 text

Slide 34

Slide 34 text

Slide 35

Slide 35 text

Slide 36

Slide 36 text

Slide 37

Slide 37 text

Slide 38

Slide 38 text

Slide 39

Slide 39 text

Slide 40

Slide 40 text

Slide 41

Slide 41 text

Slide 42

Slide 42 text

Slide 43

Slide 43 text

Slide 44

Slide 44 text

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.

Slide 45

Slide 45 text

Slide 46

Slide 46 text

Slide 47

Slide 47 text

Slide 48

Slide 48 text

Slide 49

Slide 49 text

Slide 50

Slide 50 text

• 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.