SQL Securing Database
skip to main contentMy HomeIN301M1 Secure Database DevelopmentUpdate alerts – You have new alertsNHNicki HamiltonContentDiscussionsAssignmentsQuizzesGradesMore ToolsHelp
Bookmarks0 bookmarked topics
Course Schedule0 upcoming events
Table of Contents
Table of Contents17Incomplete activities
Getting Startedmodule: contains 1 sub-modules
Syllabusmodule: contains 0 sub-modules
Course Resourcesmodule: contains 0 sub-modules5Incomplete activities
Digital Bookmodule: contains 0 sub-modules1Incomplete activities
Academic Toolsmodule: contains 0 sub-modules4Incomplete activities
Learnmodule: contains 1 sub-modules
Connectmodule: contains 2 sub-modules5Incomplete activities
Practicemodule: contains 2 sub-modules
Competency Assessmentmodule: contains 1 sub-modulesselected2Incomplete activities
Assessmentmodule: contains 0 sub-modules2Incomplete activities
To receive credit, you must complete the Competency Assessment. You may contact your professor if you have any questions.
This Competency Assessment (CA) assesses your level of performance on the specific outcome(s) that are the focus of this course. The CA requires you to demonstrate your knowledge, understanding, and proficiency of the outcome(s). Be sure to use the other activities in this course prior to attempting the CA. You can attempt the CA an unlimited number of times within the term. However, the CA must be completed successfully by the end of the term to earn credit. You can attempt the CA an unlimited number of times within the term. However, the CA must be completed successfully by the end of the term to earn credit.
Be sure to review the Competency Assessment Checklist Rubric in order to understand the criteria on which you will be evaluated.
Instructors will provide feedback on the CA. If you expect to receive feedback, revise, and resubmit this CA near the end of the term, you will need to make your submission no later than 5 days before the end of the term.
Please refer to your syllabus for additional Competency Assessment requirements.
Expand All Collapse All0 %0 of 2 topics completeShow data table for This chart displays the number of completed topics versus the total number of topics within module Competency Assessment..
List of Topics and Sub-Modules Purpose
Part 1 will provide a review of SQL. You will also apply threat modelling concepts and use the DREAD model to find risks for two threats.
Part 2 will provide an overview of various big data security concerns, such as authentication, permissions, and data accessibility. You will also explore possible solutions to some security concerns specific to big data ecosystem. Finally, you will secure shell and transfer a file to/from the Hadoop virtual machine.
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.
If you do not have the above software installed, please perform the required installations. The following document will assist you with installation of the software: SQL Server Setup.
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 screenshot 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 next action on a new page.
Show a person’s name, credit card number, and type.
Show a store name and year-to-date sales. Group by name and year-to-date values.
Create three tables with the following parameters:IN301: primary key field, vulnerability_id, solution_id and organization. All fields require a value 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. Be sure to only show the organization with values.
Using the Adventure Works design document, identify important data assets. Explain your table selections.
Keeping those important data assets in mind, develop a DREAD threat rating for the below. Include a brief description of which had the higher risk rating and explain why.
Excessive privilege abuse.
In order to obtain mastery level for this Competency Assessment, complete the following:
Apply the Microsoft Threat Modeling Tool™ to model a case of a mobile client sending a request to a database. Show the database returning a response to the mobile client. 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 above software installed, please perform the required installations. The following document will assist you with installation of the software: Hadoop Setup.
Complete the following:
For each of the actions, as applicable, use the Hortonworks Hadoop HDP virtual machine environment.
Please copy the numbered action into your Microsoft Word document.
Respond to items 1–3. For items 4–5 below, provide a screenshot of the execution showing the command and the result set.
Start a next action on a new page.
Other than authentication, explore a big data security concern in depth. Explain what it is, why it is a security concern and possible solutions.
Other than Kerberos, research what Hadoop uses to ensure secure authentication, permissions, and/or data accessibility.
Explain how Kerberos works in the Hadoop ecosystem.
Secure shell to the Hadoop virtual machine.
Transfer a file to and from your host and Hadoop virtual machine.
Minimum Submission Requirements
This Assessment should be a Microsoft Word document, containing all screenshots and requirements as indicated in the individual instructions for each part, in addition to the title and reference pages.
Respond to the questions in a thorough manner, providing specific examples of concepts, topics, definitions, and other elements asked for in the questions. Your submission should be highly organized, logical, and focused.
Your submission must be written in Standard English and demonstrate exceptional content, organization, style, and grammar and mechanics.
Your submission should provide a clearly established and sustained viewpoint and purpose.
Your writing should be well ordered, logical and unified, as well as original and insightful.
A separate page at the end of your submission should contain a list of references, in APA format. Use your textbook, the Library, and the internet for research.
Be sure to cite both in-text and reference list citations where appropriate and reference all sources. Your sources and content should follow proper APA citation style. For more information on APA style formatting, go to Academic Writer, formerly APA Style Central, under the Academic Tools area of this course.
Your submission should:
include a cover sheet;
be typed in Times New Roman, 12 -point font;
include correct citations
be written in Standard English with no spelling or punctuation errors; and
include correct references at the bottom of the last page.
If work submitted for this competency assessment does not meet the minimum submission requirements, it will be returned without being scored.