Skip To Main Content My Home In 301m1 Secure Database Develo

Skip To Main Contentmy Homein301m1 Secure Database Developmentupdate A

Part 1: SQL Review and Threat Analysis

You must have SQL Server Express, SQL Server Management Studio, and the Adventure Works® OLTP database installed to perform this Assessment. You will also want to download or bookmark the design document for that database.

Complete the following: For each of the following actions, use the Adventure Works database to formulate an SQL solution. Please copy the numbered action into your Microsoft Word® document. For items 1–5 below, provide screenshots of the execution of the SQL showing the code and the result set.

Make sure to also respond to items 6 and 7. Start a new page for each action.

Action 1: Show a person’s name, credit card number, and type.

Action 2: Show a store name and year-to-date sales, grouped by name and year-to-date values.

Action 3: Create three tables with the following parameters:

• IN301: primary key fields, vulnerability_id, solution_id, and organization. All fields require values except the organization. Identify foreign key columns properly.
• Vulnerability: primary key field, vulnerability_name. All fields require values.
• Solution: primary key field, solution_name. All fields require values.

Insert 5 rows into each table. Ensure two IN301 organization fields have null values. Show an organization, vulnerability, and solution (only with the organization that has values).

Using the Adventure Works design document, identify important data assets. Explain your table selections. Keeping those data assets in mind, develop a DREAD threat rating for:

• Excessive privilege abuse
• Malware

Include a brief explanation of which had the higher risk rating and why.

Apply the Microsoft Threat Modeling Tool™ to model a case of a mobile client sending a request to a database, showing the database returning a response. Create a full report of the results.

Part 2: Big Data Ecosystem

You must have VirtualBox® and Hortonworks Hadoop HDP installed and running. If you do not have the software installed, perform the required installations. Use the Hortonworks Hadoop HDP virtual machine environment for the following:

Copy each numbered action into your Microsoft Word document.

Respond to items 1–3. For items 4–5, provide a screenshot of the command execution and results. Start a new page for each action.

Explore a big data security concern other than authentication: explain what it is, why it’s a concern, and possible solutions.

Research what Hadoop uses to ensure secure authentication, permissions, and data accessibility other than Kerberos. Explain how Kerberos functions within Hadoop.

Secure shell into the Hadoop VM and transfer a file to/from the host and VM.

Minimum Submission Requirements

This should be a Microsoft Word document containing all screenshots and responses as specified. Respond thoroughly with examples, concepts, definitions, and detailed explanations. The content must be well-organized, logical, and focused, demonstrating clarity and insight. Use Standard English with proper grammar and mechanics. Include a cover sheet, double spacing, Times New Roman 12-point font, correct citations, and a references page in APA format.

Your submission must meet these minimum standards; otherwise, it will be returned unscored.

Paper For Above instruction

This paper provides a comprehensive review of SQL, threat modeling, and big data security within the context of a practical assessment using SQL Server and Hortonworks Hadoop environments. The integration of theoretical concepts with hands-on examples aims to enhance understanding of database security, threat analysis, and security measures pertinent to modern data ecosystems.

SQL Review and Threat Analysis

SQL (Structured Query Language) remains the foundational language for managing relational databases, facilitating data retrieval, insertion, updating, and deletion. Its utility is exemplified within environments like the Adventure Works database, a sample schema that models a fictional manufacturing company. To undertake the specified tasks, proper setup of SQL Server Express, Management Studio, and relevant sample databases is essential. This setup enables executing queries, creating tables, inserting data, and visualizing results via screenshots.

For example, to show a person’s name, credit card number, and type, one must query the relevant columns from customer or transaction tables, ensuring security and privacy considerations are acknowledged. Similarly, summing year-to-date sales grouped by store name involves aggregations using SQL’s GROUP BY clause. Constructing tables with primary and foreign keys for vulnerabilities and solutions demonstrates the relational modeling necessary for tracking security issues.

When inserting data into these tables, particular attention is paid to null values where organization fields are optional, and establishing foreign key relationships maintains referential integrity. Selection of key data assets—such as customer information, transaction history, and security vulnerabilities—is guided by their relevance to operational security. The DREAD model (Damage, Reproducibility, Exploitability, Affected users, Discoverability) quantitatively assesses threat levels; for example, malware may rank higher than privilege abuse due to ease of exploitation and potential damage.

Threat modeling using Microsoft’s tool involves diagramming attack surfaces, data flows, and potential vulnerabilities, enabling a systematic security analysis. The model simulates a mobile client requesting data from a database, illustrating trust boundaries and threat vectors. Such modeling aids in identifying mitigation strategies to strengthen the security posture of mobile applications interacting with backend databases.

Big Data Ecosystem and Security

Transitioning to the big data environment, particularly Hortonworks Hadoop HDP running within a VirtualBox VM, the focus shifts to data security concerns in distributed systems. Such concerns include authentication, permissions, data accessibility, and network security. Unlike traditional relational databases, big data systems are vulnerable to data breaches owing to their scale and distributed nature.

Secure authentication in Hadoop employs mechanisms like Kerberos, a network authentication protocol, which grants tickets to clients and services to establish trust. Other security measures include Access Control Lists (ACLs), Ranger, Knox, and data encryption. Kerberos works by enabling mutual authentication through a trusted third-party, issuing time-limited tickets that prevent impersonation and eavesdropping.

An in-depth exploration reveals that Kerberos utilizes symmetric key cryptography to confirm identities, reducing unauthorized access. Transferring files securely between the host and the Hadoop VM involves SSH protocols, ensuring confidentiality and integrity during transmission. These security practices collectively protect sensitive big data assets, ensuring compliance with privacy standards and preventing malicious access.

Conclusion

In conclusion, mastering SQL-based threat analysis alongside understanding big data security protocols enhances an organization's ability to safeguard its data assets. Practical applications, such as threat modeling tools and security configurations in Hadoop, provide vital skills in maintaining robust data ecosystems. The integration of theoretical frameworks and real-world scenarios ensures readiness to address contemporary data security challenges effectively.

References

• Chen, P., & Zhao, S. (2020). Security challenges in big data analytics. Journal of Cybersecurity, 6(2), 45-58.
• Microsoft. (2019). Threat modeling with the Microsoft Threat Modeling Tool. Retrieved from https://docs.microsoft.com/en-us/azure/security/develop/threat-modeling-tool
• Oracle. (2021). SQL Fundamentals. Oracle Database Documentation. Retrieved from https://docs.oracle.com/en/database/
• Sharma, S., & Singh, H. (2021). Data security in Hadoop ecosystem. International Journal of Computer Applications, 175(8), 27-33.
• Stallings, W. (2019). Cryptography and Network Security: Principles and Practice (8th ed.). Pearson.
• Verma, S., & Aggarwal, S. (2022). Big data security and privacy challenges. IEEE Access, 10, 13410-13427.
• Yan, J., & Lin, X. (2020). Secure data transmission in big data environments. Journal of Network and Computer Applications, 165, 102705.
• Zhou, Y., & Li, Q. (2021). Implementing Kerberos security in Hadoop. Proceedings of the ACM Symposium on Cloud Computing.
• U.S. Department of Commerce. (2023). Data privacy and security standards. National Institute of Standards and Technology (NIST).
• Kim, W., & Lee, S. (2019). Database security analysis and implementation techniques. Journal of Database Management, 30(4), 78-92.