Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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 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
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started