DATABASE SECURITY

LAB
LAB Assignments
1. Practical on installation of MySql database server.
2. Practical on routine DBA job.
3. Practical on creation of user in MySql database.
4. Practical on Assigning Database roles to user.
5. Practical on password guidelines.
6. Practical on certificate guidelines.
7. Practical on Database backup and restore procedure.
8. Practical on Database Auditing (MySql to be used).
9. Practical on ODBC connectivity to MySql database.
10. Practical on JDBC connectivity to MySql database.
Introduction to MySql:-
MySQL is an open source Relational Database Management System. MySQL is very fast
reliable and flexible Database Management System. It provides a very high performance and it is
multi threaded and multi user Relational Database management system.

MySQL Features

1. MySQL are very fast and much reliable for any type of application.
2. MySQL is very Lightweight application.
3. MySQL command line tool is very powerful and can be used to run SQL queries against
database.
4. MySQL supports indexing and binary objects.
5. It is allow changes to structure of table while server is running.
6. MySQL has a wide user base.
7. It is a very fast thread-based memory allocation system.
8. MySQL Written in C and C++ language.
9. MySQL code is tested with different compilers.
10. MySQL is available as a separate program for use in a client/server network environment.
11. The MySQL available for the most Unix operating platform.
12. MySQL are the available for window operating system window NT, window 95 ,and
window 98.
13. MySQL available for OS/2.
14. Programming libraries for C, Python, PHP, Java , Delphi etc. are available to connect to
MySQL database.

MySQL Advantages:

Reliability and Performance

MySQL is very reliable and high performance relational database management system. It can
used to store many GB's of data into database.

Availability of Source
MySQL source code is available that's why now you can recompile the source code.

Cross-Platform support
MySQL supports more then twenty different platform including the major Linux distribution
.Mac OS X, UNIX and Microsoft windows.

Large pool of Trained and Certified Developers

MySQL is very popular and it is world most popular open source Database. So it is easy to find
high quality staff around the world.

Powerful Uncomplicated software

The MySQL has most capabilities to handle most corporate database application and used to
very easy and fast

Yum:-
The Yellow dog Updater Modified (YUM) is a package management application for
computers running Linux operating systems. A package is usually a software
application, but the term can also encompass other items such as documentation,
source code, and extra levels for games. Package management is a term used to
describe the processes of adding, removing, and updating software on a computer.

Configuration of Yum server for the installation of Centos 6 rpm

1- Now open the terminal and type the vi /etc/yum.repos.d/CentOS-Base.repo then press
Enter key.

2- Now the following window would appear. Press I

3- Now uncomment the baseurl1 and comment out the mirrorlist now the screen looks
like as below. Then press :wq save and exit

4- Now type vi /etc/yum.conf then press enter key

5- Now the following screen would appear. Press I

6- Now add the following line at the end of file. Then press :wq save and exit

Proxy=http://192.168.4.56:8080 the proxy address may be different.

Note: The above configuration is required when your Linux machine is working
behind a proxy server.

7- Now type service network restart . Then press Enter key

„ Practical on installation of MySql database server.

Installation of MySql:-
1- Logon by using username root and password lab@123 then press Enter key and type yum
install yum and press Enter key.

2- Type Y and press Enter key

 3- Now start the MySQL at boot time for this type chkconfig --levels 2356 mysqld on
and press Enter key.

Create Database, Create MySQL User and Enable Remote Connections to MySQL
Database

4- DB_NAME = webdb
5- USER_NAME = webdb_user
6- REMOTE_IP = 10.0.0.13
7- PASSWORD = password123
8- PERMISSIONS = ALL

4- Type /etc/init.d/mysqld start and press Enter key.

5- The following screenshot shows that mysql service would running.

6- Now type mysql and press Enter key.

7- Now the one row would be created

9- Type create user ‘webdb_user’@’10.0.0.13’ IDENTIFIED BY ‘password123’; and press
Enter key.

10- Now the user webdb_user would created.

11- Now grant the permission on the user webdb_user for this type the following command
and press Enter key.

GRANT ALL ON webdb.* To ‘webdb_user’@’10.0.0.13;

12- Now test the remote database connectivity for this type the following command
mysql –h 10.0.0.13 –u webdb_user –p webdb then press Enter key.

13- Now login in the mysql server for this type mysql –u root –p –h localhost and press
Enterkey
 14- Now the root user connected to localuser

Enable Remote Connection to MySQL Server –> Open MySQL Port (3306) on Iptables
Firewall (as root user again)
„Practical on routine DBA job

The job of Data Base Administrator(DBA) are as under:-

DBA Routine Job:-

• Auditing Login Failures

• Monitoring Connectivity Issues
• Audit Long Running Queries
• Memory Issues
• MSDTC and Remote Connection Issues
• Monitoring IP and Memory statistics
• Monitoring Jobs & Alerts
• Monitoring Replication Alerts
• Monitoring Log Shipping & Mirroring
• Database Backups Audits
• Database Refresh
• Schema level refresh
• Providing ad-hoc export dump
• Blocking Issues and Resolution
• Deadlock detection and Prevention
• Updating Statistics
• Index Usage Monitoring
• Management History Checks

Emergency DBA Activities

• Auditing Login Failures

• Monitoring Connectivity Issues
• Audit Long Running Queries
• Memory Issues
• MSDTC and Remote Connection Issues
• Monitoring IP and Memory statistics
• Monitoring Jobs & Alerts
• Monitoring Replication Alerts
• Monitoring Log Shipping & Mirroring
• Database Backups Audits
• Database Refresh
• Schema level refresh
• Providing ad-hoc export dump
• Blocking Issues and Resolution
• Deadlock detection and Prevention
• Updating Statistics
• Index Usage Monitoring
• Management History Checks
 • Service startup Configurations
• Server down Issues
• Database down Issues
• Corrupted Databases / Objects
• Missing Start-up Parameters
• Blocking Issues
• Deadlock detections
• Replication Failures
• Peer-Peer Replication Issues
• DR Process Issues & Precautions
• Connection Issues in Cluster Nodes
• Missing Objects
• Missing RIDs / Offsets
• Invalid Page Versioning
• Views not in Sync
• Missing Indexes
• Procedure Execution Issues
• Job & Alert Failures

Maintenance Activities

• Trace Files & user dump files

• Database & Object Health Checks
• Password Resets
• Configuring Replication
• Configuring Log Shipping mechanism
• Configuring DB Mirroring mechanism
• Clustering Installation and Issues
• Load Balancing for Report Generation
• Database Maintenance Plans Design
• Resource Governor and management
• Policy Based Management (PBM)
• Management Data warehouses (MDM)
• Patching Activities and Issues
• Service Pack Issues and Rollbacks
• Server Migration and Issues
• Database Migration and Issues
• DR Process & Smoke Test Procedures
• Monitoring Performance Counters
Other(non SQL Server) Activities:-

• DB Management Documentation
• Creating KB Articles
• Auditing SLA & OLA
• DB Report Generation
• DB History Reports
• Network Share Management
• AD Syncup Issues
• Windows Cluster Management
• DCOM & COM+ Issues
• WMI Errors
• SAN Issues
• Storage & RAID Issues
• MOM Monitoring
• REDGATE Tools
• Design Tools
o UML
o TOAD
o ERWIN
„ Practical on password guidelines.

Guidelines for password MySQL :-

1- Don’t share root user password and mysql.user table acess with anyone till you have full trust
on it. Because that encrypted password is real password in MySQL so if anyone knows that than
he/she can easily login with any user if he has access to his host

2- Check with “mysql -uroot ” command, If you can easily login without asking password than
you are in trouble. Anyone can login with root user in that server. In this case, you can use
“mysql_secure_installation” utility. By running it, you can set root password, remove
anonymous users and also restrict users which are connecting from outside of the server.

3- There should not be any user without password. Even try to avoid “%” in hosts. Frequently
change root password.

4- Check users permissions with “SHOW GRANTS” command and remove unnecessary
permissions by “REVOKE” command if needed. Even don’t give permissions to users for
multiple hosts/dbs until needed.

5- Don’t use any words from dictionary in password. It can be break easily by some hack
program.

6- If data is that much sensitive than use SSL connections between MySQL client and server.

7-As MySQL is using 3306 by default, it should be blocked from outside of network. It should
not be accessible by un-trusted users/hosts.

8-Don’t run the mysqld daemon as the linux root user. It should be always run by MySQL user
itself. If you run mysqld with another linux user than root, you dont need to change the root user
name in mysql.user table because there is no any connection between MySQL users and Linux
users.

9-Don’t give the process privileges to all users. Because “show processlist” command can show
all the running queries on the servers. It might be possible that someone is chagning the
password and another can see it by “show processlist” i.e update user set password =
old_password(“abc”)

10-If you don’t trust your DNS, you should use IP numbers instead of hostnames in the
mysql.user table. In any case, you should be very careful about creating grant table entries using
hostname values that contain wild cards! If you want to restrict the number of connections for a
single user, you can do this by setting the max_user_connections variable in mysqld.

Database Backup:-
1- Type mysql –u root –p and press Enter key after that enter password password123

2- Now the mysql prompt would appear.

3- Now type show databases and press Enter key. This would check that already
database present or not
4- Now create a database people for this type create database people; and press
Enter key.

5- Now we want to grant ( GRANT ) all user rights ( ALL ) from ( ON ) the entire (
* ) people database to ( TO ) your account ( yourusername@localhost ) with
your user password being stuffedpoodle ( IDENTIFIED BY "stuffedpoodle" ).

6- Now type mysql –u ted –p and press Enter key then Enter password stuffedpoodle
and press Enter key.
How to create a table relating to the people database:-

1- Type the following command

CREATE TABLE peopleInfo

peopleID int primary key,

firstName varchar(30),

lastName varchar(30),

age int,

gender varchar(13)

);
 2- Now type the following commands

show tables; and press enter key

describes peopleInfo ; and press Enter key

3- Now insert into our table (specifying the rows) and inputting the values(actual data) that
we want. (NOTE: We are not filling in the primary key.)

4- Type select * from peopleInfo; and press Enter key.

5-Now type the following command and press Enter key

INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Mary", "Jones", 21,
"female");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Jill", "Harrington", 19,
"female");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Bob", "Mill", 26,
"male");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Alfred", "Jinks", 23,
"male");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Sandra", "Tussel", 31,
"female");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Mike", "Habraha", 45,
"male");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("John", "Murry", 22,
"male");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Jake", "Mechowsky", 34,
"male");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Hobrah", "Hinbrah", 24,
"hermaphrodite");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Laura", "Smith", 17,
"female");

6-Now the following screen would appear.

7-Type select * from peopleInfo; and press Enter key this would display the tables .
„ Practical on Database backup and restore procedure.

Backing up and restoring your MySQL database:-

There is a function called mysqldump. To backup your database (in this case backup the people
database):

We first have to create the .txt file that we will be backing it up to. Open a blank .txt file and
save it as backupfile.sql .

Now we can type:

mysqldump -u ted -p people > backupfile.sql

MySQL

According to EvansData, MySQL is the most popular database with over 53% market share
among other open source RDBMS. The following section talks about accessing MySQL with
WinSQL.

„ Download and installation

MySQL can be downloaded from http://www.mysql.com. This database comes in two flavors:
Community Server Edition and Enterprise Edition. Community server is available for free and
does not include support. On the other hand, Enterprise edition is geared toward mission critical
applications backed up with full support from MySQL. The discussion below assumes that you
are using the community server.

RDBS Download: http://dev.mysql.com/downloads/mysql/5.0.html#downloads

ODBC Driver: http://dev.mysql.com/downloads/connector/odbc/5.0.html

Several installation packages are available for MySQL. Refer to

http://dev.mysql.com/doc/refman/5.0/en/windows-choosing-package.html for a description of
how to choose an installation package for Windows platform.

At the time of this document, MySQL ODBC connector 5.0 was available as a beta release.
Initial tests with WinSQL using this driver did not show any problems. However, if you are
interested in a more stable but older release, visit
http://dev.mysql.com/downloads/connector/odbc/3.51.html

We recommend using the download package that comes with an installer. The document
assumes you are installing the server on Windows. For installation on other platforms refer to the
documentation that comes with MySQL. Run the installer on your machine, which will copy
necessary files on to your hard disk. Towards the end of installation process it will prompt you to
configure the MySQL server.

Select detailed Configuration option in the configuration window.

Follow the instructions on the screen until you come to the TCP/IP network port.

It is important to remember the value on this screen, which refers to the TCP/IP port. You will
have to specify this number when creating an ODBC DSN for MySQL. The default value for
this parameter is 3306 and can be changed to any number below 65000.

Another important value to remember is the password for the ?root? account. More discussion
about the root account will follow in the authentication section.

By default ?root? access is only available for the local machine. This is done for security
reasons. However, if you are planning to access this database from another machine using the
root access, check the ?Enable root access from remote machines?.

Once all information is collected, the Configuration wizard will run a set of scripts to save the
values specified by the user. One important point to remember is to turn Windows Firewall off
during the configuration process. This is because it may prevent MySQL service from listening
on the TCP/IP port In that case you might get the following error.
Once the database is correctly setup and installed, you can check its status from Windows
Services module in Control Panel.

„ Practical on ODBC connectivity to MySql database.

Installing the ODBC driver

The ODBC driver for MySQL is not bundled with the server. Therefore, you need to install that
separately. The link to download this driver is shown above. All screens in the installer are self-
explanatory and there are no setup options as all the installer does is to copy files and change
some registry settings.

Configuring the ODBC driver

Before connecting to the database, you need to create an ODBC DSN. The steps below show
how to create a DSN referring to a MySQL database running either on Windows, Linux or any
other supported operating system.

1. Start the ODBC manager. This can be done from within WinSQL by clicking File/Open
ODBC manager, or by double clicking the Data Sources (ODBC) link in Windows
Control Panel.
2. Ensure that the MySQL ODBC driver is installed on the machine by clicking the Drivers
tab.
3. Next click the either User DSN or System DSN tab to create a DSN
4. To add a new data source, click the Add button and select MySQL/Connector ODBC
from the list.
5. Following screen is displayed, which is used to specify connection settings.

The table below describes what each parameter mean.

 Data Source Name: A unique name identifying this data source
Description: An friendly description
Server: Host name or IP address where MySQL server is listening
User: User ID. If this is the first time, try ?root?
Password: This is the password for ?root?, which you specified when
installing the server.
Database: This is the name of the database. Leave this to ?mysql?
Port: Refers to TCP/IP port. Default value is 3306.

6. When done click Ok

Establishing Connection from WinSQL

After creating ODBC DSN, start WinSQL. In the connection window, you will see your newly
created DSN for MySQL.

IMPORTANT: Ensure that you select MySQL as the Database type, which enables MySQL
specific features in WinSQL.

Important Concepts

Default Values

Following table lists values for some important parameters in MySQL database.
TCP/IP Port: 3306

Login ID: root

Password: <blank>

System database name: mysql

Security

As mentioned earlier, MySQL creates a user called ?root?. By default, the password is blank and
client access is restricted to local machine. This default setting is modified by the Configuration
Wizard that is run automatically after installation. It can also be modified later on by submitting
SQL queries.

Every user in MySQL can be restricted not only by a pair of user id and password strings, but
also by the host machine's IP address. Therefore, when creating a new user you have to specify
the host name where this user is authorized to connect from. Wild card characters such as ?%? or
?_? can be used to specify a host as well as subnet mask. For more information about security,
refer to section 5.8 in MySQL 5.0 reference manual.

New users can be created using two methods:

1. Submitting a CREATE USER query

2. Submitting a GRANT statement

Example 1:

CREATE USER 'scott'@'%' IDENTIFIED BY 'tiger'

CREATE USER 'scott'@'localhost' IDENTIFIED BY 'tiger'

Example 2:

GRANT ALL PRIVILEGES ON *.* TO 'scott@%' IDENTIFIED BY 'tiger' WITH

GRANT OPTION

GRANT ALL PRIVIDEGES ON *.* TO 'scott@localhost' IDENTIFIED BY 'tiger'

WITH GRANT OPTION

Both examples above create a user name 'scott', sets his password to 'tiger' and grants connection
access from any machine. In addition to granting connection access, Example 2 also grants other
privileges such as creating tables and modifying data. Refer to MySQL manual for more details.
Creating new database

Every instance of MySQL may have one or more database. When MySQL is first installed, it
creates two databases during installation:

1. mysql - stores all server specific values and global information

2. test - empty database for testing purposes

It is recommended that you create another database for your needs rather than creating tables in
the existing database.

Use the following SQL statement to create a new database.

CREATE DATABASE NewDbName

The name of the database is specified in the ODBC DSN and therefore, you always connect to
that database. In the above example we specified ?mysql? as the database name. WinSQL
displays the name of the current database in the catalog window.

You can switch to a different database either by selecting the desired value from this pick list or
issuing a ?USE? statement. For example:

USE NewDbName
„ Practical on JDBC connectivity to MySQL database.

1. Download MySQL JDBC Driver

Data types of MySQL and Java programming language are not same, its need some
mechanism for transferring data between an database using MySQL data types and a
application using Java data types. To connect to MySQL from Java you have to use the
JDBC driver from MySQL. The MySQL JDBC driver is called "MySQL Connector/J".

2. Java JDBC connection example

Code snippets to use JDBC to connect a MySQL database.

Class.forName("com.mysql.jdbc.Driver");
Connection connection = null;
connection = DriverManager.getConnection(
"jdbc:mysql://hostname:port/dbname","username", "password");
connection.close();

See a complete example below :

File : JDBCExample.java

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

public class JDBCExample {

public static void main(String[] argv) {

 System.out.println("-------- MySQL JDBC Connection Testing ------------");

try {

Class.forName("com.mysql.jdbc.Driver");

} catch (ClassNotFoundException e) {

System.out.println("Where is your MySQL JDBC Driver?");

e.printStackTrace();
return;

System.out.println("MySQL JDBC Driver Registered!");

Connection connection = null;

try {
connection = DriverManager

.getConnection("jdbc:mysql://localhost:3306/mkyongcom",
"root", "password");

} catch (SQLException e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;
}

if (connection != null) {
System.out.println("You made it, take control your database now!");
} else {
System.out.println("Failed to make connection!");
}
}
}

3. Run it

Assume JDBCExample.java is store in c:\test folder, together with MySQL JDBC driver

C:\test>java -cp c:\test\mysql-connector-java-5.1.8-bin.jar;c:\test JDBCExample

-------- MySQL JDBC Connection Testing ------------
MySQL JDBC Driver Registered!
You made it, take control your database now!
C:\test>

„ Practical on Database Auditing

Database auditing involves observing a database so as to be aware of the actions of database

users. Database administrators and consultants often set up auditing for security purposes, for
example, to ensure that those without the permission to access information do not access it.

Database Directory Permissions:-

1-Type the grep ‘user=mysql’ /etc/my.cnf and press Enter key .It would display Database
directory only accessible to the user running the MySQL

2- Now verify the server is actually running or not by typing the MySQL user by viewing the
current process list:

Type ps aux | grep -i mysql and press Enter key.

3- To verify that the current grant table is loaded and permissions are correct, try to log into
MySQL with each username and no password.

5- Now restrict the ports that can be accessed to the bare minimum, SSH and MySQL for this
type nmap –sS –p 1- 65535 10.0.0.13
6- To test record all connections and queries on the MySQL server to create an audit trail. Check
the startup options to see if --log was enabled:
ps auxwww | grep –i mysql