MySQL learning-user creation authorization, mysql-user authorization

MySQL learning-user creation authorization, mysql-user authorization

Some time ago, the project was changed to the SAAS architecture. Each billing account can obtain a subdomain, a separate database, a separate database user, and the permissions of the corresponding database.

Now we have time to back up database-related commands using a blog. Some of them have not been tried. Please correct them if any errors exist.

1. Create a user

Create user 'username' @ 'host' identity by 'Password ';

Insert into mysql. user (Host, User, Password) values ("host", "username", password ("password "));

If you want the specified user to only access from a specified domain or host, you can specify the host when creating the user, for example, 10.10.10.22. If you want to log on from a local machine, you can set the host to localhost. If you want to connect the host on multiple machines, you can set the host to %.

2. Create and view Databases

Show databases; // display the database

Create database dbname; // create a database

Use dbname; // enter the database

Show tables; // display table

Desc tablename; // displays the table structure.

Source SQL/file/path; import SQL files

3. user authorization

Grant all privileges on dbname. * to username @ '%' identified by 'Password'; // authorize the username user to have all permissions for the dbname Database

Grant select, update on dbname. * to username @ '%' identified by 'Password'; // authorize the username user to have specified permissions for the dbname Database

4. Refresh authorization

Flush privileges;

5. delete users and revoke permissions

Drop user username @ host; // cancel an account and its permissions

Revoke privilege on dbname. tablename FROM 'username' @ 'host'; // cancel the authorized user

Delete from user where user = "username" and host = "host"; // delete a user

6. Change the User Password

Mysqladmin-uroot-proot password 123; // change the password of the root user to 123

Update mysql. user set password = password ('new password') where user = "username" and host = "localhost ";

Set password for 'username' @ 'host' = password ('newpassword ');

7. delete a database

Drop database dbname; // delete a database that already exists

Alter table name ENGINE = storage ENGINE name; // modify the storage ENGINE of the table

Alter table Name drop attribute name; // delete a field

Alter table old table name rename to new table name; // Modify table Name

Alter table name modify attribute name data type; // modify Field Data Type

Alter table name change old property name new data type; // Modify Field name

Alter table Name drop foreing key foreign KEY alias; // Delete the foreign KEY constraint of the sub-table

Alter table example add phone VARCHAR (20); // add a unrestricted Field

Alter table example add age INT (4) not null; // add a constrained Field

Alter table example add num INT (8) primary key first; // add a field to the FIRST position of the table

Alter table example add address VARCHAR (30) not null after phone; // add a field AFTER the specified position of the table

Alter table example modify name VARCHAR (20) FIRST; // modify the field to the FIRST place.

Alter table example modify num INT (8) ATER phone; // After modifying a field to a specified field

 

How does MySQL authorize a self-created user such as daitest to create a new database? Command

Take a look
In mysql, you can grant one or more permissions to a user, such as select, insert, update, and delete. The grant command is used in the following format:
Grant permission on database objects to users
1. grant normal data users the right to query, insert, update, and delete all table data in the database.
Grant select on testdb. * to common_user @ '%'
Grant insert on testdb. * to common_user @ '%'
Grant update on testdb. * to common_user @ '%'
Grant delete on testdb. * to common_user @ '%'
Alternatively, replace the following with a mysql command:
Grant select, insert, update, delete on testdb. * to common_user @ '%'

2. grant database developers to create tables, indexes, views, stored procedures, and functions... .
Grant permissions to create, modify, and delete mysql Data Table structures.
Grant create on testdb. * to developer @ '192. 192.% ';
Grant alter on testdb. * to developer @ '192. 192.% ';
Grant drop on testdb. * to developer @ '192. 192.% ';
Grant the mysql foreign key operation permission.
Grant references on testdb. * to developer @ '192. 192.% ';
Grant the permission to operate mysql temporary tables.
Grant create temporary tables on testdb. * to developer @ '2017. 192.% ';
Grant the permission to operate mysql indexes.
Grant index on testdb. * to developer @ '192. 192.% ';
Grant permissions to operate the mysql view and view the source code.
Grant create view on testdb. * to developer @ '192. 192.% ';
Grant show view on testdb. * to developer @ '192. 192.% ';
Grant permissions to operate mysql stored procedures and functions.
Grant create routine on testdb. * to developer @ '192. 192.% ';-now, can show procedure status
Grant alter routine on testdb. * to developer @ '192. 192.% ';-now, you can drop a procedure
Grant execute on testdb. * to developer @ '192. 192.% ';

3. grant common dba permission to manage a mysql database.
Grant all privileges on testdb to dba @ 'localhost'
The keyword "privileges" can be omitted.

4. grant senior dba permission to manage all databases in mysql.
Grant... the remaining full text>

How many ip addresses can be authorized to an account in mysql?

Use the following authorization statement to authorize the specified machines 192.168.1.1 and 192.168.1.2 to access
Grant all privileges on *. * to 'root' @ '192. 168.1.1 'identified by 'test123 ';
Grant all privileges on *. * to 'root' @ '192. 168.1.2 'identified by 'test123 ';