Assignment Description

Ch7 DBMS Functions:           Exercise for BITS Distributors (15pts)

MySQL Metadata
A DBMS must store data about the data in a database and make it accessible to users. Data about the data in a database, or metadata, includes table descriptions and field definitions. This exercise will focus on the MySQL database and user access.

MySQL implements a sophisticated access control and privilege system that allows you to create comprehensive access rules for handling client operations and effectively preventing unauthorized clients from accessing the database system.

There is a database named mysql created automatically by MySQL installer. The mysql database contains five main grant tables. MySQL makes use of those tables to control the privileges in MySQL database server. Understanding those table is very important before you can implement your own flexible access control system.

Review the tutorial topics listed at the website above to explore and learn how this part of the MySQL administration works.

 Using the workbench, completing the following exercises for this assignment.

1_Connect to the mysql schema and show all tables, attributes and records as shown in the table DB:

2_Create 2 new users and assign passwords so that they will be prompted to enter a new password with their first login:

3_Grant privileges for each of the new users to permit them to perform all actions on all objects, such as selecting & updating from the BITS table:

4_Show all users and password expiration information and when the password was last changed as shown in the table USER:

5_ Show all granted privileges for one of your new users and then revoke privileges for updating and deleting from the table BITS for that user then show the updated grant privileges for that user: