Question: QUESTION 4 (10 marks) Assume that the user root with a password 'DPIT115' created a database called transport and the user 'root' executed CREATE TABLE

QUESTION 4 (10 marks) Assume that the user root with a

QUESTION 4 (10 marks) Assume that the user root with a password 'DPIT115' created a database called transport and the user 'root' executed CREATE TABLE statements given on page 2 of the examination paper to create the relational tables in the database transport. Write a SQL script that performs the following operations the user 'root'. Assume that the user 'root' has already connected to the database. (1) The user 'root' nominates a database transport as a default database, and then the user creates two roles 'driver' and 'admin'. (1 mark) (2) The user 'root' grants read access rights to the relational tables EMPLOYEE and DRIVER to the role 'admin'. The read access rights cannot be propagated to other roles or users. (1 mark) (3) The user 'root' grants the rights to insert the rows into a relational tables TRIP and TRIPLEG to the role 'driver'. The access rights can be propagated to other roles or users. (1 mark) (4) The user 'root' grants the update privilege on all relational tables in the transport database to the role 'admin'. The privilege cannot be propagated to other roles or users. (1 mark) (5) The user 'root' grants the read access rights to information about the total number of trips performed by each driver to a role 'driver'. (1 mark) (6) The user 'root' creates five (5) new users and grants the role 'driver' to two (2) of the users, and the role 'admin' to the all other users. The names and passwords of the new user accounts are up to you. (1 mark) (7) The user 'root' sets the resource limits for the users created in the previous step allowing ten (10) maximum concurrent connections. Finally, the user 'root' locks all the user accounts created in the previous step. (1 mark) (8) Explain in your own words the difference between Authentication and Authorisation. Use original examples that you make up yourself and diagrams that you draw yourself to illustrate your answer. (3 marks) Add your code into the question4.sql template provided and output your report file on your virtual machine to a file named question4.rpt

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

QUESTION 4 (10 marks) Assume that the user root with a password 'DPIT115 created a database called transport and the user 'root' executed CREATE TABLE statements given on page 2 of the examination...

9 of 10 nagement and Security Final Assessment (Summer 2020) QUESTION 6 (10 marks) Assume that the user 'root' with a password 'DPIT115' created a database called transport and the user 'root'...

Assume that a user root with a password 'csit 1 1 5 ' created a database called banks. The user executed CREATE TABLE statements given on page 4 of the examination paper to create the relational...

Assume that a user root with a password 'root' has created a database called THERAPY and inside the database the user root has created the tables given on page 4 of the examination. ( Note: you are...

Please write down again and make sure which sentence corrects which line? QUESTION 3 (10 marks) Write the data manipulation statements of SQL that modify the contents of a database listed on page 2...

( 2 ) Assume that CREATE TABLE statements given on page 4 of the examination paper are included in a script file dept.sql . Assume that a user root with a password 'root' would like to create a...

PROJECT SCOPE [Instructions for what to include in this section: Define the scope of work that will be undertaken to provide the deliverable(s) mentioned in the Project Charter (PC). Craft this...

read the question and answer question 2. the Er diagram is given already. use that to create the relational mode. Consider the design of a database for a web site that allow people to share sunset...

can someone solve this Modern workstations typically have memory systems that incorporate two or three levels of caching. Explain why they are designed like this. [4 marks] In order to investigate...

Database Administration CHAPTER OBJECTIVES I Understand the need for and importance of database I ttno'illI the meaning of ACID transaction administralin - Learn the tour 1992 ANSI standard isolation...

Assume that you have been invited to advise the newly established accounting oversight body in one of the former Eastern European countries that became a member of the EU in May 2004. The accounting...

Since the UK Code of Corporate Governance was updated in 2018 there have been more major failings in how some large companies in the UK are managed. In 2020, FRC surprised that in many cases...

A student registration system is proposed. Some of the requirements include pieces of information like data being manipulated, information about individual students, information about individual...

Calculate the minimum and maximum takt time for the given

Stereotype threat, arises when one is in a situation or doing something for which a negative stereotype about ones group applies (Steele 1997:614) and has the potential to lower the motivation and...

1-4 How will MIS help my career?

The Social Justice case for D&I has been argued in strategic human resource management literature as having longer term impact in building an inclusive culture, as opposed to the Business Case,...