1. grant all on db_name.* to user_name@’localhost’ identified by ‘password’; ——This also sets the password in the same step.
2. flush privileges; ——Don’t forget to flush after grant; it reloads the grant tables.
For some other MySQL administration tasks, the mysqladmin command can be used.
The examples in this article run on MySQL 5.0 and above. The simple format of the MySQL user privilege command can be summarized as:
In MySQL, you can grant a user one or more privileges like select, insert, update, delete, etc., primarily using the grant command. The usage format is:
grant privileges on database_object to user
1. Grant a regular data user the rights to query, insert, update, and delete data in all tables of a 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@’%’
Or, replace it with a single MySQL command:
grant select, insert, update, delete on testdb.* to common_user@’%’
2. Grant a database developer the privileges to create tables, indexes, views, stored procedures, functions, etc.
Grant the privileges to create, modify, and delete MySQL table structures.
grant create on testdb.* to developer@’192.168.0.%’;
grant alter on testdb.* to developer@’192.168.0.%’;
grant drop on testdb.* to developer@’192.168.0.%’;
Grant the privilege to operate MySQL foreign keys.
grant references on testdb.* to developer@’192.168.0.%’;
Grant the privilege to operate MySQL temporary tables.
grant create temporary tables on testdb.* to developer@’192.168.0.%’;
Grant the privilege to operate MySQL indexes.
grant index on testdb.* to developer@’192.168.0.%’;
Grant the privileges to operate MySQL views and view source code.
grant create view on testdb.* to developer@’192.168.0.%’;
grant show view on testdb.* to developer@’192.168.0.%’;
Grant the privileges to operate MySQL stored procedures and functions.
grant create routine on testdb.* to developer@’192.168.0.%’; — now, can show procedure status
grant alter routine on testdb.* to developer@’192.168.0.%’; — now, you can drop a procedure
grant execute on testdb.* to developer@’192.168.0.%’;
3. Grant a regular DBA the privileges to manage a specific MySQL database.
grant all privileges on testdb to dba@’localhost’
Among them, the keyword “privileges” can be omitted.
4. Grant a senior DBA the privileges to manage all databases in MySQL.
grant all on *.* to dba@’localhost’
5. MySQL grant privileges can be applied at multiple levels.
1. Grant acting on the entire MySQL server:
grant select on *.* to dba@localhost; — dba can query tables in all databases in MySQL.
grant all on *.* to dba@localhost; — dba can manage all databases in MySQL.
2. Grant acting on a single database:
grant select on testdb.* to dba@localhost; — dba can query tables in testdb.
3. Grant acting on a single data table:
grant select, insert, update, delete on testdb.orders to dba@localhost;
4. Grant acting on columns in a table:
grant select(id, se, rank) on testdb.apache_log to dba@localhost;
5. Grant acting on stored procedures and functions:
grant execute on procedure testdb.pr_add to ‘dba’@'localhost’
grant execute on function testdb.fn_add to ‘dba’@'localhost’
6. Viewing MySQL User Privileges
View current user (own) privileges:
show grants;
View other MySQL user privileges:
show grants for dba@localhost;
7. Revoking privileges granted to MySQL users.
Revoke has a syntax similar to grant, only needing to replace the keyword “to” with “from”:
grant all on *.* to dba@localhost;
revoke all on *.* from dba@localhost;
8. Notes on MySQL grant and revoke user privileges
1. After granting or revoking user privileges, the changes only take effect when the user reconnects to the MySQL database.
2. If you want the authorized user to also be able to grant these privileges to other users, you need the option “grant option“
grant select on testdb.* to dba@localhost with grant option;
This feature is generally seldom used. In practice, database permissions are best managed uniformly by a DBA.
=========================================================================
1. Creating Users and Granting Privileges
Grant statement syntax:
grant privileges (columns) on what to user identified by “password” with grant option
To use this pattern, you need to determine the fields:
privileges List of privilege specifiers and the operations they allow
alter Modify tables and indexes
create Create databases and tables
delete Delete existing records from tables
drop Drop (delete) databases and tables
index Create or drop indexes
insert Insert new rows into tables
reference Unused
select Retrieve records from tables
update Modify existing table records
file Read or write files on the server
process View thread information executing in the server or kill threads
reload Reload grant tables or flush logs, host cache, or table cache.
shutdown Shut down the server
all All; synonym for all privileges
usage Special “no privileges” privilege
The above privileges are divided into three groups:
Group 1: Applies to databases, tables, and columns: alter create delete drop index insert select update
Group 2: Administrative privileges; they allow users to affect server operation and must be authorized strictly: file process reload shut*
Group 3: Privilege specials; all means “all privileges”, usage means no privilege, i.e., create a user without granting privileges.
columns
The columns the privilege applies to (optional) and you can only set column-specific privileges. If the command includes more than one column, separate them with commas.
what
The level at which privileges apply. Privileges can be global, database-specific, or table-specific.
user
The user to whom privileges are granted, consisting of a user name and a host name, allowing two users with the same name to connect from different places. Default: mysql user password
The password given to the user (optional); if you do not specify an identified by clause for the user, their password remains unchanged.
When using identified by, the password string should be written as a literal; grant will encode the password for you.
Note: set password uses the password() function
with grant option The user can grant privileges to other users through the grant statement (optional).
Example Walkthrough:
grant all on db_webarch.* to admin@’localhost’ identified by “passwd” Can only connect locally
grant all on db_webarch.* to admin@’webarch.org’ identified by “admin” Allows connection from this domain
grant all on db_webarch.* to admin@% identified by “admin” Allows connection from any host
Note: The ”%” character acts as a wildcard, matching the same meaning as the like pattern match.
grant all on db_webarch.* to admin@%.webarch.org identified by “password”;
Allows admin to connect from any host in the webarch.org domain
grant all on db_webarch.* to [email protected] identified by “password”
grant all on db_webarch.* to [email protected].% identified by “password”
grant all on db_webarch.* to [email protected]/17 identified by “password” Allows login from a single IP, IP range, or a subnet IP
Note: Sometimes the user@IP part needs quotes like ”[email protected]/17″
grant all on *.* to admin@localhost identified by “password” with grant option
Add a superuser admin who can perform any operation when logged in locally.
grant reload on *.* to admin@localhost identified by “password” Only grant the reload privilege
grant all on db_webarch to [email protected] indetified by “password” All privileges
grant select on db_webarch to admin@% indetified by “password” Read-only privilege
grant select,insert,delete,update on db_webarch to [email protected] indetified by “password”
Only select, insert, delete, update privileges
grant select on db_webarch.blog to admin@localhost indetified by “password” Only for table
grant update (name) on db_webarch.blog to admin@localhost Only for the name column of the table, password unchanged
grant update (id,name,author) on db_webarch.blog to admin@localhost Only for multiple columns of the table
grant all on book.* to “”@webarch.org Allows all users in the webarch.org domain to use the book database
grant all on book.* to admin@%.webarch.org indetified by “password” with grant option
Allows admin to manage and authorize all tables in the book database.
2. Revoking Privileges and Deleting Users
The syntax of revoke is similar to the grant statement
to is replaced by from, and there is no indetifed by or with grant option clause. As follows:
revoke privileges (columns) on what from user
user: Must match the user part of the original grant statement for the user whose privileges you want to revoke.
privileges: Do not need to match; you can authorize with a grant statement and then use a revoke statement to revoke only part of the privileges.
The revoke statement only deletes privileges, not the user. After revoking all privileges, the user record in the user table remains, and the user can still connect to the server.
To completely delete a user, you must explicitly delete the user record from the user table using a delete statement:
delete from user where user=”admin”
flush privileges; Reload the grant tables
Note: When using the grant and revoke statements, the tables are automatically reloaded, but this is not the case when you directly modify the grant tables.