University Database Security Assignment: Configuring MySQL Database

This assignment focuses on configuring a MySQL database to ensure data security through user management and access control. The student creates a new user, 'jump', and assigns a password. They then load the 'classicmodels' database and grant the user specific privileges, including create, drop, alter, insert, update, select, delete, and index permissions. The assignment details the use of the GRANT command and explores different privilege levels, including static and dynamic privileges. Best practices for database security, such as physical security, firewall configuration, disabling unnecessary services, data encryption, and auditing, are also discussed. The student tests the implemented security measures by attempting to access databases with the newly created user account, demonstrating how the assigned privileges restrict or allow access. The conclusion emphasizes the importance of database security and the effectiveness of GRANT and REVOKE commands in securing a database.

Running head: CONFIGURE A MYSQL DATABASE
1
Assignment
Student
Course
Professor
Date

CONFIGURE A MYSQL DATABASE 2
Configure a MySQL Database
Database security refers to the measures that are taken to protect the database from
malicious usage. Security can be provided to the database at four levels, which includes a
database server, data stored in the database, DBMS (Database Management System), and work-
flow application (Glendon, 2011). The task of the assignment is to provide security to the
database named “classic models” for different users of the MySQL. Hence, the task at hand
involves creating the user and providing access to the classic model database for the particular
set of privileges with the help of grant command. Finally, determining how security is provided
to the database with access privileges.
Task
Creating Database User:
The database can be created with the following SQL statement:
Query:
CREATE USER 'jump'@'localhost' IDENTIFIED BY 'start';
Output:

CONFIGURE A MYSQL DATABASE 3
Loading Classicmodels Database:
The “classicmodels” database can be loaded with the help of the following command:
Query:
source C:\Users\Admin\Downloads\mysqlsampledatabase\mysqlsampledatabase.sql
Output:
Granting Privileges to the Classicmodels database
The SQL command to provide access to “classicmodels” database for creating, drop,
alter, insert, update, select, delete, and index privileges for the user are given below:

CONFIGURE A MYSQL DATABASE 4
Query:
Grant create, drop, alter, insert, update, select, delete, index on classicmodels.* To
'jump'@'localhost';
Output:
MySQL Grant Command
The grant command that is used to assign privilege of access to the database for the user
is given below (Tahaghoghi & Tahaghoghi, 2014):
Syntax:
GRANT
privilage_type [(column_list)]
[, privilage_type [(column_list)]] ...
ON [object_type] privilage_level
TO user_or_role [, user_or_role]
Some of the privilege levels are:
*.*
database name.*
database name.table name
table name

CONFIGURE A MYSQL DATABASE 5
database name.routine name
Furthermore, there are two types of privileges which are Static and Dynamic privilege.
1) Static Privileges
Static Privileges cannot be unregistered and are built into the server, and are available at
any given time to be granted or revoked privileges during runtime.
Some of the Static Privileges of a database for which permission can be granted or revoked are:
 ALL PRIVILEGES
 CREATE
 INSERT
 DELETE
 UPDATE
 DROP
 EXECUTE
 SELECT
 SHOW VIEW
 SHOW DATABASE
 TRIGGER etc.
2) Dynamic Privileges
Dynamic Privileges can be registered or unregistered at runtime. Furthermore,
unregistered privileges cannot be granted at runtime.
Some of the Dynamic Privileges of a database for which permission can be granted or revoked
are:
 SYSTEM_USER
 AUDIT_ADMIN
 BACKUP_ADMIN
 ROLE_ADMIN
 SET_USER_ID
Database Security

CONFIGURE A MYSQL DATABASE 6
Some of the best practices that can secure the database from malicious users are given below
(Basta & Zgola 2011):
 Provide physical security to the database by keeping the server in a locked, secure room.
 The database firewalls must not allow traffic to or from wrong users; hence, SQL
Injection attack must be prevented in web application.
 Provide more security to the database by disabling the services that are not needed.
 Encrypt the data in the database.
 Provide access to a minimum number of users.
 Audit and track database activity
 Secure password to the database
Therefore, these mechanisms can be used to protect data from illegitimate user. In case of
legitimate users, not all user requires to view all the data in various database. For example,
accounting data need not be viewed by the employees not in the accounting department. In the
same way in accounting department the employees need not given right to insert/update account
data, they may be given only option to view and extract data. Thus, to provide security certain
data or operation on them from certain legitimate user, access right privileges can be set for the
user. To implement access level privilege access, MySQL uses GRANT and REVOKE privilege.
The GRANT command is used to provide access to the tables, whereas the REVOKE command
is used to remove the privilege of any particular type of access to a particular table. Therefore, to
test how security to the database is provided with the “grant” command, an example is provided
below.
Testing
The “classicmodel” database contains the following tables listed in output:

CONFIGURE A MYSQL DATABASE 7
The access permission that is set on the classicmodels database for the user jump is tested in the
following way.
1) The user account is changed to jump to access the mydb database by executing command
mysql -u jump -p mydb
The program displays error message as the root user does not provide access to mydb
database
2) When user account is changed to jump to access the classicmodels database by executing
command
mysql -u jump -p classicmodels
The user is allowed rights to access the classicmodels database
The testing described above is tested and the following output is displayed
The table customers of “classicmodels” is accessed from “user jump” as shown below from the
“jump” user account:

CONFIGURE A MYSQL DATABASE 8
Conclusion
In conclusion, there is relative importance of database security, and there are different
ways of achieving them. Therefore, an application utilizing the “GRANT” and “REVOKE”
command improves the security of a database. Hence, the new user named jump was created
with password start in the “classicmodels” database. The user is then granted a specific list of
access to the “user jump” in the “classicmodels” database.
References
Basta, A. & Zgola, M. (2011). Database security. Boston, MA: Cengage Learning
Glendon, M.L. (2011). Fundamentals of database management systems. New York, NY: John
Wiley and Sons, Inc.
Tahaghoghi, S. & Tahaghoghi, S.M.M., (2014). Learning MySQL. Sabastopol, CA: O’Reilly
Publisher.