MySQL permissions and database design case study

/* User and permission management */ ------------------
User information table: mysql.user
 
-- FLUSH PRIVILEGES
 
-- Add user CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER username IDENTIFIED BY [PASSWORD] password (string)
    - You must have the global CREATE USER privilege for the mysql database, or have the INSERT privilege.
    - Can only create users, cannot grant permissions.
    - Username, note the quotes: e.g. 'user_name'@'192.168.1.1'
    - The password also needs to be quoted, and pure numeric passwords also need to be quoted - To specify the password in plain text, omit the PASSWORD keyword. To specify the password as a hashed value returned by the PASSWORD() function, include the keyword PASSWORD.
 
-- Rename user RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user
 
-- Set password SET PASSWORD = PASSWORD('password') -- Set password for current user SET PASSWORD FOR username = PASSWORD('password') -- Set password for specified user -- Delete user DROP USER kuangshen2
DROP USER username -- assign permissions/add users GRANT permission list ON table name TO username [IDENTIFIED BY [PASSWORD] 'password']
    - all privileges means all privileges - *.* means all tables in all libraries - library name.table name means a table under a library - View permissions SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR username - view the current user permissions SHOW GRANTS; or SHOW GRANTS FOR CURRENT_USER; or SHOW GRANTS FOR CURRENT_USER();
 
-- Revoke permissions REVOKE permission list ON table name FROM user name REVOKE ALL PRIVILEGES, GRANT OPTION FROM user name -- Revoke all permissions

-- Privilege list ALL [PRIVILEGES] -- Set all simple privileges except GRANT OPTION ALTER -- Allow use of ALTER TABLE
ALTER ROUTINE -- change or delete a stored procedure CREATE -- enable use of CREATE TABLE
CREATE ROUTINE -- create a stored routine CREATE TEMPORARY TABLES -- allow use of CREATE TEMPORARY TABLE
CREATE USER -- Enables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEW -- Allows the use of CREATE VIEW
DELETE -- Allows the use of DELETE
DROP -- Allows the use of DROP TABLE
EXECUTE -- Allows the user to run a stored procedure FILE -- Allows the use of SELECT ... INTO OUTFILE and LOAD DATA INFILE
INDEX -- Allows use of CREATE INDEX and DROP INDEX
INSERT -- Allows the use of INSERT
LOCK TABLES -- Allows you to use LOCK TABLES on tables for which you have SELECT privileges.
PROCESS -- Enable use of SHOW FULL PROCESSLIST
REFERENCES -- not implemented RELOAD -- FLUSH is allowed
REPLICATION CLIENT -- allows the user to ask for the address of a slave or master server REPLICATION SLAVE -- for replication slave servers (reading binary log events from the master server)
SELECT -- Allows the use of SELECT
SHOW DATABASES -- Displays all databases SHOW VIEW -- Enables the use of SHOW CREATE VIEW
SHUTDOWN -- allow use of mysqladmin shutdown
SUPER -- Enables use of CHANGE MASTER , KILL , PURGE MASTER LOGS , and SET GLOBAL statements, and the mysqladmin debug command; allows you to connect (once) even if max_connections has been reached.
UPDATE -- Allows the use of UPDATE
USAGE -- Synonym for "no privileges" GRANT OPTION -- Allow granting of privileges /* Table maintenance */
 
--Analyze and store the keyword distribution of the table ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE table name...
-- Check one or more tables for errors CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- Defragment the data file OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

-- Export 1. Export a table -- mysqldump -uroot -p123456 school student >D:/a.sql
  mysqldump -u username -p password database name table name > file name (D:/a.sql)
2. Export multiple tables -- mysqldump -uroot -p123456 school student result >D:/a.sql
  mysqldump -u username -p password database name table 1 table 2 table 3 > file name (D:/a.sql)
3. Export all tables --mysqldump -uroot -p123456 school >D:/a.sql
  mysqldump -u username -p password database name > file name (D:/a.sql)
4. Export a library -- mysqldump -uroot -p123456 -B school >D:/a.sql
  mysqldump -u username -p password -B database name > file name (D:/a.sql)
 
You can use -w to carry backup conditions -- Import 1. When logging in to mysql: -- source D:/a.sql
  source backup file 2. Without logging in, mysql -u username -p password database name < backup file