ESAA Data Analytics, Oracle

Language: Layout:

--Note: Ignore the "ORA-9000: invalid SQL statement" message in case this pops up
--Exercise 1
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 1');
END;\\
SELECT PRODUCT, VERSION
FROM PRODUCT_COMPONENT_VERSION
WHERE PRODUCT LIKE '%Database%'\\
SELECT ACTION, VERSION, ID
FROM DBA_REGISTRY_HISTORY\\

--Exercise 2
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 2');
END;\\
SELECT UPPER(VALUE)
FROM V$PARAMETER
WHERE UPPER(NAME) = 'AUDIT_SYS_OPERATIONS'\\

--Exercise 3
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 3');
END;\\
SELECT UPPER(VALUE)
FROM V$PARAMETER
WHERE UPPER(NAME)='AUDIT_TRAIL'\\

--Exercise 4
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 4');
END;\\
SELECT UPPER(VALUE)
FROM V$SYSTEM_PARAMETER
WHERE UPPER(NAME)='GLOBAL_NAMES'\\

--Exercise 5
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 5');
END;\\
SELECT UPPER(VALUE)
FROM V$PARAMETER
WHERE UPPER(NAME)='O7_DICTIONARY_ACCESSIBILITY'\\

--Exercise 6
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 6');
END;\\
SELECT UPPER(VALUE)
FROM V$SYSTEM_PARAMETER
WHERE UPPER(NAME)='REMOTE_LISTENER' AND VALUE IS NOT NULL\\

--Exercise 7
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 7');
END;\\
SELECT UPPER(VALUE)
FROM V$SYSTEM_PARAMETER
WHERE UPPER(NAME)='REMOTE_LOGIN_PASSWORDFILE'\\

--Exercise 8
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 8');
END;\\
SELECT UPPER(VALUE)
FROM V$PARAMETER
WHERE UPPER(NAME)='SEC_PROTOCOL_ERROR_FURTHER_ACTION'\\

--Exercise 9
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 9');
END;\\
SELECT UPPER(VALUE)
FROM V$PARAMETER
WHERE UPPER(NAME)='SEC_RETURN_SERVER_RELEASE_BANNER'\\

--Exercise 10
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 10');
END;\\
select a.USERNAME, a.ACCOUNT_STATUS, a.CREATED, a.PROFILE, a.AUTHENTICATION_TYPE, b1.LIMIT as password_grace_time, b2.limit as password_lock_time, b3.limit as failed_login_attempts, b4.limit as password_life_time, b5.limit as password_reuse_max, b6.limit as password_reuse_time, b7.limit as password_verify_function
from DBA_USERS a, dba_profiles b1, dba_profiles b2, dba_profiles b3, dba_profiles b4, dba_profiles b5, dba_profiles b6, dba_profiles b7
where a.account_status in ('EXPIRED','OPEN')
and b1.resource_type = 'PASSWORD' and b2.resource_type = 'PASSWORD' and b3.resource_type = 'PASSWORD' and b4.resource_type = 'PASSWORD' and b5.resource_type = 'PASSWORD' and b6.resource_type = 'PASSWORD' and b7.resource_type = 'PASSWORD'
and b1.profile = a.profile and b2.profile = a.profile and b3.profile = a.profile and b4.profile = a.profile and b5.profile = a.profile and b6.profile = a.profile and b7.profile = a.profile and b1.resource_name = 'PASSWORD_GRACE_TIME'
and b2.resource_name = 'PASSWORD_LOCK_TIME' and b3.resource_name = 'FAILED_LOGIN_ATTEMPTS' and b4.resource_name = 'PASSWORD_LIFE_TIME' and b5.resource_name = 'PASSWORD_REUSE_MAX' and b6.resource_name = 'PASSWORD_REUSE_TIME' and b7.resource_name = 'PASSWORD_VERIFY_FUNCTION'
group by a.USERNAME, a.ACCOUNT_STATUS, a.CREATED, a.PROFILE, a.AUTHENTICATION_TYPE, b1.LIMIT, b2.limit, b3.limit, b4.limit, b5.limit, b6.limit, b7.limit\\

--Exercise 11
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 11');
END;\\
SELECT DISTINCT A.USERNAME
FROM DBA_USERS_WITH_DEFPWD A, DBA_USERS B
WHERE A.USERNAME = B.USERNAME
AND B.ACCOUNT_STATUS = 'OPEN'\\

--Exercise 12
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 12');
END;\\
SELECT *
FROM DBA_USERS
WHERE USERNAME IN ('BI','HR','IX','OE','PM','SCOTT','SH')\\

--Exercise 13
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 13');
END;\\
SELECT USERNAME
FROM DBA_USERS
WHERE AUTHENTICATION_TYPE='EXTERNAL'\\

--Exercise 14
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 14');
END;\\
SELECT PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE GRANTEE='PUBLIC'
AND PRIVILEGE='EXECUTE'
AND TABLE_NAME='DBMS_ADVISOR'\\

--Exercise 15
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 15');
END;\\
SELECT PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE GRANTEE='PUBLIC'
AND PRIVILEGE='EXECUTE'
AND TABLE_NAME='UTL_FILE'\\

--Exercise 16
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 16');
END;\\
SELECT PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE GRANTEE='PUBLIC'
AND PRIVILEGE='EXECUTE'
AND TABLE_NAME='DBMS_SCHEDULER'\\

--Exercise 17
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 17');
END;\\
SELECT GRANTEE, PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE ADMIN_OPTION='YES'
AND GRANTEE NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y')\\

--Exercise 18
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 18');
END;\\
SELECT GRANTEE, PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE PRIVILEGE='SELECT ANY DICTIONARY'
AND GRANTEE NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y')\\

--Exercise 19
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 19');
END;\\
SELECT GRANTEE, PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE PRIVILEGE='SELECT ANY TABLE'
AND GRANTEE NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y')\\

--Exercise 20
BEGIN
DBMS_OUTPUT.PUT_LINE('EXERCISE 20');
END;\\
SELECT AUDIT_OPTION, SUCCESS, FAILURE
FROM DBA_STMT_AUDIT_OPTS\\


absolute service time: 3,15 sec