TransactionsandSecurityImplementations.docx

Transactions and Security Implementations

Outcomes addressed in this activity:

Unit Outcomes:

· Use Data Control Language (DCL) statements that manage database user permissions.

· Utilize the Transaction Control Language (TCL) statements that manage changes made by Data Manipulation Language (DML) statements.

· Generate database views to help maintain data confidentiality.

Course Outcome:

IT234-4: Discover more advanced SQL such as security commands and logins.

Purpose

Data security is critical in an organization. In this unit, you will learn how to create roles, how to create users and assign them to roles, and how to grant and revoke privileges on database tables.

Assignment Instructions

Please watch the Unit 9 videos covering facets associated with database security and transaction control. Navigate to the Academic Tools area of this course and select Library then Required Readings to access your texts and videos.

You will need to change the authentication method used by Microsoft SQL Server in order to complete this assignment. Open the Microsoft SQL Server Management Studio (SSMS) application using the standard Windows authentication method as illustrated below.

The 'Connect to Server' prompt in the Microsoft SQL Server Management Suite (SSMS) application.

Right-click on the SQL Server instance at the top of the tree in the Object Explorer window. Select the Properties item from the right-click menu.

The right-click menu showing the location of the SQL Server instance Properties item.

The Server Properties window will appear. Click on the Security link at the left side of the window. Select the “SQL Server and Windows Authentication mode” option in the “Server authentication” list. Click on the OK button when finished.

The Server Properties window showing the SQL Server authentication options.

You’ll receive a prompt indicating you’ll need to restart the SQL Server instance. Click on the OK button to close out the window.

Microsoft SQL Server prompt specifying that the instance needs to be restarted in order for implemented changes to go into effect.

Right-click on the SQL Server instance at the top of the tree in the Object Explorer window. Select the Restart item from the right-click menu.

The right-click menu showing the location of the SQL Server instance Restart option.

You may receive a User Account Control prompt. Click on the YES button to continue.

The User Account Control (UAC) prompt asking whether application changes should be implemented.

You will then receive a restart prompt. Click on the YES button to proceed.

Prompt verifying the SQL Server instance restart.

You’ll then see a restart progress window. It will close out once the restart is complete.

Progress bar showing the status of the SQL Server instance restart.

You can now proceed with work on the assignment problems below. Your assignment submittal needs to show both the generated SQL statements and confirmatory screenshots verifying task completion.

Problem 1: Create a view called EmployeeDirectory that displays the first name, last name, title, and phone extension of all employees in the company.

Problem 2: Create a stored procedure that increases an employee’s salary by a raise percentage. The skeleton of the stored procedure is provided below.

CREATE PROCEDURE GiveEmployeeRaise

@EmployeeID INT, @RaisePercentage DECIMAL

AS

<REPLACE WITH YOUR SQL CODE>

The equation for computing a new salary is as follows:

New Salary = Old Salary * (1 + Raise Percentage/100)

If an employee with EmployeeID = 9 gets a 5% raise, the stored procedure call would be as follows:

EXEC GiveEmployeeRaise @EmployeeID = 9, @RaisePercentage = 5;

Problem 3: Create and execute a transaction block that contains two DML statements. The first statement updates the title for  all employees to “President.” The second statement inserts a new region record with a RegionID = 10 and a RegionDescription = “Antarctica.” Incorporate these statements within the SQL block specified below:

BEGIN TRANSACTION

<REPLACE WITH INSERT/UPDATE STATEMENTS>

SELECT * FROM Employees;

SELECT * FROM Region;

ROLLBACK TRANSACTION

SELECT * FROM Employees;

SELECT * FROM Region;

Execute the completed SQL block in a Microsoft SSMS query window.

Briefly explain what happened with the execution of this transaction. Provide screenshots of the data before and after the ROLLBACK TRANSACTION statement. Please note the query results will appear in 4 separate sections in the Results area of Microsoft SSMS following execution of the above SQL block.

Problem 4: You are asked to add three new products to an existing order with OrderID = 11061. The additional records need to be added to the OrderDetails table with the following information:

Record 1

OrderID = 11061

ProductID = 62

UnitPrice = 45

Quantity = 10

Discount = 0

Record 2

OrderID = 11061

ProductID = 70

UnitPrice = 14

Quantity = 25

Discount = 0

Record 3

OrderID = 11061

ProductID = 1000

UnitPrice = 100

Quantity = 5

Discount = 0

Incorporate the SQL insert statements for the new records into the transaction block specified below and execute in a Microsoft SSMS query window:

BEGIN TRANSACTION NewOrderDetails

BEGIN TRY

<REPLACE WITH INSERT STATEMENTS>

COMMIT TRANSACTION NewOrderDetails;

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION NewOrderDetails

END CATCH

SELECT * FROM OrderDetails

WHERE OrderID = 11061;

Briefly explain what happened with the execution of this transaction. Do the new records get inserted into the OrderDetails table? If not, why?

Problem 5: Create four new roles in the Northwind database:

· SalesPerson

· SalesManager

· HRperson

· HRmanager

Problem 6: Use Data Control Language (DCL) statements that manage database user permissions.

· Grant select, insert, and update permissions for Sales-related tables (Orders & OrderDetails) to the SalesPerson role.

· Grant select and delete permissions for Sales-related tables to the SalesManager role.

· Grant select permissions for the EmployeeDirectory view to the SalesPerson and SalesManager roles.

· Grant select, insert, and update permissions for HR-related tables (Employees & EmployeeTerritories) to the HRperson role.

· Grant select and delete permissions for HR-related tables to the HRmanager role.

· Grant execute permission for the GiveEmployeeRaise stored procedure to the HRperson role

Problem 7: Create four new users named Jane, Joan, Joe, and James. Use the CREATE LOGIN and CREATE USER commands to accomplish the work. Each established Northwind database user account must be associated with an applicable SQL Server login account (e.g., CREATE USER Jane FOR LOGIN Jane). Please note, you must establish the SQL Server login accounts before the database-level usernames. Use the following password for each of the four SQL Server login accounts:  P@$$w0rd

Problem 8: Grant the roles specified below to Jane, Joan, Joe, and James.

· Give Jane the role of SalesPerson.

· Give Joan the role of SalesManager

· Give Joe the role of HRperson.

· Give James the role of HRmanager

Use the ALTER ROLE command to accomplish the role granting work.

ALTER ROLE <role_name> ADD MEMBER <user_name>;

Problem 9: In Microsoft SSMS, right-click on the SQL Server instance at the top of the tree in the Object Explorer window. Select the Connect item from the right-click menu.

The right-click menu showing the location of the SQL Server instance Connect option.

The login prompt will appear. Select “SQL Server Authentication” from the Authentication drop down box. Enter “Jane” and “P@$$w0rd” into the Login and Password fields, respectively. Click on the Connect button when finished.

The 'Connect to Server' prompt in the Microsoft SQL Server Management Suite (SSMS) application showing the SQL Server Authentication option.

You will now be connected to the SQL Server instance as “Jane.”

The Microsoft SSMS Object Explorer window pane showing a new connection to the SQL Server instance as a user called 'Jane'.

Expand the Databases item under “Jane.” Right-click on the Northwind database item and select the New Query item in the right-click menu.

The right-click menu showing the option for invoking a New Query window.

Generate SQL statements in the query window to do the following:

· Update the order quantity to 50 for OrderID = 10249 and ProductID = 51.

· Delete the record in OrderDetails with OrderID = 10251 and ProductID = 65.

· Select all of the records from the Employees table

· Select all of the records from the EmployeeDirectory view established in Problem 1

Provide screenshots of the SQL statement outputs. Provide a brief explanation for any statement that failed to execute

Problem 10: Connect to the Northwind database as “Joan” using the steps specified in Problem 9. Generate SQL statements in the query window to do the following:

· Update the order quantity to 60 for OrderID = 10249 and ProductID = 51.

· Delete the record in OrderDetails with OrderID = 10251 and ProductID = 65.

· Select all of the records from the Employees table

· Select all of the records from the EmployeeDirectory view established in Problem 1

Provide screenshots of the SQL statement outputs. Provide a brief explanation for any statement that failed to execute

Problem 11: Connect to the Northwind database as “Joe” using the steps specified in Problem 9. Generate SQL statements in the query window to do the following:

· View all of the records contained in the Orders table.

· Give a 5% raise to the employee with EmployeeID = 9 using the GiveEmployeeRaise stored procedure

· Select all of the records from the Employees table

· Select all of the records from the EmployeeDirectory view

Provide screenshots of the SQL statement outputs. Provide a brief explanation for any statement that failed to execute

Problem 12: Connect to the Northwind database as “James” using the steps specified in Problem 9. Generate SQL statements in the query window to do the following:

· Give a 10% raise to the employee with EmployeeID = 8 using the GiveEmployeeRaise stored procedure established in Problem 2

· Update the title to “Gamemaster” for the employee with EmployeeID = 9

· Select all of the records from the Employees table

· Select all of the records from the EmployeeDirectory view

Provide screenshots of the SQL statement outputs. Provide a brief explanation for any statement that failed to execute

Assignment Requirements

Microsoft SQL Server Express and SQL Server Management Studio (SSMS) MUST be installed to complete this Assignment.

Compose your Assignment in a Word document and be sure to identify yourself, your class, and unit Assignment at the top of your paper. Embed the screenshots of your SQL statements and confirmatory output (e.g., table structure definitions) into the Word document.

image6.png

image7.png

image8.png

image9.png

image10.png

image11.png

image12.png

image1.png

image2.png

image3.png

image4.png

image5.png

Transactions and Security Implementations

Introduction

Transactions are a fundamental feature of OLTP systems. In transactional systems, you must be able to read a record and then write it back without losing data. All transactions have a beginning and an end. Transaction isolation levels ensure that transactions in different applications do not interfere with each other. When you run a transaction, the system must return all changes that occur within the transaction to your application. An ACID compliant system ensures that all users get an accurate record of the data in the database at any point in time. A DBMS is responsible for managing updates and inserts into records, but it does not guarantee that no one else updates or deletes records while your insert statement is processing

Transactions are a fundamental feature of OLTP systems.

A transaction is a way to ensure that all changes made by a particular user are applied to the database as a single unit. A transaction begins when you issue an INSERT, UPDATE or DELETE statement and ends when you issue another command that conflicts with it.

A transactional system can be characterized by its ability to support multiple users working on the same data at the same time without any loss of integrity or integrity violations. The basic requirement for such an implementation is that data should be managed in units called “transactions” so that each user has her own copy of it, which means there should be no conflicts between transactions.

In transactional systems, you must be able to read a record and then write it back without losing data.

A transaction is an atomic unit of work that can be committed or rolled back. A transaction can only be started by one user and must be completed by any other user. In order to perform a complex action like updating multiple records in a database, it’s important that you have control over your data at all times. You don’t want another user messing around with your data while you’re still working on it!

If your system allows multiple users to access the same set of objects at once (such as databases), then this type of isolation level is called ACID compliant because ACID stands for atomicity (the ability to guarantee what has happened), consistency (the ability for two separate operations running concurrently) and durability (the ability for one operation to permanently change another).

All transactions have a beginning and an end.

All transactions have a beginning and an end. Transactions are the fundamental feature of OLTP systems, because they structure your data in such a way that it can be easily accessed, manipulated, and recovered. Transactions also allow you to group together sets of operations that are related to each other–for example, when an update has been made on one row in your database table but not all rows need to be updated at once (because for example, only one column needs changing).

Transactions help ensure data integrity by making sure that each operation is performed before another one begins (or after it ends). In addition to this basic functionality, transactions can also be thought of as a series of steps that must all happen within some given amount of time; this is why we call them “executable” rather than “atomic”.

Transaction isolation levels ensure that transactions in different applications do not interfere with each other.

Transaction isolation levels ensure that transactions in different applications do not interfere with each other. The transaction isolation level determines how much information is shared between transactions, and it determines whether a transaction can see data or changes made by another transaction.

The different isolation levels are:

  • Read Committed (RC) – This is the default mode for MySQL 5.7 and newer, which means that all reads are fully deterministic. Transactions will see any changes made by previous transactions, but they won’t affect any subsequent operations until those operations complete successfully or fail due to errors such as duplicate rows being inserted or updated.
  • Repeatable Read (RR) – RR allows multiple read operations against one table at once; however, they only return data once–the last time someone called ‘SELECT * FROM xxx’ on your table returns no results because there were no new records inserted since then! This feature can be useful when you need to clear out old data quickly without losing important information about what’s been done so far (such as what happened yesterday).

When you run a transaction, the system must return all changes that occur within the transaction to your application.

When you run a transaction, the system must return all changes that occur within the transaction to your application.

In order to ensure that no changes are lost and no data is overwritten by another transaction, they are returned in order that they were made. This can be critical because not all users would agree on one ordering of events (for example, what should happen if two people try to run their own transactions at once). The solution is simple: after each change has been made and committed or rolled back, it’s sent back out again with any errors so it can be checked for consistency before being applied again. If there is an error during processing then all changes associated with this particular transaction will be rolled back without fail–even if other parts of your codebase cannot handle such errors properly themselves!

An ACID compliant system ensures that all users get an accurate record of the data in the database at any point in time.

An ACID compliant system ensures that all users get an accurate record of the data in the database at any point in time. This can be achieved by using a transaction log and locking mechanism that supports atomicity, consistency, isolation and durability.

Atomicity – All changes are made to the database in one transaction; No partial update is allowed.

Consistency – Once a change has been committed it must appear consistent with all other versions of your data within your application or database schema (including other databases), otherwise it may be rolled back if required by some external constraint (e.g., an update manager).

Isolation – Transactions should not interfere with each other so each transaction sees a consistent view into its own state without interference from others’ requests; No two transactions can see overlapping data structures unless explicit synchronization mechanisms exist within them such as optimistic locking or pessimistic locking strategies

A DBMS is responsible for managing updates and inserts into records, but it does not guarantee that no one else updates or deletes records while your insert statement is processing.

A DBMS is responsible for managing updates and inserts into records, but it does not guarantee that no one else updates or deletes records while your insert statement is processing.

A good example of this would be if you were inserting a new user account into the database. The DBMS can guarantee that no one else will update or delete your users record while it’s being created. But if someone tries before it’s finished with their own insert statement on another table (for example), then your insert statement might fail because there are now two different versions of that same row in memory at once–one from each source, which would cause confusion as to which was correct. This could lead to errors being thrown when trying to execute queries against those tables later down the line!

You need to be careful when dealing with transactions because they’re easy to get wrong if not done correctly

Transactions are a powerful tool, but they’re also easy to get wrong if not done correctly. When you use transactions in your application, you should always be aware of what happens when things go wrong:

  • If a transaction fails due to an error in the data or logic (e.g., missing required fields), that transaction will roll back and all changes made by previous transactions will be undone.
  • If multiple transactions try to modify the same record at the same time, only one will succeed; any others will fail with an exception indicating there was an issue with their data source or execution environment–for example, if someone tries to run more than one copy of your app on their phone at once!

Conclusion

Transactions are a fundamental feature of OLTP systems. In transactional systems, you must be able to read a record and then write it back without losing data. All transactions have a beginning and an end. Transaction isolation levels ensure that transactions in different applications do not interfere with each other. When you run a transaction, the system must return all changes that occur within the transaction to your application. An ACID compliant system ensures that all users get an accurate record of the data in the database at any point in time

Get 20% Discount on This Paper
Pages (550 words)
Approximate price: -

Try it now!

Get 20% Discount on This Paper

We'll send you the first draft for approval by at
Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Ace Writing Center has stood as the world’s leading custom essay writing services providers. Once you enter all the details in the order form under the place order button, the rest is up to us.

Essays

Essay Writing Services

At Ace Writing Center, Nowadays, students normally have extremely busy schedules. You will note that some of them have to take on some evening or weekend jobs in order to get some income that can help them to sustain in college or in the university. This can deny them a chance to write all the essays given. Others usually get bombarded with a lot of work by their lecturers. This can still delay such students from working on all their essays. However, some of them usually try to work on all these essays but end up delivering their work late. This can prevent them from graduating since most lecturers are strict on deadlines. If you want to write a business essay, the wise way is to hire an outstanding essay writing service like us, so that you can get the best results. If you are keen, you will note that many companies usually overcharge their customers. Some of them are there only to make money. And in reality, they really don’t care to build a long term commitment with students. You should not choose such companies. You should take your time and choose a reliable company to work with. Ace Writing Center is the ultimate solution for you. We have been offering our writing service for more than 7 years. This is a clear indication that you will get quality essay writing service. We have a wide range of writers who can work on any business essay that you might have. We believe in doing extensive research so that we can provide quality work to all our clients. .

Admissions

Admission and Business Papers

Have you ever had to write an admission essay for college? The majority of students face the same issues when applying to a university or college and many in such situations decide they need professional help to cope with this matter. They get in a situation when the deadline keeps coming closer but lack motivation to start because they are just not sure if their writing skills are strong enough. We have a solution for you! Ace Writing Center is the best admission essay writing service with a large professional team and years of experience in providing high-quality papers to students of all levels and faculties. The mission of our team is to help students make their dreams of entering a good college come true and that’s what we offer!.

Editing

Editing and Proofreading

Sometimes all the words for your paper just flow out of your mind and into your fingers. You type quickly at your keyboard and there they are, your beautiful words right there on the screen. But you have no idea how to polish it up. You may be wishing there was a paper writing service that offered this type of writing service. Look no more! Here at Ace Writing Center, we offer you an editing and proofreading option that you can't find anywhere else..

Coursework

College Essay Writing

In case you are familiar Ace Writing Center, you know the way to distinguish a better company from a cheap one exactly. First of all, poor service website does not have a sufficient support. We think support team is an essential part of success; it has to answer all clients’ questions and be a connecting link between clients and their writers. On our web-service you will get answers about anything you need and your writer will receive all your instructions, assignments and requirements exactly and swiftly. A writing service that we run has got a flexible pricing system that will save you from senseless wastes and many bonus systems that let you sparing money for something important for you.

Open chat
1
Hello. Can we help you?