CMU step 8: check the setup and a login

1CLEAR COLUMNS2SET LINESIZE 200 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN name           FORMAT A264COLUMN value          FORMAT A125COLUMN username       FORMAT A256COLUMN external_name  HEADING "MAPPED TO" FORMAT A70 WORD_WRAPPED7COLUMN account_status HEADING "STATUS" FORMAT A168COLUMN role           FORMAT A309COLUMN schema_user    HEADING "SCHEMA" FORMAT A2010COLUMN ad_login       HEADING "AD LOGIN" FORMAT A2511COLUMN ad_dn          HEADING "AD DN" FORMAT A50 WORD_WRAPPED12COLUMN method         HEADING "METHOD" FORMAT A1613COLUMN id_type        HEADING "MAPPING" FORMAT A1614COLUMN ldap_type      HEADING "LDAP" FORMAT A415 16PROMPT LDAP parameters in this container17SELECT name, value18FROM v$parameter19WHERE name LIKE 'ldap_directory%'20ORDER BY name;21 22PROMPT Wallet location from CMU_WALLET (no rows: default or sqlnet.ora location)23SELECT p.property_value AS name, d.directory_path AS external_name24FROM database_properties p25LEFT JOIN dba_directories d ON d.directory_name = p.property_value26WHERE p.property_name = 'CMU_WALLET';27 28PROMPT Database users mapped to AD29SELECT username, external_name, account_status30FROM dba_users31WHERE authentication_type = 'GLOBAL'32ORDER BY username;33 34PROMPT Roles mapped to AD groups35SELECT role36FROM dba_roles37WHERE authentication_type = 'GLOBAL'38ORDER BY role;39 40PROMPT Who this session is. As an AD user expect PASSWORD_GLOBAL, GLOBAL SHARED or GLOBAL EXCLUSIVE, and AD41SELECT SYS_CONTEXT('USERENV', 'SESSION_USER')           AS schema_user,42       SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') AS ad_login,43       SYS_CONTEXT('USERENV', 'ENTERPRISE_IDENTITY')    AS ad_dn,44       SYS_CONTEXT('USERENV', 'AUTHENTICATION_METHOD')  AS method,45       SYS_CONTEXT('USERENV', 'IDENTIFICATION_TYPE')    AS id_type,46       SYS_CONTEXT('USERENV', 'LDAP_SERVER_TYPE')       AS ldap_type47FROM dual;48 49PROMPT Roles enabled in this session50SELECT role51FROM session_roles52ORDER BY role;