MySQL permission and root password reset

Post by **cah** » Mon Nov 25, 2013 7:46 pm

Manzar and Ken came to me today and asked me to import iportal sql file.

I created a new schema named iportal and import the sql file by the following command:

Code: Select all

mysql -u mysql-p iportal < /data/home/hsiaoc1/iportal_db.dump.sql

It had errors during the attempts.

Code: Select all

ERROR 1449 (HY000) at line 5657: There is no 'admin'@'localhost' registered

So, I created a new user admin/4dm1n for it.
NOTICE: mysql user is like root user and has all privileges.

Code: Select all

mysql> show grants;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for mysql@localhost                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'localhost' IDENTIFIED BY PASSWORD '*0B664ADC22F4FD1B3CA8578995A5E1446F5C6AB9' | 
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

However, the import process got stuck:

Code: Select all

ERROR 1356 (HY000) at line 5657: View 'test.activity_last_fax' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

I was trying to grant admin user all privileges to iportal.* but it kept complaining access denied.

Code: Select all

mysql> GRANT ALL PRIVILEGES ON iportal.* TO 'admin'@'localhost' WITH GRANT OPTION;
ERROR 1044 (42000): Access denied for user 'mysql'@'localhost' to database 'iportal'

It looks like I had to use root user to grant privileges. However, I forgot the root's password so I had to reset its password. Good thing is, mysql user has all privileges and I was able to reset root user's password.

Code: Select all

mysql> UPDATE mysql.user SET Password=PASSWORD('mysql4dm1n') where user='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 5  Changed: 0  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

After resetting root's password, I was able to log in as root and grant privileges to admin user on iportal.*.

Code: Select all

mysql> GRANT ALL PRIVILEGES ON iportal.* TO 'admin'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON iportal.* TO 'admin'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for admin
    -> ;
+------------------------------------------------------------------------------------------------------+
| Grants for admin@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*68AB655AF1DDBDB3179671D16EB5B698564AC722' | 
| GRANT ALL PRIVILEGES ON `iportal`.* TO 'admin'@'%' WITH GRANT OPTION                                 | 
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for admin@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for admin@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*68AB655AF1DDBDB3179671D16EB5B698564AC722' | 
| GRANT ALL PRIVILEGES ON `iportal`.* TO 'admin'@'localhost' WITH GRANT OPTION                                 | 
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

I then tried to import the sql again but this time it stopped at 5807 (total is 5850) with the following error:

Code: Select all

ERROR 1146 (42S02) at line 5807: Table 'callcenter.sample_product_outbound_flows_view' doesn't exist

It looks like it is trying to import into callcenter schema that did not exist.
So I created another schema named "callcenter" and but it stopped at the same spot again.

I then realized that I was still trying to import into iportal schema. I changed to callcenter schema and tried the import again. It complained again:

Code: Select all

%mysql -u root -p callcenter < /data/home/hsiaoc1/iportal_db.dump.sql
Enter password: 
ERROR 1356 (HY000) at line 5657: View 'callcenter.activity_last_fax' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

I had to grant privilege to callcenter.* for admin user.

Code: Select all

mysql> GRANT ALL PRIVILEGES ON callcenter.* TO 'admin'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON callcenter.* TO 'admin'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for admin
    -> ;
+------------------------------------------------------------------------------------------------------+
| Grants for admin@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*68AB655AF1DDBDB3179671D16EB5B698564AC722' | 
| GRANT ALL PRIVILEGES ON `iportal`.* TO 'admin'@'%' WITH GRANT OPTION                                 | 
| GRANT ALL PRIVILEGES ON `callcenter`.* TO 'admin'@'%' WITH GRANT OPTION                              | 
+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show grants for admin@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for admin@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*68AB655AF1DDBDB3179671D16EB5B698564AC722' | 
| GRANT ALL PRIVILEGES ON `callcenter`.* TO 'admin'@'localhost' WITH GRANT OPTION                              | 
| GRANT ALL PRIVILEGES ON `iportal`.* TO 'admin'@'localhost' WITH GRANT OPTION                                 | 
+--------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

I then tried again and this time, it complained about another new schema:

Code: Select all

ERROR 1146 (42S02) at line 5807: Table 'inventory.movement_view' doesn't exist

I then created a new schema "inventory" and granted necessary privileges to admin user:

Code: Select all

mysql> GRANT ALL PRIVILEGES ON inventory.* TO 'admin'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
 
mysql> GRANT ALL PRIVILEGES ON inventory.* TO 'admin'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for admin@localhost;                                         
+--------------------------------------------------------------------------------------------------------------+
| Grants for admin@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*68AB655AF1DDBDB3179671D16EB5B698564AC722' | 
| GRANT ALL PRIVILEGES ON `inventory`.* TO 'admin'@'localhost' WITH GRANT OPTION                               | 
| GRANT ALL PRIVILEGES ON `iportal`.* TO 'admin'@'localhost' WITH GRANT OPTION                                 | 
| GRANT ALL PRIVILEGES ON `callcenter`.* TO 'admin'@'localhost' WITH GRANT OPTION                              | 
+--------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> show grants for admin;
+------------------------------------------------------------------------------------------------------+
| Grants for admin@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*68AB655AF1DDBDB3179671D16EB5B698564AC722' | 
| GRANT ALL PRIVILEGES ON `iportal`.* TO 'admin'@'%' WITH GRANT OPTION                                 | 
| GRANT ALL PRIVILEGES ON `callcenter`.* TO 'admin'@'%' WITH GRANT OPTION                              | 
| GRANT ALL PRIVILEGES ON `inventory`.* TO 'admin'@'%' WITH GRANT OPTION                               | 
+------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)