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.
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 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.
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.
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.
You may receive a User Account Control prompt. Click on the YES button to continue.
You will then receive a restart prompt. Click on the YES button to proceed.
You’ll then see a restart progress window. It will close out once the restart is complete.
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 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.
You will now be connected to the SQL Server instance as “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.
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
