Securing database with command line linux: Difference between revisions

From ICO wiki
Jump to navigationJump to search
mNo edit summary
 
(411 intermediate revisions by one other user not shown)
Line 1: Line 1:
= Aim of this page =
Author: Mohanad Aly
*To know information about Linux operation system.
*To secure the database using the command line method.
*What is sudo (superuser) ?
*What is the root user ?
*Control the command line environment.
*Access the information in database by easy way.


Group : Cyber Security Engineering (C11)


=Information about Linux operation system and Database=
Page Created: 18 February 2016
*'''What is Linux?'''
Linux is, in simplest terms, an operating system. It is the software on a computer that enables applications and the computer operator to access the devices on the computer to perform desired functions. The operating system (OS) relays instructions from an application to, for instance, the computer's processor. The processor performs the instructed task, then sends the results back to the application via the operating system.
Explained in these terms, Linux is very similar to other operating systems, such as Windows and OS X.
But something sets Linux apart from these operating systems. The Linux operating system represented a $25 billion ecosystem in 2008. Since its inception in 1991, Linux has grown to become a force in computing, powering everything from the New York Stock Exchange to mobile phones to supercomputers to consumer devices.


As an open operating system, Linux is developed collaboratively, meaning no one company is solely responsible for its development or ongoing support. Companies participating in the Linux economy share research and development costs with their partners and competitors. This spreading of development
‎Last modified: 02 June 2016‎
[[File:Pagename explanation linux_os.jpg|thumb|Linux operation system]]
burden amongst individuals and companies has resulted in a large and efficient ecosystem and unheralded software innovation.
Over 1,000 developers, from at least 100 different companies, contribute to every kernel release. In the past two years alone, over 3,200 developers from 200 companies have contributed to the kernel--which is just one small piece of a Linux distribution.


*'''Components of Linux System'''
= Aim of this page =
*'''Control the Command Line environment.'''
*'''What database mean ?'''
*'''Database Threats.''' 
*'''Securing Database using the command line method.'''


Linux Operating System has primarily three components
== General Information about command line ==
*Before starting there is a short course of command line in code academy website [https://www.codecademy.com/ Code Academy ]
*After finishing the command line exercises, user need to remember a few useful commands.
*First <code>cd</code> the command that make the user navigate everywhere in the command line environment.
* Second <code>pwd</code> the command that always make user know his location in the system.
* User need this command <code>whoami</code> to know he is the root user or not.
* <code>sudo  -s</code> switch ordinary user to the root user that control everything in the system.
* <code> apt-get update </code> Always update repositories and useful before install anything.
* <code> apt-get install </code> After user become super user, user will need this command to install packages.
* <code> apt-get Upgrade </code> For upgrading the system.
* <code> nano </code> Nano always good for beginners, easy to edit or write file in command line. 
* <code> ls </code> Which user already know from the code academy, it's for listing all the files and folder in the directory.
{|style="margin: 0 auto;"
| [[File:Pagename_explanation_commands.jpg|thumb|upright|300px|Examples of the basic commands]]


| [[File:Screenshot_from_2016-06-02_14-23-23.jpg|upright|400px|nano text editor example]]
|}


1) Kernel - Kernel is the core part of Linux. It is responsible for all major activities of this operating system. It is consists of various modules and it interacts directly with the underlying hardware. Kernel provides the required abstraction to hide low level hardware details to system or application programs.
=Database=


2) System Library - System libraries are special functions or programs using which application programs or system utilities accesses Kernel's features. These libraries implements most of the functionalities of the operating system and do not requires kernel module's code access rights.
<span style="color:#20B336">
 
'''What database mean ?'''
3) System Utility - System Utility programs are responsible to do specialized, individual level tasks.
[[File:Pagename_Root_database.jpg|thumb|center|Database [https://www.linkedin.com/pulse/what-importance-database-management-system-scott-aveda]]]
 
A database is an organized collection of data. It is the collection of schemas, tables, queries, reports, views and other objects. The data are typically organized to model aspects of reality in a way that supports processes requiring information, such as modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies.
*'''Kernel Mode VS User Mode'''
 
Kernel component code executes in a special privileged mode called kernel mode with full access to all resources of the computer. This code represents a single process, executes in single address space and do not require any context switch and hence is very efficient and fast. Kernel runs each processes and provides system services to processes, provides protected access to hardwares to processes.
 
Support code which is not required to run in kernel mode is in System Library. User programs and other system programs works in User Mode which has no access to system hardwares and kernel code. User programs/ utilities use System libraries to access Kernel functions to get system's low level tasks.
 
'''Basic Features'''
[[File:Pagename explanation linux_architecture.jpg|thumb|Explanation Linux architecture]]
'''Following are some of the important features of Linux Operating System.'''
 
*Portable - Portability means softwares can works on different types of hardwares in same way.Linux kernel and application programs supports their installation on any kind of hardware platform.
 
*Open Source - Linux source code is freely available and it is community based development project. Multiple teams works in collaboration to enhance the capability of Linux operating system and it is continuously evolving.
 
*Multi-User - Linux is a multiuser system means multiple users can access system resources like memory/ ram/ application programs at same time.
 
*Multiprogramming - Linux is a multiprogramming system means multiple applications can run at same time.
 
*Hierarchical File System - Linux provides a standard file structure in which system files/ user files are arranged.
 
*Shell - Linux provides a special interpreter program which can be used to execute commands of the operating system. It can be used to do various types of operations, call application programs etc.
 
*Security - Linux provides user security using authentication features like password protection/ controlled access to specific files/ encryption of data.


'''Linux System Architecture is consists of following layers'''
==Database Threats==
<span style="color:#FF0000">
'''An attacker can target and compromise a database server in a number of ways by exploiting a variety of configuration and application level vulnerabilities.''' [https://msdn.microsoft.com/en-us/library/ff648664.aspx#c18618429_004]


*Hardware layer - Hardware consists of all peripheral devices (RAM/ HDD/ CPU etc).
[[File:IC53151.gif|thumb|300px|Major threats and vulnerabilities that can result in a compromised database server and the potential destruction or theft of sensitive data [https://msdn.microsoft.com/en-us/library/ff648664.aspx]]]


*Kernel - Core component of Operating System, interacts directly with hardware, provides low level services to upper layer components.
'''The main threats to a database server are:'''
*SQL injection
*Network eavesdropping
*Unauthorized server access
*Password cracking
<span style="color:#FF0000">
'''SQL Injection'''
*With a SQL injection attack, the attacker exploits vulnerabilities in the application's input validation and data access code to run arbitrary commands in the database using the security context of the Web application.


*Shell - An interface to kernel, hiding complexity of kernel's functions from users. Takes commands from user and executes kernel's functions.
'''Vulnerabilities'''
*Vulnerabilities exploited by SQL injection include:
*Poor input validation in the Web applications
*Unsafe, dynamically constructed SQL commands
*Over-privileged application logins to the database
*Weak permissions that fail to restrict the application's login to the database


*Utilities - Utility programs giving user most of the functionalities of an operating systems.
'''Countermeasures'''
*To counter SQL injection attacks:
*The application should constrain and sanitize input data before using it in SQL queries.
*Use type safe SQL parameters for data access. These can be used with stored procedures or dynamically constructed SQL command strings. *Using SQL parameters ensures that input data is subject to type and length checks and also that injected code is treated as literal *data, not as executable statements in the database.
*Use a SQL Server login that has restricted permissions in the database. Ideally, user should grant execute permissions only to selected *stored procedures in the database and provide no direct table access.


'''What database mean ?'''
<span style="color:#FF0000">
'''Network Eavesdropping'''
*The deployment architecture of most applications includes a physical separation of the data access code from the database server. As a result, sensitive data, such as application-specific data or database login credentials, must be protected from network eavesdroppers.


A database is an organized collection of data. It is the collection of schemas, tables, queries, reports, views and other objects. The data are typically organized to model aspects of reality in a way that supports processes requiring information, such as modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies.
'''Vulnerabilities'''
Vulnerabilities that increase the likelihood of network eavesdropping include:
*Insecure communication channels


'''What sudo mean in Linux ?'''
Passing credentials in clear text to the database for example
*Using SQL authentication instead of Windows authentication
*Using SQL authentication without a server certificate


Sudo (superuser do) allows a system administrator to give certain users (or groups of users) the ability to run some (or all) commands as root while logging all commands and arguments. Sudo operates on a per-command basis. It is not a replacement for the shell. Features include: the ability to restrict what commands a user may run on aper-host basis, copious logging of each command (providing a clear audit trail of who did what), a configurable timeout of the sudo command, and the ability to use the same configuration file (sudoers) on many different machines.
'''Countermeasures'''
To counter network eavesdropping:
*Use Windows authentication to connect to the database server to avoid sending credentials over the network.
*Install a server certificate on the database server. This results in the automatic encryption of SQL credentials over the network.
*Use an SSL connection between the Web server and database server to protect sensitive application data. This requires a database server certificate.
*Use an IPSec encrypted channel between Web and database server.
<span style="color:#FF0000">
'''Unauthorized Server Access'''


'''What root mean ?'''
Direct access to user database server should be restricted to specific client computers to prevent unauthorized server access.
[[File:Pagename Root user.jpeg|thumb|right|Explanation root user]]
*root is the user name or account that by default has access to all commands and files on a Linux or other Unix-like operating system. It is also referred to as the root account, root user and the superuser.


*The word root also has several additional, related meanings when used as part of other terms, and thus it can be a source of confusion to people new to Unix-like systems.
'''Vulnerabilities'''
Vulnerabilities that make the database server susceptible to unauthorized server access include:
*Failure to block the SQL Server port at the perimeter firewall
*Lack of IPSec or TCP/IP filtering policies


*One of these is the root directory, which is the top level directory on a system. That is, it is the directory in which all other directories, including their subdirectories, and files reside. The root directory is designated by a forward slash ( / ).
'''Attacks'''
Direct connection attacks exist for both authenticated users and those without a user name and password; for example
*Tools such as Query Analyzer (Isqlw.exe) or the command line equivalent (Osql.exe) are used to establish a direct connection to SQL Server and issue commands.
*Server information, such as software version, is revealed to an attacker who sends carefully constructed packets to listening ports.


*Another is /root (pronounced slash root), which is the root user's home directory. A home directory is the primary repository of a user's files, including that user's configuration files, and it is usually the directory in which a user finds itself when it logs into a system. /root is a subdirectory of the root directory, as indicated by the forward slash that begins its name, and should not to be confused with that directory. Home directories for users other than root are by default created in the /home directory, which is another standard subdirectory of the root directory.
'''Countermeasures'''
To counter these attacks:
*Make sure that SQL Server ports are not visible from outside of the perimeter network.
*Within the perimeter, restrict direct access by unauthorized hosts, for example, by using IPSec or TCP/IP filters.
<span style="color:#FF0000">
'''Password Cracking'''


*Root privileges are the powers that the root account has on the system. The root account is the most privileged on the system and has absolute power over it (i.e., complete access to all files and commands). Among root's powers are the ability to modify the system in any way desired and to grant and revoke access permissions (i.e., the ability to read, modify and execute specific files and directories) for other users, including any of those that are by default reserved for root.
*A common first line of attack is to try to crack the passwords of well known account names, such as sa (the SQL Server administrator account).


= General Information about command line =
'''Vulnerabilities'''
[[File:Pagename explanation Screenshot-Terminal.png|thumb|right|Example about the command line]]
Common vulnerabilities that lead to password cracking are:
[[File:Pagename_explanation_commands.jpg|thumb|right|Examples of the basic commands]]
*Weak or blank passwords
*Before starting you need to take a short course of command line in code academy [https://www.codecademy.com/ Code Academy ]
*Passwords that contain everyday words
*After you finish the command line exercises for the command line you need to know a few commands that you have to remember always.
*First <code>cd</code> the command that will make you navigate everywhere in the command line environment. The simple commands that will make you feel free in Linux environment if you are using Windows or Mac


* second <code>pwd</code>  command that always you will know your location in the system
'''Attacks'''
* Don't forget also <code>whoami</code> to know you are the root user.
Common password cracking attacks include:
*Dictionary attacks
*Manual password guessing


* <code>sudo  -s</code>  switch you to root user that control everything in the system.
'''Countermeasures'''
* <code> ls </code> which you already now from the code academy to list all the files and folder in the directory.
To counter these attacks:
*Create passwords for SQL Server login accounts that meet complexity requirements.
*Avoid passwords that contain common words found in the dictionary.


=Starting to install MySQL database=
=Starting to install MySQL database=
*There are many implementations of the SQL database language available on Linux and Unix-like systems. MySQL and MariaDB are two popular options for deploying relational databases in server environments.
*There are many implementations of the SQL database language available on Linux and Unix-like systems. MySQL and MariaDB are two popular options for deploying relational databases in server environments.


*However, like most software, these tools can be security liabilities if they are configured incorrectly. This tutorial will guide you through some basic steps you can take to secure your MariaDB or MySQL databases, and ensure that they are not an open door into your VPS.
*However, like most software, these tools can be security liabilities if they are configured incorrectly. This tutorial will guide user through some basic steps, user can take to secure MariaDB or MySQL databases, and ensure that they are not an open door into VPS.


*For the sake of simplicity and illustration, we will use the MySQL server on an Ubuntu 12.04 VPS instance. However, these techniques can be applied to other Linux distributions and can be used with MariaDB as well.
*For the sake of simplicity and illustration, using the MySQL server on an Ubuntu 12.04 VPS instance. However, these techniques can be applied to other Linux distributions and can be used with MariaDB as well. [https://www.digitalocean.com/community/tutorials/how-to-secure-mysql-and-mariadb-databases-in-a-linux-vps]


= Initial Setup=
= Initial Setup=


*'''MySQL gives you an opportunity to take the first step towards security during installation. It will request that you set a root password.'''
*'''MySQL gives user an opportunity to take the first step towards security during installation. It will request that user set a root password.'''
[[File:MySQL.jpg|thumb |left|Configuring mysql-server]]
[[File:MySQL.jpg|thumb |right|Configuring mysql-server [https://www.digitalocean.com/community/tutorials/how-to-secure-mysql-and-mariadb-databases-in-a-linux-vps]]]
*<code> sudo apt-get install mysql-server </code>
*<code> sudo apt-get install mysql-server </code>


You can always set the root password at a later time, but there is no reason to skip this step, so you should secure your administrator account from the very beginning.
User can always set the root password at a later time, but there is no reason to skip this step, so user should secure his administrator account from the very beginning.


Once the installation is complete, we should run a few included scripts. First, we will use the "mysql_install_db" script to create a directory layout for our databases.
Once the installation is complete, user should run a few included scripts. First, user will use the "mysql_install_db" script to create a directory layout for our databases.


*<code> sudo mysql_install_db </code>
*<code> sudo mysql_install_db </code>




'''Next, run the script called "mysql_secure_installation". This will guide us through some procedures that will remove some defaults that are dangerous to use in a production environment.'''
'''Next, run the script called "mysql_secure_installation". This will guide through some procedures that will remove some defaults that are dangerous to use in a production environment.'''


*<code> sudo mysql_secure_installation </code>
*<code> sudo mysql_secure_installation </code>
it will first prompt you for the root password you set up during installation. Immediately following, you will be asked a series of questions, beginning with if you'd like to change the root password.
it will first prompt user for the root password user set up during installation. Immediately following, user will be asked a series of questions, beginning with if you'd like to change the root password.


This is another opportunity to change your password to something secure if you have not done so already.
This is another opportunity to change user password to something secure if user have not done so already.


You should answer "Y" (for yes) to all of the remaining questions.
User should answer "Y" (for yes) to all of the remaining questions.


This will remove the ability for anyone to log into MySQL by default, disable logging in remotely with the administrator account, remove some test databases that are insecure, and update the running MySQL instance to reflect these changes.
This will remove the ability for anyone to log into MySQL by default, disable logging in remotely with the administrator account, remove some test databases that are insecure, and update the running MySQL instance to reflect these changes.
Line 131: Line 157:
'''Security Considerations'''
'''Security Considerations'''


The overarching theme of securing MySQL (and almost any other system) is that access should be granted only when absolutely necessary. Your data safety sometimes comes down to a balance between convenience and security.
The overarching theme of securing MySQL (and almost any other system) is that access should be granted only when absolutely necessary. User data safety sometimes comes down to a balance between convenience and security.


In this guide, we will lean on the side of security, although your specific usage of the database software may lead you to pick and choose from these options.
In this Wiki, user will learn on the side of security, although user specific usage of the database software may lead user to pick and choose from these options.


'''Security Through the My.cnf File'''
'''Security Through the My.cnf File'''
Line 139: Line 165:
The main configuration file for MySQL is a file called "my.cnf" that is located in the "/etc/mysql/" directory on Ubuntu and the "/etc/" directory on some other VPS.
The main configuration file for MySQL is a file called "my.cnf" that is located in the "/etc/mysql/" directory on Ubuntu and the "/etc/" directory on some other VPS.


We will change some settings in this file to lock down our MySQL instance.
First changing some settings in this file to lock down user MySQL instance.


Open the file with root privileges. Change the directory path as needed if you are following this tutorial on a different system:
Open the file with root privileges. Change the directory path as needed if user are following this tutorial on a different system


<code>sudo nano /etc/mysql/my.cnf</code>
<code>sudo nano /etc/mysql/my.cnf</code>


'''The first setting that we should check is the "bind-address" setting within the "[mysqld]" section. This setting should be set to your local loopback network device, which is "127.0.0.1".'''
'''The first setting that should check is the "bind-address" setting within the "[mysqld]" section. This setting should be set to user local loopback network device, which is "127.0.0.1".'''
<code>bind-address = 127.0.0.1</code>
<code>bind-address = 127.0.0.1</code>


*This makes sure that MySQL is not accepting connections from anywhere except for the local machine.
*This makes sure that MySQL is not accepting connections from anywhere except for the local machine.


*If you need to access this database from another machine, consider connecting through SSH to do your database querying and administration locally and sending the results through the ssh tunnel.
*If user need to access this database from another machine, consider connecting through SSH to do user database querying and administration locally and sending the results through the ssh tunnel.


*The next hole we will patch is a function that allows access to the underlying filesystem from within MySQL. This can have severe security implications and should be shut off unless you absolutely need it.
*The next hole will patch is a function that allows access to the underlying filesystem from within MySQL. This can have severe security implications and should be shut off unless user absolutely need it.


'''In the same section of the file, we will add a directive to disable this ability to load local files:'''
'''In the same section of the file, user will add a directive to disable this ability to load local files:'''
<code>local-infile=0</code>
<code>local-infile=0</code>
This will disable loading files from the filesystem for users without file level privileges to the database.
This will disable loading files from the file system for users without file level privileges to the database.
[[File:Error.jpg ‎|thumb |right|MySQL log, error log]]
[[File:Error.jpg ‎|thumb |right|MySQL log, error log [https://www.digitalocean.com/community/tutorials/how-to-secure-mysql-and-mariadb-databases-in-a-linux-vps]]]
If we have enough space and are not operating a huge database, it can be helpful to log additional information to keep an eye on suspicious activity.
If the owner have enough space and are not operating a huge database, it can be helpful to log additional information to keep an eye on suspicious activity.


Logging too much can create a performance hit, so this is something you need to weigh carefully.
Logging too much can create a performance hit, so this is something user need to weigh carefully.


You can set the log variable within the same "[mysqld]" section that we've been adding to.
User can set the log variable within the same "[mysqld]" section that we've been adding to.


<code>log=/var/log/mysql-logfile</code>
<code>log=/var/log/mysql-logfile</code>


'''Make sure that the MySQL log, error log, and mysql log directory are not world readable'''
'''User have to make sure that the MySQL log, error log, and mysql log directory are not world readable'''
 
<code>sudo ls -l /var/log/mysql*</code>
<code>sudo ls -l /var/log/mysql*</code>


= Securing MySQL From Within=
= Securing MySQL From Within=


*There are a number of steps you can take while using MySQL to improve security.
*There are a number of steps user can take while using MySQL to improve security.


*We will be inputting the commands in this section into the MySQL prompt interface, so we need to log in.
*User will be inputting the commands in this section into the MySQL prompt interface, so user need to log in.


<code>mysql -u root -p</code>
<code>mysql -u root -p</code>


You will be asked for the root password that you set up earlier.
User will be asked for the root password that set up earlier.


'''Securing Passwords and Host Associations'''
'''Securing Passwords and Host Associations'''
Line 183: Line 210:
<code>SELECT User,Host,Password FROM mysql.user;</code>
<code>SELECT User,Host,Password FROM mysql.user;</code>


[[File:Database.jpg ‎|thumb |right|Securing Passwords and Host Associations example]]
[[File:Database.jpg ‎|thumb |right|Securing Passwords and Host Associations example [https://www.digitalocean.com/community/tutorials/how-to-secure-mysql-and-mariadb-databases-in-a-linux-vps]]]


As you can see, in our example set up, the user "demo-user" has no password and is valid regardless of what host he is on. This is very insecure.
As user can see, in this example set up, the user "demo-user" has no password and is valid regardless of what host he is on. This is very insecure.


We can set a password for the user with this command. Change "newPassWord" to reflect the password you wish to assign.
User can set a password for the user with this command. Change "newPassWord" to reflect the password that user wish to assign.


<code>UPDATE mysql.user SET Password=PASSWORD('newPassWord') WHERE User="demo-user";</code>
<code>UPDATE mysql.user SET Password=PASSWORD('newPassWord') WHERE User="demo-user";</code>


If we check the User table again, we will see that the demo user now has a password.
Checking the User table again, user will see that the demo user now has a password.


<code>SELECT User,Host,Password FROM mysql.user;</code>
<code>SELECT User,Host,Password FROM mysql.user;</code>


[[File:Password.jpg ‎|thumb |right|Securing Passwords and Host Associations example]]
[[File:Password2.jpg ‎|thumb |right|Securing Passwords and Host Associations example [https://www.digitalocean.com/community/tutorials/how-to-secure-mysql-and-mariadb-databases-in-a-linux-vps]]]




If you look in the "Host" field, you will see that we still have a "%", which is a wildcard that means any host. This is not what we want. Let's change that to be "local-host".
If user look in the "Host" field, user will see that still have a "%", which is a wildcard that means any host. This is not what want to be. Let's change that to be "local-host".


<code>UPDATE mysql.user SET Host='localhost' WHERE User="demo-user";</code>
<code>UPDATE mysql.user SET Host='localhost' WHERE User="demo-user";</code>


If we check again, we can see that the User table now has the appropriate fields set.
Checking again, user table now has the appropriate fields set.


<code>SELECT User,Host,Password FROM mysql.user;</code>
<code>SELECT User,Host,Password FROM mysql.user;</code>


If our table contains any blank users (it should not at this point since we ran "mysql_secure_installation", but we will cover this anyways), we should remove them.
If the table contains any blank users (it should not at this point since ran "mysql_secure_installation", but it will cover this anyways), user should remove them.


To do this, we can use the following call to delete blank users from the access table.
To do this, the root user can use the following call to delete blank users from the access table.


<code>DELETE FROM mysql.user WHERE User="";</code>
<code>DELETE FROM mysql.user WHERE User="";</code>


After we are done modifying the User table, we need to input the following command to implement the new permissions.
After the root user are done modifying the User table, user need to input the following command to implement the new permissions.


<code>FLUSH PRIVILEGES;</code>
<code>FLUSH PRIVILEGES;</code>


=Implementing Application-Specific Users=
=Implementing Application-Specific Users=
Line 224: Line 249:
Each application that uses MySQL should have its own user that only has limited privileges and only has access to the databases it needs to run.
Each application that uses MySQL should have its own user that only has limited privileges and only has access to the databases it needs to run.


When we configure a new application to use MySQL, we should create the databases needed by that application.
When the root user configure a new application to use MySQL, user should create the databases needed by that application.


<code>create database testDB;</code>
<code>create database testDB;</code>
Line 230: Line 255:
<code>Query OK, 1 row affected (0.00 sec)</code>
<code>Query OK, 1 row affected (0.00 sec)</code>


Next, we should create a user to manage that database, and assign it only the privileges it needs. This will vary by application, and some uses need more open privileges than others.
Next, the database owner should create a user to manage that database, and assign it only the privileges it needs. This will vary by application, and some uses need more open privileges than others.


To create a new user, use the following command.
To create a new user, use the following command.
Line 236: Line 261:
<code>CREATE USER 'demo-user'@'localhost' IDENTIFIED BY 'password';</code>
<code>CREATE USER 'demo-user'@'localhost' IDENTIFIED BY 'password';</code>


'''We can grant the new user privileges on the new table with the following command. See the tutorial on how to create a new user and grant permissions in MySQL to learn more about specific privileges.'''
'''User can grant the new user privileges on the new table with the following command. See the tutorial on how to create a new user and grant permissions in MySQL to learn more about specific privileges.''' [https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql]


<code>GRANT SELECT,UPDATE,DELETE ON testDB.* TO 'demo-user'@'localhost';</code>
<code>GRANT SELECT,UPDATE,DELETE ON testDB.* TO 'demo-user'@'localhost';</code>




As an example, if we later need to revoke update privileges from the account, we could use the following command.
As an example, if the user later need to revoke update privileges from the account, user could use the following command.


<code>REVOKE UPDATE ON testDB.* FROM 'demo-user'@'localhost';</code>
<code>REVOKE UPDATE ON testDB.* FROM 'demo-user'@'localhost';</code>


If we need all privileges on a certain database, we can specify that with the following.
If user need all privileges on a certain database he can specify that with the following.


<code>GRANT ALL ON testDB.* TO 'demo-user'@'localhost';</code>
<code>GRANT ALL ON testDB.* TO 'demo-user'@'localhost';</code>
[[File:Localhost.jpg  ‎|thumb |right|Securing Passwords and Host Associations example]]
[[File:Localhost.jpg  ‎|thumb |right|Securing Passwords and Host Associations example [https://www.digitalocean.com/community/tutorials/how-to-secure-mysql-and-mariadb-databases-in-a-linux-vps]]]
To show the current privileges of a user, we first must implement the privileges we specified using the "flush privileges" command. Then, we can query what grants a user has:
To show the current privileges of a user, user first must implement the privileges that specified using the "flush privileges" command. Then, and query what grants a user has:


<code>FLUSH PRIVILEGES;</code>
<code>FLUSH PRIVILEGES;</code>
<code>show grants for 'demo-user'@'localhost';</code>
<code>show grants for 'demo-user'@'localhost';</code>


'''Always flush privileges when you are finished making changes.'''
<span style="color:#FF0000">
 
'''Always flush privileges when the root user are finished making changes.'''
 
 


=Changing the Root User=
=Changing the Root User=


One additional step that you may want to take is to change the root login name. If an attacker is trying to access the root MySQL login, they will need to perform the additional step of finding the user-name.
One additional step that the root user may want to take is to change the root login name. If an attacker is trying to access the root MySQL login, they will need to perform the additional step of finding the user-name.


The root login can be changed with the following command.
The root login can be changed with the following command.
<code>rename user 'root'@'localhost' to 'newAdminUser'@'localhost';</code>
<code>rename user 'root'@'localhost' to 'newAdminUser'@'localhost';</code>


We can see the change by using the same query we've been using for the User database.
User can see the change by using the same query that been used for the User database.


<code>select user,host,password from mysql.user;</code>
<code>select user,host,password from mysql.user;</code>


Again, we must flush privileges for these changes to happen.
<span style="color:#FF0000">
Again!!, user must flush privileges for these changes to happen.


<code>FLUSH PRIVILEGES;</code>
<code>FLUSH PRIVILEGES;</code>


Remember that you will have to log into MySQL as the newly created username from now on when you wish to perform administrative tasks.
User have to remember that to log into MySQL as the newly created username from now on when user wish to perform administrative tasks.


<code>mysql -u newAdminUser -p</code>
<code>mysql -u newAdminUser -p</code>
=Summary=
Database is always at risk, cause one small mistake and the hacker can access to the database from different ways with different methods, user should now have a general understanding about databases and how to create a secure database. present article was for beginners that try to gain an understanding of databases security. Databases are not like most other files and require a little bit of thought in order for user to understand the concept of securing the database.


=See also=
=See also=


[http://www.liquidweb.com/kb/create-a-mysql-database-on-linux-via-command-line/] Securing database


1.[https://www.youtube.com/watch?v=FR4QIeZaPeM Database general understanding]
2.[https://en.wikipedia.org/wiki/Database_security Database security]
3.[https://msdn.microsoft.com/en-us/library/aa302430.aspx Building Secure Data Access]
4.[http://www.liquidweb.com/kb/create-a-mysql-database-on-linux-via-command-line Securing database]


=References=
=References=


[https://www.linux.com/learn/what-linux-overview-linux-operating-system] Linux information


[http://www.tutorialspoint.com/operating_system/os_linux.htm]Operating System - Linux
1.[https://msdn.microsoft.com/en-us/library/ff648664.aspx Database Threats and Countermeasures ]
 
2.[https://www.digitalocean.com/community/tutorials/how-to-secure-mysql-and-mariadb-databases-in-a-linux-vps Securing the database ]


[http://linux.about.com/cs/linux101/g/sudo.htm] Sudo definition
3.[https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql Grant permissions in mysql ]


[http://www.linfo.org/root.html] Root meaning in Linux operation system
------


[https://www.digitalocean.com/community/tutorials/how-to-secure-mysql-and-mariadb-databases-in-a-linux-vps] Securing the database
[[Category:Operatsioonisüsteemide administreerimine ja sidumine]]

Latest revision as of 23:42, 29 January 2017

Author: Mohanad Aly

Group : Cyber Security Engineering (C11)

Page Created: 18 February 2016

‎Last modified: 02 June 2016‎

Aim of this page

  • Control the Command Line environment.
  • What database mean ?
  • Database Threats.
  • Securing Database using the command line method.

General Information about command line

  • Before starting there is a short course of command line in code academy website Code Academy
  • After finishing the command line exercises, user need to remember a few useful commands.
  • First cd the command that make the user navigate everywhere in the command line environment.
  • Second pwd the command that always make user know his location in the system.
  • User need this command whoami to know he is the root user or not.
  • sudo -s switch ordinary user to the root user that control everything in the system.
  • apt-get update Always update repositories and useful before install anything.
  • apt-get install After user become super user, user will need this command to install packages.
  • apt-get Upgrade For upgrading the system.
  • nano Nano always good for beginners, easy to edit or write file in command line.
  • ls Which user already know from the code academy, it's for listing all the files and folder in the directory.
Examples of the basic commands
nano text editor example

Database

What database mean ?

Database [1]

A database is an organized collection of data. It is the collection of schemas, tables, queries, reports, views and other objects. The data are typically organized to model aspects of reality in a way that supports processes requiring information, such as modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies.

Database Threats

An attacker can target and compromise a database server in a number of ways by exploiting a variety of configuration and application level vulnerabilities. [8]

Major threats and vulnerabilities that can result in a compromised database server and the potential destruction or theft of sensitive data [2]

The main threats to a database server are:

  • SQL injection
  • Network eavesdropping
  • Unauthorized server access
  • Password cracking

SQL Injection

  • With a SQL injection attack, the attacker exploits vulnerabilities in the application's input validation and data access code to run arbitrary commands in the database using the security context of the Web application.

Vulnerabilities

  • Vulnerabilities exploited by SQL injection include:
  • Poor input validation in the Web applications
  • Unsafe, dynamically constructed SQL commands
  • Over-privileged application logins to the database
  • Weak permissions that fail to restrict the application's login to the database

Countermeasures

  • To counter SQL injection attacks:
  • The application should constrain and sanitize input data before using it in SQL queries.
  • Use type safe SQL parameters for data access. These can be used with stored procedures or dynamically constructed SQL command strings. *Using SQL parameters ensures that input data is subject to type and length checks and also that injected code is treated as literal *data, not as executable statements in the database.
  • Use a SQL Server login that has restricted permissions in the database. Ideally, user should grant execute permissions only to selected *stored procedures in the database and provide no direct table access.

Network Eavesdropping

  • The deployment architecture of most applications includes a physical separation of the data access code from the database server. As a result, sensitive data, such as application-specific data or database login credentials, must be protected from network eavesdroppers.

Vulnerabilities Vulnerabilities that increase the likelihood of network eavesdropping include:

  • Insecure communication channels

Passing credentials in clear text to the database for example

  • Using SQL authentication instead of Windows authentication
  • Using SQL authentication without a server certificate

Countermeasures To counter network eavesdropping:

  • Use Windows authentication to connect to the database server to avoid sending credentials over the network.
  • Install a server certificate on the database server. This results in the automatic encryption of SQL credentials over the network.
  • Use an SSL connection between the Web server and database server to protect sensitive application data. This requires a database server certificate.
  • Use an IPSec encrypted channel between Web and database server.

Unauthorized Server Access

Direct access to user database server should be restricted to specific client computers to prevent unauthorized server access.

Vulnerabilities Vulnerabilities that make the database server susceptible to unauthorized server access include:

  • Failure to block the SQL Server port at the perimeter firewall
  • Lack of IPSec or TCP/IP filtering policies

Attacks Direct connection attacks exist for both authenticated users and those without a user name and password; for example

  • Tools such as Query Analyzer (Isqlw.exe) or the command line equivalent (Osql.exe) are used to establish a direct connection to SQL Server and issue commands.
  • Server information, such as software version, is revealed to an attacker who sends carefully constructed packets to listening ports.

Countermeasures To counter these attacks:

  • Make sure that SQL Server ports are not visible from outside of the perimeter network.
  • Within the perimeter, restrict direct access by unauthorized hosts, for example, by using IPSec or TCP/IP filters.

Password Cracking

  • A common first line of attack is to try to crack the passwords of well known account names, such as sa (the SQL Server administrator account).

Vulnerabilities Common vulnerabilities that lead to password cracking are:

  • Weak or blank passwords
  • Passwords that contain everyday words

Attacks Common password cracking attacks include:

  • Dictionary attacks
  • Manual password guessing

Countermeasures To counter these attacks:

  • Create passwords for SQL Server login accounts that meet complexity requirements.
  • Avoid passwords that contain common words found in the dictionary.

Starting to install MySQL database

  • There are many implementations of the SQL database language available on Linux and Unix-like systems. MySQL and MariaDB are two popular options for deploying relational databases in server environments.
  • However, like most software, these tools can be security liabilities if they are configured incorrectly. This tutorial will guide user through some basic steps, user can take to secure MariaDB or MySQL databases, and ensure that they are not an open door into VPS.
  • For the sake of simplicity and illustration, using the MySQL server on an Ubuntu 12.04 VPS instance. However, these techniques can be applied to other Linux distributions and can be used with MariaDB as well. [9]

Initial Setup

  • MySQL gives user an opportunity to take the first step towards security during installation. It will request that user set a root password.
Configuring mysql-server [3]
  • sudo apt-get install mysql-server

User can always set the root password at a later time, but there is no reason to skip this step, so user should secure his administrator account from the very beginning.

Once the installation is complete, user should run a few included scripts. First, user will use the "mysql_install_db" script to create a directory layout for our databases.

  • sudo mysql_install_db


Next, run the script called "mysql_secure_installation". This will guide through some procedures that will remove some defaults that are dangerous to use in a production environment.

  • sudo mysql_secure_installation

it will first prompt user for the root password user set up during installation. Immediately following, user will be asked a series of questions, beginning with if you'd like to change the root password.

This is another opportunity to change user password to something secure if user have not done so already.

User should answer "Y" (for yes) to all of the remaining questions.

This will remove the ability for anyone to log into MySQL by default, disable logging in remotely with the administrator account, remove some test databases that are insecure, and update the running MySQL instance to reflect these changes.


Security Considerations

The overarching theme of securing MySQL (and almost any other system) is that access should be granted only when absolutely necessary. User data safety sometimes comes down to a balance between convenience and security.

In this Wiki, user will learn on the side of security, although user specific usage of the database software may lead user to pick and choose from these options.

Security Through the My.cnf File

The main configuration file for MySQL is a file called "my.cnf" that is located in the "/etc/mysql/" directory on Ubuntu and the "/etc/" directory on some other VPS.

First changing some settings in this file to lock down user MySQL instance.

Open the file with root privileges. Change the directory path as needed if user are following this tutorial on a different system

sudo nano /etc/mysql/my.cnf

The first setting that should check is the "bind-address" setting within the "[mysqld]" section. This setting should be set to user local loopback network device, which is "127.0.0.1". bind-address = 127.0.0.1

  • This makes sure that MySQL is not accepting connections from anywhere except for the local machine.
  • If user need to access this database from another machine, consider connecting through SSH to do user database querying and administration locally and sending the results through the ssh tunnel.
  • The next hole will patch is a function that allows access to the underlying filesystem from within MySQL. This can have severe security implications and should be shut off unless user absolutely need it.

In the same section of the file, user will add a directive to disable this ability to load local files: local-infile=0 This will disable loading files from the file system for users without file level privileges to the database.

MySQL log, error log [4]

If the owner have enough space and are not operating a huge database, it can be helpful to log additional information to keep an eye on suspicious activity.

Logging too much can create a performance hit, so this is something user need to weigh carefully.

User can set the log variable within the same "[mysqld]" section that we've been adding to.

log=/var/log/mysql-logfile

User have to make sure that the MySQL log, error log, and mysql log directory are not world readable

sudo ls -l /var/log/mysql*

Securing MySQL From Within

  • There are a number of steps user can take while using MySQL to improve security.
  • User will be inputting the commands in this section into the MySQL prompt interface, so user need to log in.

mysql -u root -p

User will be asked for the root password that set up earlier.

Securing Passwords and Host Associations

SELECT User,Host,Password FROM mysql.user;

Securing Passwords and Host Associations example [5]

As user can see, in this example set up, the user "demo-user" has no password and is valid regardless of what host he is on. This is very insecure.

User can set a password for the user with this command. Change "newPassWord" to reflect the password that user wish to assign.

UPDATE mysql.user SET Password=PASSWORD('newPassWord') WHERE User="demo-user";

Checking the User table again, user will see that the demo user now has a password.

SELECT User,Host,Password FROM mysql.user;

Securing Passwords and Host Associations example [6]


If user look in the "Host" field, user will see that still have a "%", which is a wildcard that means any host. This is not what want to be. Let's change that to be "local-host".

UPDATE mysql.user SET Host='localhost' WHERE User="demo-user";

Checking again, user table now has the appropriate fields set.

SELECT User,Host,Password FROM mysql.user;

If the table contains any blank users (it should not at this point since ran "mysql_secure_installation", but it will cover this anyways), user should remove them.

To do this, the root user can use the following call to delete blank users from the access table.

DELETE FROM mysql.user WHERE User="";

After the root user are done modifying the User table, user need to input the following command to implement the new permissions.

FLUSH PRIVILEGES;

Implementing Application-Specific Users

Similar to the practice of running processes within Linux as an isolated user, MySQL benefits from the same kind of isolation.

Each application that uses MySQL should have its own user that only has limited privileges and only has access to the databases it needs to run.

When the root user configure a new application to use MySQL, user should create the databases needed by that application.

create database testDB;

Query OK, 1 row affected (0.00 sec)

Next, the database owner should create a user to manage that database, and assign it only the privileges it needs. This will vary by application, and some uses need more open privileges than others.

To create a new user, use the following command.

CREATE USER 'demo-user'@'localhost' IDENTIFIED BY 'password';

User can grant the new user privileges on the new table with the following command. See the tutorial on how to create a new user and grant permissions in MySQL to learn more about specific privileges. [10]

GRANT SELECT,UPDATE,DELETE ON testDB.* TO 'demo-user'@'localhost';


As an example, if the user later need to revoke update privileges from the account, user could use the following command.

REVOKE UPDATE ON testDB.* FROM 'demo-user'@'localhost';

If user need all privileges on a certain database he can specify that with the following.

GRANT ALL ON testDB.* TO 'demo-user'@'localhost';

Securing Passwords and Host Associations example [7]

To show the current privileges of a user, user first must implement the privileges that specified using the "flush privileges" command. Then, and query what grants a user has:

FLUSH PRIVILEGES;

show grants for 'demo-user'@'localhost';

Always flush privileges when the root user are finished making changes.

Changing the Root User

One additional step that the root user may want to take is to change the root login name. If an attacker is trying to access the root MySQL login, they will need to perform the additional step of finding the user-name.

The root login can be changed with the following command. rename user 'root'@'localhost' to 'newAdminUser'@'localhost';

User can see the change by using the same query that been used for the User database.

select user,host,password from mysql.user;

Again!!, user must flush privileges for these changes to happen.

FLUSH PRIVILEGES;

User have to remember that to log into MySQL as the newly created username from now on when user wish to perform administrative tasks.

mysql -u newAdminUser -p

Summary

Database is always at risk, cause one small mistake and the hacker can access to the database from different ways with different methods, user should now have a general understanding about databases and how to create a secure database. present article was for beginners that try to gain an understanding of databases security. Databases are not like most other files and require a little bit of thought in order for user to understand the concept of securing the database.

See also

1.Database general understanding

2.Database security

3.Building Secure Data Access

4.Securing database

References

1.Database Threats and Countermeasures

2.Securing the database

3.Grant permissions in mysql