I want to prevent few tables from accidental deletion?
Moderator: General Moderators
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
I want to prevent few tables from accidental deletion?
Is there a way to prevent a few important tables from accidental deletion?
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Re: I want to prevent few tables from accidental deletion?
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?
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
What if I want the root itself not to accidentally provide the drop command for few tables?
Last edited by raghavan20 on Sat Jan 14, 2006 2:08 pm, edited 2 times in total.
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
I tried something like this but does not seem to work...
I created an user then, a visitor with a password
the usage of "forum_db.*" in the statement is wrong?
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'- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
I am not really using the root account for a mysql application to work. I am jus' experimenting and trying to learn what can be done.
Let's say if root is capable of assigning permissions to itself and others then if it revokes drop ability for itself then at any time it should be again be able to grant the drop ability to itself.
Do locks only deal with read and write?
Let's say if root is capable of assigning permissions to itself and others then if it revokes drop ability for itself then at any time it should be again be able to grant the drop ability to itself.
Do locks only deal with read and write?
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
I tried to remove drop from root, and after this the root was not able to drop any table. The root is now revoked of granting itself drop permission so it gave an error when I tried to grant drop permission again. Then I had to again enable the drop grant from another visitor account which had all privileges.
Here is the interesting output:
Here is the interesting output:
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)