I want to prevent few tables from accidental deletion?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
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?

Post by raghavan20 »

Is there a way to prevent a few important tables from accidental deletion?
User avatar
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?

Post by Chris Corbyn »

raghavan20 wrote:Is there a way to prevent a few important tables from accidental deletion?
Revoke DROP permissions on that table for the user who will be accessing the database.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

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.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Stop using root.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

I tried something like this but does not seem to work...

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'
I created an user then, a visitor with a password

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'
the usage of "forum_db.*" in the statement is wrong?
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

You have to define all the grants individually. GRANT ALL will not work if you revoke specifics. Probably best to simply create you users and not GRANT them DROP permissions on te relevant tables.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

revoking anything from root is a bad idea.. stop using root, use a different user id with the drop permission(s) revoked..
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Think about it, if you dropped ROOT's ability to delete tables, you would no longer ever be able to delete tables..
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

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?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

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:

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)
Post Reply