Programming/MySQL/Setup: Difference between revisions

From Dev Wiki
Jump to navigation Jump to search
(Update installation section)
(Clean up page and add more user commands)
Line 21: Line 21:




== User Accounts ==
== User Management ==
=== Connecting as Root ===
{{ note | Unless otherwise specified, all commands from this section assume you're in the [[PostgreSQL#Basics | PostgreSQL shell]]. }}
{{ Warn | Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}
 
=== List All Users ===
CREATE USER user FROM mysql.user;
 
=== Create User ===
CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';


For previous versions of MySQL, it was standard to default the root user as a login user with a password of {{ ic |root}}. This is no longer the default, but can easily be set with a few commands. First, enter the MySQL shell. Then enter:
=== Remove User===
{{ bc | ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; }}
DROP USER '<username>'@'localhost';
* This will set the root user's password to "root".
* Alternatively, alter the password to whatever you desire, if you want to add a tiny bit more security (setting root as a login-able password user is still not very recommended for security reasons).
{{ bc | FLUSH PRIVILEGES; }}
* This ensures that changes will propagate through the server without a reboot.


=== Change User Password ===
ALTER USER '<username>'@'localhost' IDENTIFIED BY '<password>';


Once set, the {{ ic |sudo mysql}} command will likely no longer work. Instead, login with root as {{ ic | mysql -u root -p}}.
=== User Permissions ===
Once created, a user account needs to be given permission in order to be able to do anything. Note that, from a security standpoint, it's always best to give the minimum possible permissions necessary for the user to do their job.


=== Adding a New User Account ===
MySQL generally assumes permissions will be granted per database. And for security, it even allows restricting access per table.
Add a new user account with:
{{ bc | CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>'; }}


Where {{ ic |<username>}} and {{ic |<password>}} are replaced by the desired username and password for the user account.
Thus for security, the general syntax is to specify both database and table to set permissions for. To set the permission for the user on all tables in the database, or to set the permission on all database, use an {{ ic |*}} character instead of the database or table name.


==== Add User Permissions ====
GRANT <permission> ON <database_name>.<table_name> TO '<username>'@'localhost';


=== User Permissions ===
==== Remove User Permissions ====
Once created, a user account needs to be given permission in order to be useful at all. Note that, from a security standpoint, it's always best to give the minimum possible permissions necessary for the user to do their job.
GRANT <permission> ON <database_name>.<table_name> TO '<username>'@'localhost';


==== Full Permissions ====
==== Full Permissions ====
{{ Warn | Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}
{{ Warn | Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}


To give a user full permissions (like the root account), launch the MySQL Shell, then run:
To give a user full permissions (like the root account), run:
{{ bc | GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost' WITH GRANT OPTION; }}
GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost' WITH GRANT OPTION;
* Where {{ ic | username}} is replaced by the name of the user account to adjust.
FLUSH PRIVILEGES;
{{ bc | FLUSH PRIVILEGES; }}
 
* This ensures that changes will propagate through the server without a reboot.
The {{ ic |FLUSH PRIVILEGES}} line ensures that changes will propagate through the server without a reboot.
 
=== Connecting as Root ===
{{ Warn | Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}
 
For previous versions of MySQL, it was standard to default the root user as a login user with a password of {{ ic |root}}. This is no longer the default, but can easily be set with a few commands:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
FLUSH PRIVILEGES;
 
Alternatively, alter the password to whatever you desire, if you want to add a tiny bit more security (setting root as a login-able password user is still not very recommended for security reasons).
 
The {{ ic |FLUSH PRIVILEGES}} line ensures that changes will propagate through the server without a reboot.
 
 
Once set, the {{ ic |sudo mysql}} command will likely no longer work. Instead, login with root as {{ ic |mysql -u root -p}}.

Revision as of 06:15, 20 November 2020

Installation

Windows

Download and install from https://dev.mysql.com/downloads/installer/

Arch Linux

Arch Linux recommends installing MariaDB instead.
For installation details, see MariaDB setup.

Ubuntu

Basic installation can be performed with: 

 sudo apt install mysql-server

From here, you can access MySQL via: 

 sudo mysql

Securing the Installation

Note that the above command alone is an insecure installation, and is not suitable for things like production, or instances where stored data is not just test data and thus should be stored securely.

To configure MySQL for security also run: 

 sudo mysql_secure_installation


User Management

Note: Unless otherwise specified, all commands from this section assume you're in the PostgreSQL shell.

List All Users

CREATE USER user FROM mysql.user;

Create User

CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';

Remove User

DROP USER '<username>'@'localhost';

Change User Password

ALTER USER '<username>'@'localhost' IDENTIFIED BY '<password>';

User Permissions

Once created, a user account needs to be given permission in order to be able to do anything. Note that, from a security standpoint, it's always best to give the minimum possible permissions necessary for the user to do their job.

MySQL generally assumes permissions will be granted per database. And for security, it even allows restricting access per table.

Thus for security, the general syntax is to specify both database and table to set permissions for. To set the permission for the user on all tables in the database, or to set the permission on all database, use an * character instead of the database or table name.

Add User Permissions

GRANT <permission> ON <database_name>.<table_name> TO '<username>'@'localhost';

Remove User Permissions

GRANT <permission> ON <database_name>.<table_name> TO '<username>'@'localhost';

Full Permissions

Warn: Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter.

To give a user full permissions (like the root account), run: 

GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

The FLUSH PRIVILEGES line ensures that changes will propagate through the server without a reboot.

Connecting as Root

Warn: Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter.

For previous versions of MySQL, it was standard to default the root user as a login user with a password of root. This is no longer the default, but can easily be set with a few commands: 

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
FLUSH PRIVILEGES;

Alternatively, alter the password to whatever you desire, if you want to add a tiny bit more security (setting root as a login-able password user is still not very recommended for security reasons).

The FLUSH PRIVILEGES line ensures that changes will propagate through the server without a reboot.


Once set, the sudo mysql command will likely no longer work. Instead, login with root as mysql -u root -p.

Retrieved from "https://wiki.brandon-rodriguez.com/index.php?title=Programming/MySQL/Setup&oldid=518"