I want to prevent few tables from accidental deletion?
Posted: Sat Jan 14, 2006 7:53 am
Is there a way to prevent a few important tables from accidental deletion?
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
Revoke DROP permissions on that table for the user who will be accessing the database.raghavan20 wrote:Is there a way to prevent a few important tables from accidental deletion?
Code: Select all
mysql> revoke drop
-> on forum_db.*
-> from root@localhost;
ERROR 1141 (42000): There is no such grant defined for user 'root' on host 'localhost'Code: Select all
mysql> select user, host from mysql.user;
+-----------------+-----------+
| user | host |
+-----------------+-----------+
| dummy@localhost | % |
| root | % |
| francis | localhost |
| raghavan | localhost |
| root | localhost |
| visitor | localhost |
+-----------------+-----------+
6 rows in set (0.11 sec)
but when I try this, I get the same error...
mysql> revoke DROP
-> on forum_db.*
-> from visitor@"localhost";
ERROR 1141 (42000): There is no such grant defined for user 'visitor' on host 'localhost'Code: Select all
mysql> grant
-> grant option
-> on
-> *.*
-> to
-> visitor@localhost;
Query OK, 0 rows affected (0.09 sec)
mysql> show grants for visitor@localhost;
+----------------------------------------------------------------------------------------
---------------------------------------------------+
| Grants for visitor@localhost
|
+----------------------------------------------------------------------------------------
---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'visitor'@'localhost' IDENTIFIED BY PASSWORD '*BE9CCED92
6640666CC59F47E0B9D3A98A2CB94C4' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------
---------------------------------------------------+
1 row in set (0.00 sec)
mysql> quit
Bye
C:\>mysql -u visitor -p
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 507 to server version: 4.1.11-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show grants for root@localhost;
+----------------------------------------------------------------------------------------
------------------------------------------------+
| Grants for root@localhost
|
+----------------------------------------------------------------------------------------
------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*9BC01A49104D
6960C8C288548AD36884E0B580D8' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------
------------------------------------------------+
1 row in set (0.00 sec)
mysql> revoke drop
-> on
-> *.*
-> from
-> root@localhost;
Query OK, 0 rows affected (0.05 sec)
mysql> show grants for root@localhost;
+----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
--------------------------------------------------------------+
| Grants for root@localhost
|
+----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
--------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENC
ES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, R
EPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD
'*9BC01A49104D6960C8C288548AD36884E0B580D8' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> bye
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that correspon
ds to your MySQL server version for the right syntax to use near 'bye' at line 1
mysql> quit;
Bye
C:\>mysql -u root -p
Enter password: ************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 508 to server version: 4.1.11-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use forum_db;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_forum_db |
+--------------------+
| forums_tbl |
| loginrecords_tbl |
| members_tbl |
| replies_tbl |
| temp_table |
| topics_tbl |
+--------------------+
6 rows in set (0.08 sec)
mysql> drop table temp_table;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'forum_db'
mysql> grant drop
-> on
-> *.*
-> to
-> root@localhost;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysql> show grants for root@localhost;
+----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
--------------------------------------------------------------+
| Grants for root@localhost
|
+----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
--------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENC
ES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, R
EPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD
'*9BC01A49104D6960C8C288548AD36884E0B580D8' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> quit
Bye
C:\>mysql -u visitor -p
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 509 to server version: 4.1.11-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> grant drop
-> on
-> *.*
-> to
-> root@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for root@localhost;
+----------------------------------------------------------------------------------------
------------------------------------------------+
| Grants for root@localhost
|
+----------------------------------------------------------------------------------------
------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*9BC01A49104D
6960C8C288548AD36884E0B580D8' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------
------------------------------------------------+
1 row in set (0.00 sec)