QuestionQuestion

Transcribed TextTranscribed Text

Assignment 3 This assignment includes hands-on lab exercises on Oracle VM. It has two Parts. Part 1 is Database Administrative Lab and Part 2 is Database Hardening Lab. Part 1. Database Administration Lab Purpose The goal of the lab is to demonstrate, through hands-on exercises, how to perform select database administrative tasks. Setup This lab should be performed under the Oracle Linux VM provided in the course. 1. Start your Oracle Linux VM through the Oracle VM VirtualBox Manager. 2. Login as the oracle OS user. 3. Open a terminal window by double-clicking the terminal icon (Figure 1). Figure 1. Terminal icon 4. Login to SQL*Plus as the Sys user as sysdba as shown below (Figure 2). The default password is oracle. Page 2 of 8 Figure 2. SQL*Plus Session 5. Create a new USER with your name. The password should be the same as your first name. Allocate the following parameters: default TABLESPACE users, TEMPORARY TABLESPACE temp, QUOTA 10M ON users, PASSWORD EXPIRE, ACCOUNT UNLOCK. You can allocate any profile. 6. Alter the user password that you created in question 5. Change it to your last name. 7. Create a user Profile called STUDENT. Assign your own resource and password limits. 8. Verify the results of the STUDENT profile by querying dba_profiles. 9. Alter the Idle_Time for the STUDENT profile. 10. Alter User (Your_Name) to Use STUDENT profile. 11. Show all values in the DEFAULT profile. 12. Create a Password Profile - limit failed login attempts to 2, password life time to 15, PASSWORD_REUSE_TIME to DEFAULT, and PASSWORD_REUSE_MAX is equal to 1. 13. Grant Select to Any Table to User Your_Name. Page 3 of 8 14. Revoke Select to Any Table granted in question 13. 15. What data dictionary view can be used by a DBA to view system privileges granted to users? Show all columns in this view. 16. What does the following query do? SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'SCOTT'; 17. Grant Select to User Your_Name on DEPT table. 18. Revoke Select to User Your_Name for DEPT. 19. Show all object privileges granted to user YOUR_NAME. Note: If necessary, grant an object privilege first (e.g. repeat command in Question 13) to get meaningful results. 20. Show all object privileges granted to the current user. 21. Show how to display all object privileges granted to other users. Note: Create some users and grant them privileges first. Depending on the tables you use for this exercise, you might need to login with a privileged account and grant yourself privileges WITH GRANT OPTION. 22. Create A Role called Developer. 23. Grant Create Session to Developer. 24. Grant Developer Role to Your_Name. 25. Create a database link using FIXED USER credentials. Page 4 of 8 Part 2. Hardening the Database Lab Introduction Database hardening is the systematic process of securing your DBMS configuration from unauthorized access and security threats. Limiting access to database resources reduces the overall security risks to your system. There are many guides and checklists available for securing an Oracle database configuration. The following guides are referenced in this lab:  Oracle Database Security Checklist  The Center for Internet Security (CIS) Benchmark For Oracle Database 12c Other comprehensive hardening guidelines are available at:  Security Technical Implementation Guides (STIGS) - Database Security Purpose The goal of the lab is to demonstrate, through hands-on exercises, ways to secure an Oracle database instance. This lab is a general hardening exercise and is not comprehensive, but does cover key areas for securing your Oracle database. Setup This lab should be performed under the Oracle Linux VM provided in the course. 1. Start your Oracle Linux VM through the Oracle VM VirtualBox Manager. 2. Login as the oracle OS user. 3. Open a terminal window by double-clicking the terminal icon (Figure 1). Figure 1: Terminal Icon 4. Login to SQL*Plus as the Sys user as sysdba as shown below (Figure 2). The default password is oracle. Page 5 of 8 Figure 2: SQL*Plus Session Lock and Expire Default or Unused User Accounts For security reasons you don't want to leave user accounts open in your database that are not used or needed. You can choose to lock the accounts or remove the user accounts. In this exercise you will lock the accounts and expire the passwords of users that will not be used in the labs. For this course we use the Oracle sample schemas, but in a production environment it is best practice not to install the sample schemas or remove them if they have been installed. 1. In SQL*Plus (as Sys as sysdba), run a query that shows the username and account status of all of the database users in the dba_users data dictionary view. 2. Use alter user statements to lock the accounts of all users and expire the passwords of all users except the Sys, System, App, HR, and Scott users. Ex. Lock and expire: SQL> alter user OBE account lock password expire; Ex. Expire only: SQL> alter user DVF password expire; 3. Run a query again on dba_users to show that the Sys, System, App, HR, and Scott accounts are “OPEN” and all other accounts are “EXPIRED & LOCKED”. Note: The dba_users_with_defpwd data dictionary view will give you all users in an 18c database that are using default passwords. Page 6 of 8 Implement Password Verification "Choosing secure passwords and implementing good password policies are by far the most important defense for protecting against password based security threats." (“Oracle Database Security Checklist,” 2008, p. 4) In this exercise you will change the default profile to alter the default parameters for password management including using a password verification function. The script, utlpwdmg_for_674.sql, is a modified version of the Oracle script utlpwdmg.sql provided with an 11g install and is edited to included Oracle and CIS security recommendations. utlpwdmg_for_674.sql creates a password verification function, "verify_function_18c", and assigns it to the default profile. The password verification function ensures that passwords are created with a minimum length of 10, and that passwords contain at least one digit, one character, and one symbol. The function also checks the password's complexity and checks it against the username and previously used passwords. 1. Open a new terminal window and view the script using the less command. [oracle@localhost /]$ less /home/oracle/674_scripts/utlpwdmg_for_674.sql 2. In SQL*Plus (as Sys as sysdba), run the utlpwdmg_for_674.sql script: SQL> @/home/oracle/674_scripts/utlpwdmg_for_674.sql Change the Passwords of Administrative Accounts It is good security practice not to use the same passwords for the Oracle administrative accounts. In this exercise you will change the default passwords of the Sys and System accounts. 1. With the password command or alter user statement change the passwords of the Sys and System users. Create a unique password for each user. SQL> password system Changing password for system New password: Retype new password: Password changed Note: The “Old Password” for Sys is oracle. Note: You can also change passwords using the syntax “alter user <user> identified by <password>”. Page 7 of 8 Note: Be sure that the new passwords are a minimum length of 10, and that the passwords contain at least one digit, one character, and one symbol. Verifying DBA Privileges The Oracle DBA role should only be granted to those users who really need DBA privileges. Be selective and use caution with the DBA role. In this exercise you will look at all of the accounts that have the DBA role and revoke the DBA role from any user that does not need the role. 1. In SQL*Plus (as Sys as sysdba), run a query that shows the users that are assigned the DBA privilege excluding the Sys and System users. SQL> select grantee from dba_role_privs where granted_role='DBA' and grantee not in ('SYSTEM','SYS'); 2. If there are any users with the DBA privilege other than Sys or System, revoke the DBA privilege. SQL> revoke DBA from <user> Note: Replace <user> above with the user(s) returned in step 1. Verifying Roles It is good security practice to drop any predefined or user defined roles that are not used. In this exercise you will look at all of the roles in the database and drop the “manager” role because it is no longer used. 1. In SQL*Plus (as Sys as sysdba), run a query that shows all of the roles in the database. SQL> SELECT * FROM DBA_ROLES; 2. Drop the MANAGER role. SQL> drop role manager; Verify the REMOTE_OS_AUTHENT Parameter "Setting REMOTE_OS_AUTHENT to TRUE can cause a security exposure, because it lets someone using a non-secure protocol, such as TCP, perform an operating system authorized Page 8 of 8 login (formerly referred to as an OPS$ login)." (“Oracle® Database Advanced Security Administrator's Guide 11g Release 2 (11.2),” n.d.). In this exercise you will verify that the REMOTE_OS_AUTHENT parameter is set to FALSE. The default parameter in 18c is FALSE. 1. In SQL*Plus (as Sys as sysdba), run the following command to show the value of the REMOTE_OS_AUTHENT parameter. SQL> show parameter REMOTE_OS_AUTHENT Audit Operations of Sys user and SYSDBA and SYSOPER privileges It is good security practice to audit the activities of the sys user and those users authenticated with SYSDBA or SYSOPER. In this exercise you will verify that the AUDIT_SYS_OPERATIONS parameter to TRUE. 1. In SQL*Plus (as Sys as sysdba), run the following command to verify the value of the AUDIT_SYS_OPERATIONS parameter. SQL> show parameter AUDIT_SYS_OPERATIONS; Note: The audit records by default are written to $ORACLE_HOME/rdbms/audit, in UNIX/Linux, and the Event Log in Windows. Other Security Considerations Other security considerations include:  Only install the Oracle components needed for your database environment  Stay current and install Oracle security patches as well as OS security patches  Change the default name and port of the listener  Do not use the default SID, “ORCL” References: 1. (2008, June). Oracle Database Security Checklist. Retrieved from http://www.oracle.com/technetwork/database/security/twp-security-checklistdatabase-1- 132870.pdf 2. Cecchetti, A. (2008, September). Security Configuration Benchmark For Oracle Database Server 11g Version 1.0.0. Retrieved from https://www.cisecurity.org/tools2/oracle/CIS_Oracle_11g_Benchmark_v1.0.pdf 3. Oracle® Database Advanced Security Administrator's Guide 11g Release 2 (11.2). (n.d.). Retrieved from http://download.oracle.com/docs/cd/E11882_01/network.112/e10746/asoauth.htm#ASOAG9 769

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

    By purchasing this solution you'll be able to access the following files:
    Solution.docx.

    $18.00
    for this solution

    or FREE if you
    register a new account!

    PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

    Find A Tutor

    View available Anthropological Curation Tutors

    Get College Homework Help.

    Are you sure you don't want to upload any files?

    Fast tutor response requires as much info as possible.

    Decision:
    Upload a file
    Continue without uploading

    SUBMIT YOUR HOMEWORK
    We couldn't find that subject.
    Please select the best match from the list below.

    We'll send you an email right away. If it's not in your inbox, check your spam folder.

    • 1
    • 2
    • 3
    Live Chats