Privileges, User Lists, Password Change Script for MySQL

Show privileges In MySQL :-

mysql> SHOW GRANTS FOR ‘root’@’localhost’;
+———————————————————————+
| Grants for root@localhost |
+———————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ WITH GRANT OPTION |
+———————————————————————+

To list the privileges granted to the account that you are using to connect to the server, you can use any of the following statements:

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

To get more knowledge and details on privileges->

mysql> SHOW PRIVILEGES\G

List of MYSQL users and their privileges –>

mysql> select user,host from mysql.user;
+——————+————–+
| user | host |
+——————+————–+
| root | % |
| root | 127.0.0.1 |
| debian-sys-maint | localhost |
| root | localhost |
| root | stack.domain |
+——————+————–+
8 rows in set (0.01 sec)
The above output shows a list of existing MySQL accounts. Note that a MySQL account has two components: user and host. This allows the same user to use different MySQL accounts depending on which host they are connecting from. “%” is a wildcard character interpreted as “any” host.

To find the privilege(s) granted to a particular MySQL account:

mysql> show grants for ‘root’@’%’;
+————————————————+
| Grants for root@% |
+————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ …. |
+————————————————+
1 row in set (0.00 sec)

To change the password of a given account:

SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘Test@1234’);

Create User in MySQL:
CREATE USER ‘test_user’@’localhost’ IDENTIFIED BY ‘XXXX’;

Grant Privileges to user:
grant select on *.* to ‘new_user’@’%’ identified by ‘xxxxx’ with grant option;

After granting privileges, use the below command to bring that in effect-
mysql> flush privileges;

Advertisements

Author: sqlserverposts

Database Consultant by profession. I am a geek and a avid sports lover too. "Progress informed by the past, inspired by future".

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s