Skip to content

Make 'users' table read only

Jump to bottom
Jayanth Dungavath edited this page Oct 12, 2017 · 1 revision
  • Create new user with privileges same as the existing Rails DB User (jay in this case!):
mysql> create user 'root'@'localhost' identified by 'b94Ka9dV';
Query OK, 0 rows affected (0.01 sec)

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

mysql> GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'root'@'localhost';
+--------------------------------------------------------------------+
| Grants for root@localhost                                           |
+--------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
  • Exit from MySql and login with the new user created:
jayanths-mbp-8:support jay$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.7.19 Homebrew

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
  • Verify who you are:
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
  • Default privileges for the Rails DB User:
mysql> show grants for 'jay'@'localhost';
+--------------------------------------------------------------------+
| Grants for jay@localhost                                           |
+--------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'jay'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'jay'@'localhost' WITH GRANT OPTION        |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
  • Revoke ALL PRIVILEGES on all tables first:
mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'jay'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'jay'@'localhost';
+-------------------------------------------------------------+
| Grants for jay@localhost                                    |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jay'@'localhost' WITH GRANT OPTION   |
| GRANT PROXY ON ''@'' TO 'jay'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)
  • Execute following query to generate SQL Queries that GRANT ALL PRIVILEGES to all tables except 'users' table:
mysql> SELECT CONCAT("GRANT ALL PRIVILEGES ON `db/development.mysql2`.", table_name, " TO 'jay'@'localhost';") FROM information_schema.TABLES WHERE table_schema = "db/development.mysql2" AND table_name <> "users";
+---------------------------------------------------------------------------------------------------------+
| CONCAT("GRANT ALL PRIVILEGES ON `db/development.mysql2`.", table_name, " TO 'jay'@'localhost';")        |
+---------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.bookmarks TO 'jay'@'localhost';                         |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.change_manager_changes TO 'jay'@'localhost';            |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.checksum_audit_logs TO 'jay'@'localhost';               |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.conversations TO 'jay'@'localhost';                     |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.devise_multi_auth_authentications TO 'jay'@'localhost'; |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.domain_terms TO 'jay'@'localhost';                      |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.domain_terms_local_authorities TO 'jay'@'localhost';    |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.feed_entries TO 'jay'@'localhost';                      |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.follows TO 'jay'@'localhost';                           |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.help_requests TO 'jay'@'localhost';                     |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.local_authorities TO 'jay'@'localhost';                 |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.local_authority_entries TO 'jay'@'localhost';           |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.notifications TO 'jay'@'localhost';                     |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.orcid_profile_requests TO 'jay'@'localhost';            |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.proxy_deposit_rights TO 'jay'@'localhost';              |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.receipts TO 'jay'@'localhost';                          |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.schema_migrations TO 'jay'@'localhost';                 |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.searches TO 'jay'@'localhost';                          |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.single_use_links TO 'jay'@'localhost';                  |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.subject_local_authority_entries TO 'jay'@'localhost';   |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.trophies TO 'jay'@'localhost';                          |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.version_committers TO 'jay'@'localhost';                |
+---------------------------------------------------------------------------------------------------------+
22 rows in set (0.06 sec)
  • Revoke ALL Privileges on all tables from Rails DB User:
mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'jay'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'jay'@'localhost';
+-------------------------------------------------------------+
| Grants for jay@localhost                                    |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jay'@'localhost' WITH GRANT OPTION   |
| GRANT PROXY ON ''@'' TO 'jay'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)
  • Run the result of SELECT CONCAT command above to grant ALL PRIVILEGES to all tables except 'users':
mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.bookmarks TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.change_manager_changes TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.checksum_audit_logs TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.conversations TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.devise_multi_auth_authentications TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.domain_terms TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.domain_terms_local_authorities TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.feed_entries TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.follows TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.help_requests TO 'jay'@'localhost';
GRANT ALL PRIVILEGES ON `db/development.mysql2`.local_authorities TO 'jay'@'localhost';
GRANT ALL PRIVILEGES ON `db/development.mysql2`.local_authority_entries TO 'jay'@'localhost';
GRANT ALL PRIVILEGES ON `db/development.mysql2`.notifications TO 'jay'@'localhost';
GRANT ALL PRIVILEGES ON `db/development.mysql2`.orcid_profile_requests TO 'jay'@'localhost';
GRANT ALL PRIVILEGES ON `db/development.mysql2`.proxy_deposit_rights TO 'jay'@'localhost';
GRANT ALL PRIVILEGES ON `db/development.mysql2`.receipts TO 'jay'@'localhost';
GRANT ALL PRIVILEGES ON `db/development.mysql2`.schema_migrations TO 'jay'@'localhost';
GRANT ALL PRIVILEGES ON `db/development.mysql2`.searches TO 'jay'@'localhost';
GRANT ALL PRIVILEGES ON `db/development.mysql2`.single_use_links TO 'jay'@'localhost';
GRANT ALL PRIVILEGES ON `db/development.mysql2`.subject_local_authority_Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.local_authorities TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.local_authority_entries TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.notifications TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.orcid_profile_requests TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.proxy_deposit_rights TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.receipts TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.schema_migrations TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.searches TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.single_use_links TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.subject_local_authority_entries TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.trophies TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `db/development.mysql2`.version_committers TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'jay'@'localhost';
+----------------------------------------------------------------------------------------------------------+
| Grants for jay@localhost                                                                                 |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jay'@'localhost' WITH GRANT OPTION                                                |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`conversations` TO 'jay'@'localhost'                     |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`proxy_deposit_rights` TO 'jay'@'localhost'              |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`notifications` TO 'jay'@'localhost'                     |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`checksum_audit_logs` TO 'jay'@'localhost'               |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`searches` TO 'jay'@'localhost'                          |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`subject_local_authority_entries` TO 'jay'@'localhost'   |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`orcid_profile_requests` TO 'jay'@'localhost'            |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`bookmarks` TO 'jay'@'localhost'                         |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`receipts` TO 'jay'@'localhost'                          |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`devise_multi_auth_authentications` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`version_committers` TO 'jay'@'localhost'                |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`local_authorities` TO 'jay'@'localhost'                 |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`single_use_links` TO 'jay'@'localhost'                  |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`local_authority_entries` TO 'jay'@'localhost'           |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`domain_terms` TO 'jay'@'localhost'                      |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`feed_entries` TO 'jay'@'localhost'                      |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`help_requests` TO 'jay'@'localhost'                     |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`domain_terms_local_authorities` TO 'jay'@'localhost'    |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`schema_migrations` TO 'jay'@'localhost'                 |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`trophies` TO 'jay'@'localhost'                          |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`follows` TO 'jay'@'localhost'                           |
| GRANT ALL PRIVILEGES ON `db/development.mysql2`.`change_manager_changes` TO 'jay'@'localhost'            |
| GRANT PROXY ON ''@'' TO 'jay'@'localhost' WITH GRANT OPTION                                              |
+----------------------------------------------------------------------------------------------------------+
24 rows in set (0.00 sec)
  • Grant SELECT PRIVILEGES to 'users' table:
mysql> GRANT SELECT ON `db/development.mysql2`.`users` TO 'jay'@'localhost';
Query OK, 0 rows affected (0.00 sec)
  • Resulting PRIVILEGES for Rails DB User:

Scholar 2.X docs

Clone this wiki locally