The problem As a Database Administrator (DBA), you have been provided with the following account detailing the

Fantastic news! We've Found the answer you've been seeking!

Question:

The problem As a Database Administrator (DBA), you have been provided with the following account detailing the access security criteria for a SQL database system. The database consists of three tables: CUSTOMER, ORDER-RECEIPT, and ORDER-ITEM. The CUSTOMER table is indexed based on the name and street number attributes. The ORDER-RECEIPT table is indexed based on the date-of-receipt attribute and the foreign key from the CUSTOMER table. Lastly, the ORDER-ITEM dataset is indexed based on the item-name attribute and the foreign key from the ORDER table. The purpose of the system is to facilitate the processing of sales orders. The sales office is responsible for managing the entry and retrieval of documents as well as monitoring the status of orders. The office now employs a total of seven staff members. Tracey, the supervisor, requires comprehensive visibility and authority to modify all aspects of the system. Bill, Sheila, and Govind primarily handle regular tasks, nevertheless, they are particularly unable to generate new clientele. There exist other additional factors. It is imperative to establish a restriction wherein anybody other than Tracey and Govind are unable to process orders over RM1000. Additionally, it is imperative to guarantee that a temporary staff member possesses the capability to handle order processing tasks, while being restricted from accessing customer information. Tasks: 1. Produce an analysis and the necessary SQL statements to handle the problems. 2. Implement the database, populate it with test data and apply your security statements. 3. Test your access control mechanism thoroughly. 4. If, when you see the sample solution, either your analysis or testing are faulty, you need to find out why. 5. Indicate any additional SQL security measures that could be taken. 6. Comment on the strengths and weaknesses of the measures you have taken. The following activities will help you through solving the problem, step by step. Task 1 - Constructing the database schema Utilizing SQL's data definition statements, a script is generated to create three tables, wherein table 1 establishes a relationship with multiple rows of table 2, which in turn establishes a relationship with multiple rows of table 3. It is possible that you already possess a database schema that can be utilized for this objective; however, it is vital to verify that the primary and foreign keys are accurately declared, regardless of the scenario. Activity 2 - Populating the database This is a security exercise so the data itself need not be to normal test data standards. Make sure that the data is easily recognisable - use meaningful values and remember that foreign key fields should be present. Activity 3 - Analysing the problem In order to maintain clarity and simplicity for a first try, the names provided will be treated as user-roles. Construct a matrix that delineates the relationship between users and database resources. The database resources under consideration for this exercise include the basic tables and views, which are also referred to as virtual tables. The matrix will provide clarity regarding the allocation of access permissions and the corresponding authorized individuals. The aforementioned needs can be easily implemented by utilizing a SQL script consisting of GRANT statements. Please transcribe the script into paper and verify it by human inspection. Activity 4 - Executing the security script (if you have a DBMS that permits this) If an individual lacks access to an execution environment, it is advisable to seek the assistance of a peer in order to obtain constructive feedback on their current progress. Activity 5 - Testing the access control (if you have a DBMS that permits this) Generate SELECT statements based on the problem definition and execute them to verify their accurate implementation. In the event that any issues are encountered, it is advisable to rectify them promptly at the specific location where they have arisen. Activity 6 - Conclusion Please provide an overview of any further security measures that could be implemented in SQL, and offer an evaluation of the strengths and weaknesses of the mechanisms already implemented. Activity 7 - Postscript To what extent did you perform? Remember Tracey? Following the completion of her tasks, she believed that she deserved a salary increase. The individual inquired, but was denied, thereafter returning to her workstation. In order to address these inquiries, it is important to consult the database design and security script at your disposal. 1. Tracey subsequently attempted to remove the CUSTOMER table. Did she achieve success? 2. I am hopeful that the situation being referred to does not occur. However, if it were to transpire, I am curious as to the underlying reasons that would contribute to its manifestation. Did you unintentionally grant her system administration privileges? 3. Subsequently, she attempted to remove certain clientele from the database. Did she achieve success? Did the deletions propagate in a cascading manner? 4. The individual made an attempt to include a clause in every purchase over RM1000 for a quantity of 500 coffee machines. Did she achieve success? 5. And how was the problem detected? 6. Did she attempt to modify her password? Did she achieve success? 7. To what extent can an individual be bestowed with privilege? This is the sample database CREATE DATABASE SALES; USE SALES; Create Table CUSTOMER ( CustId Varchar (10) Not Null Unique, CustName VarChar (50), CustAddress Varchar (50), CustPhoneNo Char (12), Primary Key (CustId) ); INSERT INTO CUSTOMER (CustId, CustName, CustAddress, CustPhoneNo) VALUES ('C1001', 'Siti Aminah Ahmad', 'Taman Melawati Gombak', '013256778'), ('C1002', 'Ahmad Fadly', 'Taman Melewar Gombak', '0143456778'), ('C1003', 'Siti Asmah Ali', 'Bandar Tasik Selatan', '011256778'), ('C1004', 'Ainul Mardhiyah Razali', 'Taman Conought Cheras ', '018956778'), ('C1005', 'Muhammad Faizul', 'Sec 3, BB Bangi', '019987888'); Select * from Customer; Create Table ORDER_ITEM ( ItemNo VarChar (10) Not Null Unique, ItemName VarChar (30), Quantity INT Default 0, Price Decimal (5,2) Default 999.99, Primary Key (ItemNo) ); INSERT INTO ORDER_ITEM (ItemNo, ItemName, Quantity, Price) VALUES ('T1001', 'PENBLUE', 1000, 1.25), ('T1002', 'PENCIL2B', 1100, 1.23), ('T1003', 'PENCIL2A', 900, 1.30), ('T1004', 'PENBLACK', 850, 1.23), ('T1005', 'PENRED', 800, 1.20); Select * from ORDER_ITEM; Create Table ORDER_RECEIPT ( OrderNo INT Not Null Unique, OrderDate Date, CustId VarChar(10), ItemNo VarChar (10), Primary Key (OrderNo), Foreign Key (CustId) References CUSTOMER (CustId), Foreign Key (ItemNo) References ORDER_ITEM (ItemNo) ); show tables; INSERT INTO ORDER_RECEIPT (OrderNo, OrderDate, CustId, ItemNo) VALUES (1001, '2019-12-12', 'C1002', 'T1002'), (1002, '2018-05-12', 'C1004', 'T1001'), (1003, '2020-01-11', 'C1001', 'T1002'), (1004, '2020-05-01', 'C1005', 'T1003'), (1005, '2020-08-10','C1003', 'T1003'); Select * from ORDER_RECEIPT; we must fix the problem based on this sample database.