Programming/MySQL/Setup: Difference between revisions
Brodriguez (talk | contribs) (Correct commands and organize page further) |
Brodriguez (talk | contribs) (Add list of available permissions) |
||
Line 58: | Line 58: | ||
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. | 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. | ||
=== Permission Types === | |||
You can see the full, official documentation of current permissions at https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html. | |||
However, that page is kind of dense, so here's a condensed list of the main privileges you'll likely need at any given time: | |||
* {{ ic |ALL PRIVILEGES}} - Gives full permission access to the specified database. If no database is selected, then gives user all possible MySQL permissions. | |||
* {{ ic |GRANT OPTION}} - Gives permission to grant or remove other's privileges. | |||
* {{ ic |CREATE}} - Gives permission to create new tables or databases. | |||
* {{ ic |DROP}} - Gives permission to delete tables or databases. | |||
* {{ ic |SELECT}} - Gives permission to pull data from tables or databases. | |||
* {{ ic |INSERT}} - Gives permission to insert rows into tables. | |||
* {{ ic |UPDATE}} - Gives permission to update table rows. | |||
* {{ ic |DELETE}} - Gives permission too delete rows from tables. | |||
=== Show User Permissions === | === Show User Permissions === |
Revision as of 06:53, 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
General User Management
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>';
Connecting as Root
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
.
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.
Permission Types
You can see the full, official documentation of current permissions at https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html.
However, that page is kind of dense, so here's a condensed list of the main privileges you'll likely need at any given time:
ALL PRIVILEGES
- Gives full permission access to the specified database. If no database is selected, then gives user all possible MySQL permissions.GRANT OPTION
- Gives permission to grant or remove other's privileges.CREATE
- Gives permission to create new tables or databases.DROP
- Gives permission to delete tables or databases.SELECT
- Gives permission to pull data from tables or databases.INSERT
- Gives permission to insert rows into tables.UPDATE
- Gives permission to update table rows.DELETE
- Gives permission too delete rows from tables.
Show User Permissions
SHOW GRANTS FOR '<username>'@'localhost';
Add User Permissions
GRANT <permission> ON <database_name>.<table_name> TO '<username>'@'localhost';
Remove User Permissions
REVOKE <permission> ON <database_name>.<table_name> FROM '<username>'@'localhost';
Full Permissions
To give a user full permissions (like the root account), run:
GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost' WITH GRANT OPTION;