15 Securing Databases.md

Chapter 15 Securing Databases

introduction

Creating a User

Ip address

CREATE USER john@127.0.0.1

Hostname

CREATE USER john@localhost

Domain name

CREATE USER john@'%.codewithmosh.com'

No restriction and set password

CREATE USER john IDENTIFIED BY '1234';

View Users

SELECT * FROM mysql.user;

Or on the left side -> open Administration -> Users and Privileges

Dropping Users

CREATE USER bob@codewithmosh.com IDENTIFIED BY '1234';
DROP USER bob@codewithmosh.com;

Changing Passwords

For a certain user:

SET PASSWORD FOR john = '1234';

For current user:

SET PASSWORD = '1234';

Or on the left side -> open Administration -> Users and Privileges ->change/expire password -> apply

Granting Privileges

1: web/desktop application

CREATE USER moon_app IDENTIFIED BY '1234';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON sql_store.*
TO moon_app;

Search mysql privileges 2: admin

GRANT ALL
ON *.* -- all databases and all tables
TO john

Viewing Privileges

For a certain user:

SHOW GRANTS FOR john;

For current user

SHOW GRANTS;

Or on the left side -> open Administration -> Users and Privileges -> Administrative Roles/Schema Privileges

Dropping Privileges

REVOKE CREATE VIEW
ON sql_store.*
FROM moon_app;

Take security seriously, always grant the minimum required for user account.