MySQL Security: Difference between revisions

From The Uniform Server Wiki
Jump to navigation Jump to search
(New page: <span id="top"></span> {| cellpadding="2" |__TOC__||'''MySQL Security''' |} '''Introduction''' After extracting Uniform Server your first task should be to change the MySQL password. How...)
 
m (Reverted edits by Upazixorys (Talk); changed back to last version by BobS)
 
(2 intermediate revisions by 2 users not shown)
Line 5: Line 5:
'''Introduction'''
'''Introduction'''


After extracting Uniform Server your first task should be to change the MySQL password.
After extracting Uniform Server, your first task should be to change the MySQL password. However doing so will render all pre-configured plugin applications inoperable.


However doing so will render all pre-configured plugin applications inoperable.
To resolve this issue, you can edit each applications configuration file and change the password accordingly. This method is '''very high risk''' especially if you intend to run a '''production server'''.
 
To resolve this issue you can edit each applications configuration file and change the password accordingly. This method is '''very high risk''' especially if you intend to run a '''production server'''.


The following is a step-by-step guide how to '''secure MySQL server''' on Uniform Server.
The following is a step-by-step guide how to '''secure MySQL server''' on Uniform Server.


== Security issues ==
== Security issues ==
You probably are wondering what all the fuss is about the following attempts to explain the levels of security and why the root user is so dangerous.
You probably are wondering what all the fuss is about. The following attempts to explain the levels of security and why the root user is so dangerous.


'''''[[#top | Top]]'''''
=== Root User ===
=== Root User ===
A default installation of Uniform Server has a single user named '''root''' with password '''root'''. This user has full global privileges, significance of this privilege is '''server wide''' hence root user has full power to create and '''delete any database or table'''.
A default installation of Uniform Server has a single MySQL user named '''root''' with password '''root'''. This user has full global privileges. The significance of this privilege is '''server wide''', meaning the root user has full power to create and '''delete any database or table'''.


Changing the MySQL password (root user password) from Apanel protects this user. However the root user should only be used for server administration and be the only user to connect to the server with these privileges.
Changing the MySQL password (root user password) from Apanel protects this user. However the root user should only be used for server administration and be the only user to connect to the server with these privileges.


'''''[[#top | Top]]'''''
=== Application connection ===
=== Application connection ===
When installing an application you are required to enter a '''database name''' along with a '''user name''' and '''password'''. Using the root user for installation is convenient generally installation will proceed with no problems.
When installing an application, you are required to enter a '''database name''' along with a '''user name''' and '''password'''. Using the root user for installation is convenient and generally installation will proceed with no problems.


Although convenient unless steps are taken using root is an '''''extremely serious security issue'''''. Every time an application connects to the MySQL server it does so with all granted privileges. Should the application be compromised in anyway your entire MySQL sever is at risk.
Although convenient (unless steps are taken), using root is an '''''extremely serious security issue'''''. Every time an application connects to the MySQL server it does so with all granted privileges. Should the application be compromised in any way, your entire MySQL sever is at risk.


'''''[[#top | Top]]'''''
=== Application security ===
=== Application security ===
Application programmers do their utmost to protect the application from malicious users. If the application becomes compromised generally only the application is killed however since the application connected with full privileges you can kiss your MySQL server good-bye.
Application programmers do their utmost to protect the application from malicious users. If the application becomes compromised, generally only the application is killed. However if the application connected with full privileges (as root) you can kiss your MySQL server good-bye.


To put the above into context for a test server the only person that can compromise your server is you hence not an issue. Putting your servers online any user can potentially compromise your servers hence you '''must take security seriously'''.
To put the above into context for a test server, the only person that can compromise your server is you, which is not an issue. On the other hand, when putting your servers online, ''any'' user can potentially compromise your servers, so you '''must take security seriously'''.


'''''[[#top | Top]]'''''
'''''[[#top | Top]]'''''
Line 39: Line 34:
# Change the MySQL root password
# Change the MySQL root password
# Install an application using root user
# Install an application using root user
# Create a new user with limited privileges for that applications database only
# Create a new user with limited privileges for that application's database only
# Change the applications configuration file to connect using the above user.
# Change the application's configuration file to connect using the above user.


'''''[[#top | Top]]'''''
== Detail ==
== Detail ==


Line 51: Line 45:
'''''Change MySQL password'''''
'''''Change MySQL password'''''


# Start UniTray: Navigate to folder '''UniServer''' double click on '''Start.exe'''
# Start UniTray: Navigate to folder '''UniServer''' and double click on '''Start.exe'''
# Start Uniform Server: Left click UniTray > Click Start UniServer '''A) B)'''
# Start Uniform Server: Left click UniTray > Click Start UniServer '''A) B)'''
# Run Apanel: Left click UniTray > Click Admin Panel '''C)'''
# Run Apanel: Left click UniTray > Click Admin Panel '''C)'''
# Apanel left menu click  '''MySQL Server Configuration''' '''D)'''
# Apanel left menu click  '''MySQL Server Configuration''' '''D)'''
# Enter '''new MySQL Password''' (for example fred123) choose a secure one '''E)'''
# Enter '''new MySQL Password''' (for example fred123) choose a secure one '''E)'''
# Click Change (If challenged by your firewall allow access) '''F)'''
# Click Change (If challenged by your firewall, allow access) '''F)'''
|
|
[[Image:MySQL_security_1.gif]]
[[Image:MySQL_security_1.gif]]
|}
|}
'''''[[#top | Top]]'''''
=== Install an application ===
=== Install an application ===
# Install application as per instructions.<br />Note: From the above MySQL server host is '''localhost''' user name '''root''' password f'''red123'''<br />Use the password you configured.<br /><br />
# Install application as per instructions.<br />Note: From the above MySQL server host is '''localhost''' user name '''root''' password '''fred123'''<br />Use the password you configured.<br /><br />
# For the remainder of this example I will assume you are using the MediaWiki plugin '''V55_MediaWiki_1_15_1.exe'''<br />Note: This is pre-configured and uses user name '''root''' and password '''root'''
# For the remainder of this example, I will assume you are using the MediaWiki plugin '''V55_MediaWiki_1_15_1.exe'''<br />Note: This is pre-configured and uses user name '''root''' and password '''root'''


'''''[[#top | Top]]'''''


=== Create a new user ===
=== Create a new user ===
Line 76: Line 67:
# Run phpMyAdmin: Left click UniTray > Click phpMyAdmin '''G)'''
# Run phpMyAdmin: Left click UniTray > Click phpMyAdmin '''G)'''
# phpMyAdmin page is displayed. From the top menu click on '''privileges''' '''H)'''<br />This opens a new page displaying all users
# phpMyAdmin page is displayed. From the top menu click on '''privileges''' '''H)'''<br />This opens a new page displaying all users
'''''Note'':''' A user in this context is the name of a MySQL user that is allowed to connect to the MySQL server. This name has no relationship to any application's users.
# Click on '''Add a new User I)''' This opens the Add a new user page<br /><br /><br />
# Click on '''Add a new User I)''' This opens the Add a new user page<br /><br /><br />
# Enter required user name (example WikiUser) '''J)'''
# Enter required user name (example: WikiUser) '''J)'''
# Select Host from drop down menu. Select '''localhost''' restricts access '''K)'''
# Select Host from drop down menu. Select '''localhost''' to restrict access '''K)'''
# Enter password (from drop down menu select '''Use Text Field''') '''L)'''
# Enter password (from drop down menu, select '''Use Text Field''') '''L)'''
# Confirm password '''M)'''<br />Note for this example I entered '''user123'''<br /><br />'''''Note'':''' The Add new User pages allows you to set global privileges for the user. This user is being created with absolute minimum privileges hence no global privileges are set. Ensure all preveleges are unchecked.<br /><br />
# Confirm password '''M)'''<br />Note: for this example I entered '''user123'''<br /><br />'''''Note'':''' The Add new User pages allows you to set global privileges for the user. This user is being created with absolute minimum privileges, so no global privileges are set. Ensure all privileges are unchecked.<br /><br />
# Scroll down page and click '''Go''' '''N)'''<br /><br /><br /><br /><br /><br /><br />
# Scroll down page and click '''Go''' '''N)'''<br /><br /><br /><br /><br /><br /><br />
# Our new user is created and confirmation provided at top of the page. A new section is automatically added to current page named '''Database Specific Privileges''' scroll to this section.<br /><br />Note: You can assign more than one database to a user however this is a user with limited access hence a single database is assigned.<br /><br />
# Our new user is created and confirmation provided at the top of the page. A new section is automatically added to the current page named '''Database Specific Privileges'''; scroll to this section.<br /><br />Note: You can assign more than one database to a user, however for this user we are limiting access to a single database.<br /><br />
# From the drop down menu select '''wikidb''' '''O)'''<br /> This automatically openens a new page where you can assign priveleges to the user that are specific to the Wiki database '''wikidb'''<br /><br /><br /><br />
# From the drop down menu, select '''wikidb''' '''O)'''<br /> This automatically opens a new page where you can assign privileges to the user that are specific to the Wiki database '''wikidb'''<br /><br /><br /><br />
# When assigning privileges assign the absolute minimum possible to allow an application to run. If the application fails you can always go back and edit user privileges and enable more as required.<br /><br /> For MediaWiki the minimum is shown see section '''Data P)'''  
# When assigning privileges, assign the absolute minimum possible to allow an application to run. If the application fails, you can always go back and edit user privileges and enable more as required.<br /><br /> For MediaWiki the minimum is shown; see section '''Data P)'''  
# Scroll down page and click '''Go Q)'''   
# Scroll down page and click '''Go Q)'''   




'''''Note'':''' A user in this context is the name of a user that is allowed to connect to the MySQL server. This name has no relationship to any applications users.
Generally for proper security you would create a new user and password per application.   
 
 
Generally for maximum security you would create a new user and password per application.   
          
          
|
|
[[Image:MySQL_security_2.gif]]
[[Image:MySQL_security_2.gif]]
|}
|}
'''''[[#top | Top]]'''''
 


=== Edit application configuration file ===
=== Edit application configuration file ===
Line 143: Line 134:
* Tools > Options > Click Privacy > Click Show cookies button > Click Remove all cookies
* Tools > Options > Click Privacy > Click Show cookies button > Click Remove all cookies
* Tools > Options > Click Advanced > Click Clear Now
* Tools > Options > Click Advanced > Click Clear Now
'''''[[#top | Top]]'''''


== Conclusion ==
== Conclusion ==
Installing applications using root makes the whole process relatively easy since you have full privileges. After installation check the application runs then secure the MySQL server with a user with restricted privileges as explained above.
Installing applications using root makes the whole process relatively easy since you have full privileges. After installation check the application runs, then secure the MySQL server with a user that has restricted privileges as explained above.




For a production server it is essential to secure your MySQL server. For a test server the choice is yours however if you put a test server online again you must secure it.
For a production server it is essential to secure your MySQL server. For a test server the choice is yours, however if you put a test server online you must secure it.


'''''[[#top | Top]]'''''
'''''[[#top | Top]]'''''

Latest revision as of 08:32, 24 November 2010

MySQL Security

Introduction

After extracting Uniform Server, your first task should be to change the MySQL password. However doing so will render all pre-configured plugin applications inoperable.

To resolve this issue, you can edit each applications configuration file and change the password accordingly. This method is very high risk especially if you intend to run a production server.

The following is a step-by-step guide how to secure MySQL server on Uniform Server.

Security issues

You probably are wondering what all the fuss is about. The following attempts to explain the levels of security and why the root user is so dangerous.

Root User

A default installation of Uniform Server has a single MySQL user named root with password root. This user has full global privileges. The significance of this privilege is server wide, meaning the root user has full power to create and delete any database or table.

Changing the MySQL password (root user password) from Apanel protects this user. However the root user should only be used for server administration and be the only user to connect to the server with these privileges.

Application connection

When installing an application, you are required to enter a database name along with a user name and password. Using the root user for installation is convenient and generally installation will proceed with no problems.

Although convenient (unless steps are taken), using root is an extremely serious security issue. Every time an application connects to the MySQL server it does so with all granted privileges. Should the application be compromised in any way, your entire MySQL sever is at risk.

Application security

Application programmers do their utmost to protect the application from malicious users. If the application becomes compromised, generally only the application is killed. However if the application connected with full privileges (as root) you can kiss your MySQL server good-bye.

To put the above into context for a test server, the only person that can compromise your server is you, which is not an issue. On the other hand, when putting your servers online, any user can potentially compromise your servers, so you must take security seriously.

Top

General Steps

  1. Change the MySQL root password
  2. Install an application using root user
  3. Create a new user with limited privileges for that application's database only
  4. Change the application's configuration file to connect using the above user.

Detail

Change MySQL password

Change MySQL password

  1. Start UniTray: Navigate to folder UniServer and double click on Start.exe
  2. Start Uniform Server: Left click UniTray > Click Start UniServer A) B)
  3. Run Apanel: Left click UniTray > Click Admin Panel C)
  4. Apanel left menu click MySQL Server Configuration D)
  5. Enter new MySQL Password (for example fred123) choose a secure one E)
  6. Click Change (If challenged by your firewall, allow access) F)

Install an application

  1. Install application as per instructions.
    Note: From the above MySQL server host is localhost user name root password fred123
    Use the password you configured.

  2. For the remainder of this example, I will assume you are using the MediaWiki plugin V55_MediaWiki_1_15_1.exe
    Note: This is pre-configured and uses user name root and password root


Create a new user

Change MySQL password

  1. Start Uniform Server: Left click UniTray > Click Start UniServer A) B)
  2. Run phpMyAdmin: Left click UniTray > Click phpMyAdmin G)
  3. phpMyAdmin page is displayed. From the top menu click on privileges H)
    This opens a new page displaying all users

Note: A user in this context is the name of a MySQL user that is allowed to connect to the MySQL server. This name has no relationship to any application's users.

  1. Click on Add a new User I) This opens the Add a new user page


  2. Enter required user name (example: WikiUser) J)
  3. Select Host from drop down menu. Select localhost to restrict access K)
  4. Enter password (from drop down menu, select Use Text Field) L)
  5. Confirm password M)
    Note: for this example I entered user123

    Note: The Add new User pages allows you to set global privileges for the user. This user is being created with absolute minimum privileges, so no global privileges are set. Ensure all privileges are unchecked.

  6. Scroll down page and click Go N)






  7. Our new user is created and confirmation provided at the top of the page. A new section is automatically added to the current page named Database Specific Privileges; scroll to this section.

    Note: You can assign more than one database to a user, however for this user we are limiting access to a single database.

  8. From the drop down menu, select wikidb O)
    This automatically opens a new page where you can assign privileges to the user that are specific to the Wiki database wikidb



  9. When assigning privileges, assign the absolute minimum possible to allow an application to run. If the application fails, you can always go back and edit user privileges and enable more as required.

    For MediaWiki the minimum is shown; see section Data P)
  10. Scroll down page and click Go Q)


Generally for proper security you would create a new user and password per application.


Edit application configuration file

Continuing with our MediaWiki example final step is to edit the configuration file.

Open file UniServer\www\wiki\LocalSettings.php in a text editor.

Locate this section: 
## Database settings
$wgDBtype           = "mysql";
$wgDBserver         = "localhost";
$wgDBname           = "wikidb";
$wgDBuser           = "root";
$wgDBpassword       = "root";
Change $wgDBuser and $wgDBpassword as shown below: 
## Database settings
$wgDBtype           = "mysql";
$wgDBserver         = "localhost";
$wgDBname           = "wikidb";
$wgDBuser           = "WikiUser";
$wgDBpassword       = "user123";

Top

General note

After making any significant changes always restart servers and flush your browser cache and delete all cookies.

For Firefox:

  • Tools > Options > Click Privacy > Click Show cookies button > Click Remove all cookies
  • Tools > Options > Click Advanced > Click Clear Now

Conclusion

Installing applications using root makes the whole process relatively easy since you have full privileges. After installation check the application runs, then secure the MySQL server with a user that has restricted privileges as explained above.


For a production server it is essential to secure your MySQL server. For a test server the choice is yours, however if you put a test server online you must secure it.

Top

Retrieved from "https://wiki.uniformserver.com/index.php?title=MySQL_Security&oldid=4815"