CSIT115 Connect to MySQL as a User Root Either Through Command-line Interface MySQL Data Management and Security Assignment, UOW

Aim

The objectives of this assignment include:

• tasks related to the implementation of discretionary access control, granting system resources, verification of complex consistency constraints,
• tasks related to using backup and restore features of DBMS to find the differences between two states of a relational table, and implementation of a simple auditing system.

Prologue

Download and unzip a file A3-all-files.zip. You should get the files Assignment3.pdf, A3create.sql, A3load.sql, A3drop.sql, A3change1b.sql, and A3change2b.sql. Copy the files to your USB drive such that you can access all files either through command-line interface MySQL or graphical user interface MySQL Workbench.

The script files create and load data into a database that contains information about books, authors, and customers. The database also contains information about the types of purchases.

Task 1 – Part A

• Connect to MySQL as a user root either through command-line interface MySQL or graphical user interface MySQL Workbench and create a new database with a name the same as <prefix of your UOW email account>.
• While connected as a user root use SQL script A3create.sql to create the relational tables in a database created in the previous step. A script A3create.sql creates the relational tables that can be used to store information about books, authors, customers, purchases, online purchases, and walk-in purchases. Execute a script A3load.sql to load data into the relational tables that created by A3create.sql. You can use a script A3drop.sql to drop the relational tables. Do not drop the relational tables now!

No report is expected from the implementation of the steps listed above.

Implement a SQL script that performs the following actions as a user root.

• Create three users with the following user names: <prefix of your UOW email account>_1

<prefix of your UOW email account>_2 <prefix of your UOW email account>_3

For example, if your UOW email prefix is abc123 then the names of users are abc123_1, abc123_2, and abc123_3. Set all passwords to be the same as the user names. For example, if the username is abc123_1, the password should be set to abc123_1.

• Next, the script grants to the user <prefix of your UOW email account>_1 to alter relational tables, to drop relational tables, and to read and write relational tables in a database with the same name as <prefix of your UOW email account>. The privilege must be granted such that the user <prefix of your UOW email account>_1 is able to grant all privileges listed above to the other users.
• Next, the script grants to the user <prefix of your UOW email account>_2 to create views, and read data from the relational tables BOOK and AUTHOR in a database with the same name as <prefix of your UOW email account>. The privilege must be granted such that the user <prefix of your UOW email account>_2 is NOT able to grant all privileges listed above to the other users.
• Next, the script sets the following values of resource limits to a user <prefix of your UOW email account>_3: total number of queries an account owner can issue per hour must be set to 100, and the total number of updates an account owner can issue per hour must be set to 10.
• Next, the script expires the password of the account <prefix of your UOW email account>_2
• Finally, the script lists the privileges granted to all new users, <prefix of your UOW email account>_1, <prefix of your UOW email account>_2, <prefix of your UOW email account>_3, the values of resource limits set in a step (4) and a status of <prefix of your UOW email account>_2 set in a step (5). To do so your script must access appropriate relational tables in a database MySQL. Do not list information NOT related to the actions performed above!

Task 1 – Part B

• Connect to MySQL either through command-line interface MySQL or graphical user interface MySQL Workbench as user csit115 and execute a script file A3drop.sql and immediately after that the scripts A3create.sql and A3load.sql to refresh the contents of a database csit115. Exit command-line interface MySQL or graphical user interface MySQL Workbench.
• Create a logical backup of a relational table BOOK and save it in a file with the same name as <prefix of your UOW email account>.bak.
• Connect as a user csit115 to MySQL either through command-line interface MySQL or graphical user interface MySQL Workbench and execute a script file A3change1b.sql.
• Use a text editor and modify a backup file obtained in step (2) such that a backup of a relational table BOOK can be restored into a relational table with the same name as a <prefix of your UOW email account>_DOC.
• Use an updated backup file <prefix of your UOW email account>.bak to load the contents of the backup into a relational table <prefix of your UOW email account>_DOC. DO NOT delete the backup file!

No report is expected from the implementation of the steps listed above.

Implement SQL script that finds the differences between the contents of a relational table BOOK and a relational table with the same name as <prefix of your UOW email account>_DOC.

The script must first list the rows added to the relational table BOOK after the backup file was created, then the rows deleted from a relational table BOOK after the backup file was created, and finally list the rows changed in relational table BOOK after the backup file was created.

In brief, the script must first list all added rows, then all deleted rows, and finally, all changed rows in a relational table BOOK. It is allowed to use more than one SELECT statement to implement this task.

Task 2 – Part A

Connect to MySQL either through command-line interface MySQL or graphical user interface MySQL Workbench as user csit115 and execute a script file A3drop.sql and immediately after that the scripts A3create.sql and A3load.sql to refresh the contents of a database csit115.

No report is expected from the implementation of the steps listed above.

Implement a SQL script that performs the following actions.

• The script finds all cases that violate in a database csit115 the following consistency constraint.

“A purchase which is an online purchase should NOT be a walk-in purchase”

The script must list the outcomes of verification of the consistency constraint as a single column table with the following messages as the following rows.

Purchase with the purchase ID of <insert PurchaseID here> is an online purchase of type <insert OnlineType here> and also a walk-in purchase of location <insert ShopLocation here >

Use a function CONCAT to create the messages above. It is NOT allowed to use more than one SELECT statement to implement this task.

Task 2 – Part B

In this task, you will implement your own simple method of auditing the database activities.

Connect to MySQL as a user root either through command-line interface MySQL or graphical user interface MySQL Workbench and execute a script file A3drop.sql and immediately after that execute script A3create.sql and A3load.sql to refresh a database csit115.

No report is expected from the implementation of the steps listed above.

Implement a SQL script that performs the following actions.

• First, the script sets the appropriate values of the variables that allow creating a general log, to save a general log in a relational table, and to start recording a general log from now.
• Next, the script makes a relational table that contains a general log empty.
• Next, the script executes a script file A3change2b.sql. (Do NOT put results of the execution of script A3change2b.sql into a report.)
• Next, the script sets the appropriate values of all variables that stop recording a general log from now.
• Next, the script lists the DDL statements (CREATE, ALTER, DROP) processed in a period of time when a general log was recorded.
• Next, the script lists the DML statements (SELECT, INSERT, DELETE, UPDATE) processed in a period of time when a general log was recorded. Sort the results in descending order of the total number of times a DML statement has been processed.

The post CSIT115: Connect to MySQL as a User Root Either Through Command-line Interface MySQL: Data Management and Security Assignment, UOW appeared first on Assignment Help Singapore No 1 : Essay & Dissertation Writers, SG.