CST 620 Project 5

ADDITIONAL LAB GUIDANCE

Below is a list of additional guidance and/or recommendations for your lab experience report:

· Completing the labs: All sections or parts of the labs should be completed as required.

· Answering the lab questions: You are required to answer all the lab questions (if any).

· Taking screenshots: While taking screenshots is recommended in your lab, try to limit them, and only focus on the applicable ones to support your lab report.

· Writing your lab experience report: You are required to write a summary of the lab experience report based on your findings and incorporate them into your final deliverables.

· Using a file name convention: Please change the generic file name of this template to reflect part of your name, the course ID, or the project/lab title.

· e.g. 1: CST620 Project 5 Lab-Securing Database Management Systems

· e.g. 2: CST620 Project 5 Lab-Securing Database Management Systems—John Doe

· e.g. 3: CST620-Project 5 Lab_Securing Database Management Systems (7/15/22)

In compiling your findings, think of how your experience performing the labs is related to the overall project goals. You are required to collect information from the lab to understand potential security challenges, analyze, develop your lab experience report, and incorporate key components in the final project report.

Please make sure to pay attention to each item above and use it as a supplemental guide in addition to the project requirements. Finally, note that successfully completing the lab is important for achieving the overall project goals.

As a cybersecurity consultant for APX Data Systems (ADS), you were tasked with leading a project team to implement database systems that conform to the HIPAA security rule for one hospital client. The database system should be capable of hosting highly sensitive information such as personally identifiable information (PII), personal health information (PHI), and electronic personal health information (ePHI) to comply with federal regulations. Part of your responsibility was to review, evaluate, and make recommendations with respect to the maintenance of a secure database system for the client. Based on the knowledge and experience gained from the lab, answer the following questions.

PART 2: STARTING THE LAB—Connecting to MySQL Server and Securing the Initial Root Account, Securing MySQL Account with Automated Secure Installation, Creating a New MySQL Admin Account with Privileges, etc.

1. You now know that the installation of MySQL creates only a root user account that has all privileges and can execute any database statement. However, if the root account has no password, the security of MySQL installation is obviously compromised in the sense that anyone can connect to the server as root and be granted all privileges. Besides configuring the security settings, what other security control measures can you implement to make MySQL more secure?

Figure 1. Creating Alternate User

Creating an alternate user with a password will make MySQL more secure because after the initial installation

of MySQL there is no password.

2. What other ways can you configure the security settings to secure a MySQL server installation?

Figure 2. Removals to make MySQL more secure

Figure 2 shows the removal of root login remotely, anonymous users, and access to test database during the

installation of MySQL. These removals can make MySQL more secure. The removal of anonymous users will

prevent access to users that do not have an account created for them. Disabling root login will only allow root

to connect with the local host. Removing access to database will prevent access to the database test that any

user will be able to access.

3. Using the secure MySQL installer script, how can you mitigate against having anonymous users’ unauthorized access to the database system?

As shown in figure 2, after you input the command “run mysql_secure_installation” remove anonymous users

will be prompted. Answering Y will mitigate against anonymous users being able to have access to the

database system.

4. How do you start and switch to MySQL on Kali Linux or a Linux OS in general? Where are MySQL database files stored in Linux? Also, what is the default port for MySQL Server?

The command “service mysql start” activates MySQL. The command “service mysql

status” can be used to verify it. MySQL database files are stored in /var/lib/mysql, and the

default port is 3306.

5. Once you set up databases, users, and permissions, consider what the daily management of your MySQL databases, user accounts, and privileges look like. Developers, business users, contractors, vendors, and several others need access on a daily or regular basis. How will you manage database credentials as the infrastructure grows based on your experience in the lab so far?

Figure 3. Show Grants

Checking log files to see for unsuccessful and successful logins will help manage the database. This can be

used in tandem with checking the privileges of suspicious account based on the log files with the command

“SHOW GRANTS” as shown in figure 6.

6. How will you ensure each user has as much granular access and only performs specific tasks with assigned privileges and nothing more?

Checking each the users' privileges by using the command SHOW GRANTS will help ensure the right privileges

are set for the user as shown in figure 3.

PART 3: MANAGING AND SECURING MYSQL DATABASE SYSTEMS (DBMS)—Creating and Accessing MySQL Databases, Evaluating MySQL Access Control Systems and Account Management, Testing MySQL Access Control and Assigned Privileges, etc.

1. Instead of using 'cst620-admin'@'localhost' in the create user statement, one can decide to use wildcards as in ‘cst620-admin’@‘%’, where ‘%’ is the wildcard in place of localhost'. With wildcard, a user can connect from any client host, but this is not a best practice due to potential security risks. In your opinion, what potential security risks are likely to occur and what security control measures would you take to address it?

There is the potential of SQL injection and DDOS attacks. To prevent this from happening firewalls that are

configured correctly will help.

2. When a user attempts to connect to a MySQL server, the server accepts or rejects the connection based on Whether the user account is locked or unlocked as one condition. During the connection request, what else must happen for the server to verify a user after providing proper credentials? What constitutes a full identification and what role does it play in this regard?

After providing proper credentials, the server will need to check if it matches the hostname, username, and

password of what is stored in the database. Full identification can be seen in Figure 3, where a valid

password was put in for CST620-Admin and a connection id is displayed. This results in the complete process

of the successful request.

3. The MySQL server performs identity and credentials verification and accepts the connection only if specified conditions are satisfied. What does the server system use to perform identity and credentials verification? [Hint: the columns in the user table can provide a useful clue].

Figure 4. User table

User tables are used as shown in figure 4 to indicate whether user, host and passwords match.

4. Considering MySQL server authentication, is it possible for the client hostname and username combination of an incoming connection request to match more than one row in a user table? Why or why not?

It is possible for client hostname and username combination request to match more than one row in a user

table. This happens because more than one entry is put in for the same username and hostname.

5. In your opinion, and from MySQL security experience gained so far, why do you think creating remote user accounts instead of local-based accounts can create unintended security vulnerabilities and thus potential threats?

Remote user accounts add one entry point to the MySQL server. This can allow for potential attacks such as

brute force, dictionary, and denial of service attacks from threat agents.

6. Which one of the cst620-user, cst620-user1, and cst620-user2 users cannot deleted another user, database, or table? How can you determine this if at all possible?

Figure 5. User privileges

cst620-user2 is the user that does not have privileges to delete as shown in the images for figure 8. This is

seen by using the command “SHOW GRANTS For” + hostname + @ + hostname as shown in figure 5.

7. From a MySQL database security standpoint, your frontend applications may use scripts to interact with the backend database system. Assuming a malicious user or a hacker is trying to conduct SQL injection or cross-site scripting attack, even if the front-end application (e.g. forms) is compromised, why do you think it would still be a challenge for this attacker to alter backend MySQL statements and be able to manipulate the user-supplied data.

Front end security and backend security configuration are set up different and have different

vulnerabilities. It can be a challenge to alter backend MySQL statement if the configuration for security is

properly done.

8. Security misconfiguration related to such endpoints as application servers, web servers, security appliances, and other platforms pose huge security flaws to security professionals and business leaders. If a malicious actor happens to target your internal network, describe how properly configured MySQL database permissions and firewalls can mitigate any potential compromise?

MySQL database should be configured to give only a limited amount of user privileges. This is to prevent the

possible misuse of permissions. Properly configured MySQL database can prevent remote access,

anonymous user access, and potential access to a test database as shown in figure 2. Firewall can be

configured to block and accept specific types of traffic to the server. This would prevent potential threats.

9. Throughout this lab exercise, you witnessed how MySQL misconfiguration can pose security challenges to database security admins and the profession as a whole. Based on this knowledge do you think enhanced security is a by-product of good security administration? If not, why?

Figure 6. Create user and permissions

Enhanced security is a by-product of good security administration. As shown in figure 2, the administrator has the power to allow anonymous users, test database for access, and remote user access. Figure 6 shows the administrators ability to create users and assign privileges. Improper assignment of privileges can lead

To users having more access than needed, which can be abused.

10. Considering the access, version_id, plugin, authentication_string, and password_last_changed parameters of the structure of JSON inside the "Priv" column for cst620-user, what is/are the impact(s) on the MySQL security?

Figure 7. JSON Detailed

Figure 7 shows the detailed structure of JSON which shows the privilege of the user (access, version id,

plugin, authentication string, and password last changed parameters). These parameters tell the

database what a user is authorized to do. The version id parameter indicates the version that is used. The

plugin parameter indicates what is used for authentication decides which plugins are used. The authentication string parameter indicates the user’s password. The password last changed parameter

Indicates the last time the password was changed. This information conveys to the administrator the

configuration.

NOTE: Proceed to the next page and use the space provided to compile a summary of your lab experience report. Use additional space as necessary to complete the report.

SUMMARY OF THE LAB EXPERIENCE REPORT

Use the space below to summarize your lab experience report based on your findings from the lab, making sure to complete all required actions in each step of the lab and respond to all questions. Be sure to incorporate a key part of your findings in your final project report for submission to your professor. You may use additional space as necessary to complete the lab.

This lab involved MySQL RDBMS, how to start MySQL, how to secure a root account, create accounts, assigned

privileges, limit privileges, manage the database, and create a database. Throughout this lab many

configurations on the database and the data. The number of configurations that are required gives an insight

into how important it is to set up the database correctly. The installation setup process asked simple yes or no

questions to configurations that could have made a major impact on security. Some of the questions

include the use of anonymous users, root access, and test tables. The initial installation of MySQL creates a

user account with no password.

This can cause concern if the individual creating the database is not well versed in using and securing MySQL.

The lab emphasized the use of assigning privileges for the user accounts. For security measures, privileges are

limited to the authorized need for duties performed. The potential for abuse of privileges is higher when there

are more people with more privileges. Properly configuring MySQL should not be the only security measure put

in place. Firewalls should also be implemented to add another layer of defense from attacks. The lab

instruction indicated that the password used in the exercise was not strong and I agree. Passwords need to

have lower cases, uppercase, special characters, numbers, and at least 14 characters to ensure it is strong

against dictionary and brute force attacks. The administrator's configuration of the database dictates the

strength of security.