create mysql db


You should -not- use this example of password! CREATE USER 'myusername'@'localhost' IDENTIFIED BY 'Xm.-!%RQZCMNd4fb79mtLBdFrI1968'; GRANT SELECT ON *.* TO 'myusername'@'localhost'; <- this is a User with "read only" privileges! FLUSH PRIVILEGES; mysql> SHOW GRANTS FOR 'myusername'@'localhost'; +-------------------------------------------------+ | GRANT SELECT ON *.* TO 'myusername'@'localhost' | +-------------------------------------------------+ mysql> select * from user; +------------+------------------------------------------------------------------+----------------------------------+ | myusername | 572a9be07f338370e56cb0ab43001865fc5737904a100125ad1176d3cae84c3e | 20d3c040b196f337548d71db6a957ef6 | +------------+------------------------------------------------------------------+----------------------------------+ 1 row in set (0.00 sec) mysql> SHOW CREATE USER 'myusername'@'localhost'; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE USER 'myusername'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*EFE9E0806AA9F9E8C8E8E8E6FDA428503AA7F608' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) quit mysql -N -u myusername -p Xm.-!%RQZCMNd4fb79mtLBdFrI1968 create database testdb; mysql> show databaeses; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 5 rows in set (0.00 sec use testdb; CREATE TABLE user ( uid VARCHAR(255), password VARCHAR(255), salt VARCHAR(255) ); mysql> SHOW TABLES; +------------------+ | Tables_in_testdb | +------------------+ | user | +------------------+ 1 row in set (0.00 sec) SET @uid = 'testuser'; SET @password ='XxL.-!%RQd4fb79mtLB123458'; SET @salt=MD5(RAND(LAST_INSERT_ID())); INSERT INTO user(uid,password,salt) VALUES (@uid, SHA2(CONCAT(@salt, @password),256), @salt); FLUSH PRIVILEGES; mysql> select * from user; +----------+------------------------------------------------------------------+----------------------------------+ | testuser | 572a9be07f338370e56cb0ab43001865fc5737904a100125ad1176d3cae84c3e | 20d3c040b196f337548d71db6a957ef6 | +----------+------------------------------------------------------------------+----------------------------------+ 1 row in set (0.00 sec) DROP DATABASE testdb; mysql> SHOW DATABASES; +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Reload all the privileges: flush privileges; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ALL [PRIVILEGES] Grant all privileges at specified access level except GRANT OPTION and PROXY. GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' with grant option; "WITH GRANT OPTION" <- es kann der User seine Rechte auch an andere User im System weitergeben. GRANT SELECT, EXECUTE ON *.* TO 'username'@'localhost'; GRANT SELECT, SHOW VIEW ON *.* TO 'username'@'127.0.0.1' IDENTIFIED BY 'password' REQUIRE SSL; ALTER Enable use of ALTER TABLE. Levels: Global, database, table. ALTER ROUTINE Enable stored routines to be altered or dropped. Levels: Global, database, routine. CREATE Enable database and table creation. Levels: Global, database, table. CREATE ROUTINE Enable stored routine creation. Levels: Global, database. CREATE TABLESPACE Enable tablespaces and log file groups to be created, altered, or dropped. Level: Global. CREATE TEMPORARY TABLES Enable use of CREATE TEMPORARY TABLE. Levels: Global, database. CREATE USER Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. Level: Global. CREATE USER 'username'@'127.0.0.1' IDENTIFIED BY 'password' WITH sha256_password PASSWORD EXPIRE INTERVAL 180 DAY PASSWORD HISTORY 5 MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100; PASSWORD EXPIRE NEVER; PASSWORD REUSE INTERVAL 360 DAY; CREATE VIEW Enable views to be created or altered. Levels: Global, database, table. DELETE Enable use of DELETE. Level: Global, database, table. DROP Enable databases, tables, and views to be dropped. Levels: Global, database, table. EVENT Enable use of events for the Event Scheduler. Levels: Global, database. EXECUTE Enable the user to execute stored routines. Levels: Global, database, routine. FILE Enable the user to cause the server to read or write files. Level: Global. GRANT OPTION Enable privileges to be granted to or removed from other accounts. Levels: Global, database, table, routine, proxy. INDEX Enable indexes to be created or dropped. Levels: Global, database, table. INSERT Enable use of INSERT. Levels: Global, database, table, column. LOCK TABLES Enable use of LOCK TABLES on tables for which you have the SELECT privilege. Levels: Global, database. PROCESS Enable the user to see all processes with SHOW PROCESSLIST. Level: Global. PROXY Enable user proxying. Level: From user to user. REFERENCES Enable foreign key creation. Levels: Global, database, table, column. RELOAD Enable use of FLUSH operations. Level: Global. REPLICATION CLIENT Enable the user to ask where master or slave servers are. Level: Global. REPLICATION SLAVE Enable replication slaves to read binary log events from the master. Level: Global. SELECT Enable use of SELECT. Levels: Global, database, table, column. SHOW DATABASES Enable SHOW DATABASES to show all databases. Level: Global. SHOW VIEW Enable use of SHOW CREATE VIEW. Levels: Global, database, table. SHUTDOWN Enable use of mysqladmin shutdown. Level: Global. SUPER Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command. Level: Global. TRIGGER Enable trigger operations. Levels: Global, database, table. UPDATE Enable use of UPDATE. Levels: Global, database, table, column. USAGE Synonym for “no privileges” Externe Quelle: https://dev.mysql.com/doc/refman/8.0/en/grant.html ------------------------------------------------------------------------------------------------------------------------------------------------------------------------