|
|
| Legend: |
| Primary key columns |
| Columns with indexes |
| Implied relationships |
| Excluded column relationships |
| < n > number of related tables |
|
|
| Column |
Type |
Size |
Nulls |
Auto |
Default |
Children |
Parents |
| LOGIN_ID |
varchar2 |
100 |
|
|
|
|
|
| AGNCY_CD_AGENCY_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| AGENCY_DESC |
varchar2 |
40 |
√ |
|
null |
|
|
| TITLE |
varchar2 |
20 |
√ |
|
null |
|
|
| UNIT_ID |
varchar2 |
255 |
√ |
|
null |
|
|
| FNAME |
varchar2 |
50 |
√ |
|
null |
|
|
| LAST_LOGIN_DATE |
date |
7 |
√ |
|
null |
|
|
| LNAME |
varchar2 |
50 |
√ |
|
null |
|
|
| LOGINS_FAILED |
number |
2 |
√ |
|
null |
|
|
| PASSWORD_CHANGED_DATE |
date |
7 |
√ |
|
null |
|
|
| ACCOUNT_STATUS |
varchar2 |
3 |
√ |
|
null |
|
|
| DEFAULT_SCHEMA |
varchar2 |
3 |
√ |
|
null |
|
|
| DISABLED_DATE |
date |
7 |
√ |
|
null |
|
|
| STATUS |
varchar2 |
3 |
√ |
|
null |
|
|
| SCHEMA_DESCRIPTION |
varchar2 |
4000 |
√ |
|
null |
|
|
| ADDRESS1 |
varchar2 |
100 |
√ |
|
null |
|
|
| ADDRESS2 |
varchar2 |
100 |
√ |
|
null |
|
|
| CITY |
varchar2 |
100 |
√ |
|
null |
|
|
| STATE |
varchar2 |
30 |
√ |
|
null |
|
|
| ZIP |
varchar2 |
20 |
√ |
|
null |
|
|
| COUNTY_THEME |
varchar2 |
30 |
√ |
|
null |
|
|
| FAX |
varchar2 |
30 |
√ |
|
null |
|
|
| PASSWD_ATTEMPTS_ALLOWED |
number |
10 |
√ |
|
null |
|
|
| PASSWD_EXPIRATION_DAYS |
number |
10 |
√ |
|
null |
|
|
| PASSWORD_HISTORY_COUNT |
number |
0 |
√ |
|
null |
|
|
| ACCNT_EXPIRATION_DAYS |
number |
0 |
√ |
|
null |
|
|
| SCHEMA_STATUS |
varchar2 |
2 |
√ |
|
null |
|
|
| THEME_NAME |
varchar2 |
30 |
√ |
|
null |
|
|
Analyzed at Mon Sep 20 21:05 MDT 2021
|
View Definition:
SELECT login_id,
IUP.AGNCY_CD_AGENCY_CODE,
AC.AGENCY_DESC,
IUP.TITLE,
IUP.UNIT_ID,
EU.FNAME,
EU.LAST_LOGIN_DATE,
EU.LNAME,
EU.LOGINS_FAILED,
EU.PASSWORD_CHANGED_DATE,
EU.ACCOUNT_STATUS,
UES.DEFAULT_SCHEMA,
UES.DISABLED_DATE,
UES.STATUS,
-- EUA.IP_ADDRESS,
-- EUA.LOGIN_DATE_TIME,
-- EUA.LOGOUT_DATE_TIME,
-- EUA.SYSTEM_MAC_ID,
ES.SCHEMA_DESCRIPTION,
ES.ADDRESS1,
ES.ADDRESS2,
ES.CITY,
ES.STATE,
ES.ZIP,
ES.COUNTY_THEME,
ES.FAX,
ES.PASSWD_ATTEMPTS_ALLOWED,
ES.PASSWD_EXPIRATION_DAYS,
ES.PASSWORD_HISTORY_COUNT,
ES.ACCNT_EXPIRATION_DAYS,
ES.STATUS AS schema_status,
ES.THEME_NAME
FROM IJIS_USER_PROFILES iup
JOIN AGENCY_CODES AC ON IUP.AGNCY_CD_AGENCY_CODE = AC.AGENCY_CODE
JOIN EJS_USERS@EJSLOOKUPDBC eu USING (LOGIN_ID)
JOIN EJS_USERS_SCHEMAS@EJSLOOKUPDBC ues USING (LOGIN_ID)
--JOIN EJS_USER_AUDIT@EJSLOOKUPDBC eua USING (LOGIN_ID) -- has a history of all logins may not want to have this data?
JOIN EJS_SCHEMAS@EJSLOOKUPDBC es USING (SCHEMA_ID)
JOIN INSTALL INST ON UPPER(ES.DB_CONNECTION_STRING) = UPPER(INST.DB_SCHEMA) -- to keep dup loginid from other Schemas
WHERE EU.CC_ADMIN != 'Y' -- remove all CC_admin acounts from the report
AND UPPER(ES.SCHEMA_TYPE)='SCHEMA_TYPES' and UPPER(ES.SCHEMA_CODE)='COUNTY'
Possibly Referenced Tables/Views:
