MYSQL Freshman Learning Handbook

Article from "MySQL must know must be"

MySQL book recommended: MySQL must know. is a more comprehensive explanation of the practical aspects of MySQL operation, to get started well. e-book online with PDF.

This article will introduce MySQL to create accounts, delete accounts, set up and describe the permissions of various accounts

To create a user account:

[SQL]View Plaincopyprint?

1. CREATE USER user_name identified by ' Your_password ';

CREATE USER user_name identified by ' Your_password ';

Renamed

[SQL]View Plaincopyprint?

1. RENAME USER old_name to new_name;

RENAME USER old_name to New_name;

Delete a user account

[SQL]View Plaincopyprint?

1. DROP USER user_name;

DROP USER user_name;

View user's permissions can be used

SHOW GRANTS for user_name;

If the display is: GRANT USAGE on *. * to ' user_name ' @ '% ' means no permissions!!!! The previous * represents any database, followed by any table in the database

Note: Users must have grant option permission to grant or revoke permissions for other users using Grant and invoke

Example: Giving SELECT permission

GRANT SELECT on testdb.* to user_name; Indicates that the SELECT permission for all tables under the TestDB database is assigned to user_name users.

Revoke permissions:

REVOKE SELECT on testdb.* from user_name; Represents the revocation of a user's SELECT permission on all tables in the TestDB database.

You can write more than one at a time, such as: GRANT SELECT, INSERT on testdb.* to user_name;

The permissions for MySQL are shown in the following table:

All	All permissions except Grant option
Alter	Using Alter TABLE
ALTER ROUTING	Use Alter PROCEDURE and drop PROCEDURE
CREATE	Using the Create TABLE
CREATE ROUTING	Using the Create PROCEDURE
CREATE Temporary TABLES	Using the Create temporary TABLE
CREATE USER	Use the Create user, DROP user, RENAME user, and revoke all privilleages
CREATE VIEW	Using the Create VIEW
DELETE	Using the Delete
DROP	Using the Drop TABLE
EXECUTE	Using call and stored procedures
FILE	Using SELECT INTO outfile and load DATA INFILE
GRANT OPTION	Using GRANT and REVOKE
INDEX	Using the CREATE INDEX and drop index
INSERT	Using the Insert
LOCK TABLES	Using the lock TABLES
PROCESS	Use Show full processlist
RELOAD	Using Fflush
REPLICATION CLIENT	Access to server locations
REPLICATION SLAVE	Used by replication slaves
SELECT	Using Select
SHOW DATABASES	Using Show DATABASES
SHOW VIEW	Using the show CREATE VIEW
SHUTDOWN	Use Mysqladmin shutdown (to turn off MySQL)
SUPER	Use change master, KILL, LOGS, PURGE, Master, and set GLOBAL. Also allows mysqladmin debug login
UPDATE	Using the update
USAGE	No access rights

[SQL]View Plaincopyprint?

Change Password

SET PASSWORD for user_name = PASSWORD (' New_password ');

or set PASSWORD = PASSWORD (' New_password '); This is changing your password.

Grant and revoke can control access at several levels:

Entire server, using grant all and revoke all;

The entire database, using on database.*;

Specific tables, using on database.table;

Specific columns, do not know

Specific stored procedures, do not know

Add one point,

When we enter MySQL directly under the Linux system, we log in as a local anonymous account, which is "@localhost

This account is not authorized at the beginning of the usage of *. *, so many times the operation is unsuccessful.

If we just study, we can first login with root mysql-u root-p <CR> then enter the root password (or no password, no-p)

Then grant all on * * for ' @localhost;

So you can.