Page 1 of 1

I want to prevent few tables from accidental deletion?

Posted: Sat Jan 14, 2006 7:53 am
by raghavan20
Is there a way to prevent a few important tables from accidental deletion?

Re: I want to prevent few tables from accidental deletion?

Posted: Sat Jan 14, 2006 10:44 am
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.

Posted: Sat Jan 14, 2006 1:53 pm
by raghavan20
What if I want the root itself not to accidentally provide the drop command for few tables?

Posted: Sat Jan 14, 2006 1:59 pm
by Jenk
Stop using root.

Posted: Sat Jan 14, 2006 2:10 pm
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?

Posted: Sat Jan 14, 2006 9:09 pm
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.

Posted: Sat Jan 14, 2006 9:11 pm
by Jenk
revoking anything from root is a bad idea.. stop using root, use a different user id with the drop permission(s) revoked..

Posted: Sat Jan 14, 2006 9:32 pm
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..

Posted: Sun Jan 15, 2006 4:05 am
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?

Posted: Sun Jan 15, 2006 4:28 am
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)