Not Allowing Certain Tables While Backup

[s4surbhi2218]

Hey,

I want to not allow certain tables while anyone takes a backup of my mysql db .How can that be done?

Any suggestions?

[NomadicJosh]

There's an --ignore-table option

 

mysqldump -u username -p database --ignore-table=database.table1 --ignore-table=database.table2 > database.sql

[s4surbhi2218]

There's an --ignore-table option

 

mysqldump -u username -p database --ignore-table=database.table1 --ignore-table=database.table2 > database.sql

 

IS this the only way , i mean is there any other option through which i can pre set my tables to ignored category before anyone takes a complete backup ?

[jazzman1]

There is a where clause.

[s4surbhi2218]

There is a where clause.

 

how to use that for this purpose?

[jazzman1]

Example:

 

--where="name='john'"

[jazzman1]

If you are administering MySQL, you could create a group of users with very specific permission.

[fenway]

Who is this "anybody" who has complete access to your database that you're trying to control?

Edited December 1, 2012 by fenway

[s4surbhi2218]

If you are administering MySQL, you could create a group of users with very specific permission.

 

Yes this is what i need , could you please give an example command .

Edited December 1, 2012 by s4surbhi2218

[s4surbhi2218]

Who is this "anybody" who has complete access to your database that you're trying to control?

 

A normal user , for whom i have not yet controlled any permission access.

[jazzman1]

First off, my apology if my English is not good for you.

 

1. Well, before getting into specifics of the GRANT statement, take a look at the statement that grants all rights on the database named test to user jazzman, jazzman's password is "password"

 

mysql > GRANT ALL ON test.* TO jazzman@localhost IDENTIFIED BY "password";

 

The first part of the GRANT statement can take the word - ALL. Most often you will be granting rights to use sql staments ( SELECT, CREATE, DELETE, DROP, INDEX, INSERT and UPDATE).

 

The second one (ON test.*) identifies where privileges will be applied.

Learn more about them at an address -> http://dev.mysql.com...iv_grant-option

 

The third one (TO jazzman@localhost) indicates the user to be given access. Note, that MySQL needs both a name and a host, separated by the "@" symbol.

 

The last one, IDENTIFIED BY gives the user a password.

 

Here is what the user and db tables would look like for the user - "jazzman"

 

Open a mysql console and go to the mysql table:

 

Example:

[jazzman@localhost ~]$ mysql --host=localhost --user=jazzman --password=password --port=3306

mysql> use mysql
Database changed

mysql> SELECT * FROM user WHERE user='jazzman' \G
*************************** 1. row ***************************
			 Host: localhost
			 User: jazzman
		 Password: *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19
	 Select_priv: Y
	 Insert_priv: Y
	 Update_priv: Y
	 Delete_priv: Y
	 Create_priv: Y
		Drop_priv: Y
	 Reload_priv: Y
	Shutdown_priv: Y
	 Process_priv: Y
		File_priv: Y
	 Grant_priv: Y
 References_priv: Y
	 Index_priv: Y
	 Alter_priv: Y
	 Show_db_priv: Y
	 Super_priv: Y
Create_tmp_table_priv: Y
 Lock_tables_priv: Y
	 Execute_priv: Y
 Repl_slave_priv: Y
 Repl_client_priv: Y
 Create_view_priv: Y
 Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
 Create_user_priv: Y
	 Event_priv: Y
	 Trigger_priv: Y
		 ssl_type:
	 ssl_cipher:
	 x509_issuer:
	 x509_subject:
	max_questions: 0
	 max_updates: 0
 max_connections: 0
max_user_connections: 0
1 row in set (0.00 sec)

mysql> SELECT * FROM db WHERE user='jazzman' \G
*************************** 1. row ***************************
			 Host: localhost
			 Db: test
			 User: jazzman
	 Select_priv: Y
	 Insert_priv: Y
	 Update_priv: Y
	 Delete_priv: Y
	 Create_priv: Y
		Drop_priv: Y
	 Grant_priv: N
 References_priv: Y
	 Index_priv: Y
	 Alter_priv: Y
Create_tmp_table_priv: Y
 Lock_tables_priv: Y
 Create_view_priv: Y
 Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
	 Execute_priv: Y
	 Event_priv: Y
	 Trigger_priv: Y
3 rows in set (0.00 sec)

 

 

As you can see from a log, I retrieve all information that I need to know about the user named - "jazzman".

For my test database, the permissions granted by the above statement may be ok if jazzman is the administrator of that application.

In this particular example jazzman have all rights on the database named - test.

What I want to do is creating a table in this database named - product.

After that I want to remove some of jazzman's privileges on this table, for this example - DELETE statement, to do this you could use REVOKE statement.

mysql > CREATE TABLE `product` (
`id` int(11) NOT NULL,
`name` varchar(40) DEFAULT NULL,
`purchase_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

mysql > INSERT INTO `test`.`product` VALUES (1,'Lawn Mower',1),(2,'Greenhouse',1),(3,'Paving slab',2),(4,'BBQ',2),(5,'Chainsaw',5),
(6,'Power-MP3 5gb',2),(7,'Super-Player 1gb',3),(8,'Porta CD',3),(9,'36\" TV',1),(10,'Family Talk 360',5);

RESULTS:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> SELECT * FROM test.product
-> ;
+----+------------------+-------------+
| id | name			 | purchase_id |
+----+------------------+-------------+
| 1 | Lawn Mower	 |		 1 |
| 2 | Greenhouse	 |		 1 |
| 3 | Paving slab	 |		 2 |
| 4 | BBQ			 |		 2 |
| 5 | Chainsaw		 |		 5 |
| 6 | Power-MP3 5gb	|	 2 |
| 7 | Super-Player 1gb |		 3 |
| 8 | Porta CD		 |		 3 |
| 9 | 36" TV		 |		 1 |
| 10 | Family Talk 360 |		 5 |
+----+------------------+-------------+
10 rows in set (0.00 sec)

 

Granting all rights on that table to jazzman I ran that code:

mysql> GRANT ALL ON test.product TO jazzman@localhost IDENTIFIED BY "password";
Query OK, 0 rows affected (0.02 sec)

 

To remove DELETE privileges from jazzman to this particular table you can run the following:

mysql> REVOKE DELETE ON test.product FROM jazzman@localhost;
Query OK, 0 rows affected (0.00 sec)

 

To see the exact grants available at a given time, all you to know is the username and host:

As you can see from the mysql log, there is no delete pivileges to jazzman.

 

mysql> SHOW GRANTS FOR jazzman@localhost

Results:

GRANT SELECT, INSERT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `test`.`product` TO 'jazzman'@'localhost'

 

You need to load the grant table to the MySQL memory to get effect into it.

 

mysql> FLUSH privileges;
Query OK, 0 rows affected (0.00 sec)

 

After all of that , you have to log off then log on from mysql console - CTRL + D

 

Now if I try to delete the username with an id 1 from a test.product table, I got denied access.

 

mysql> DELETE FROM test.product WHERE id=1;
ERROR 1142 (42000): DELETE command denied to user 'jazzman'@'localhost' for table 'product'

 

I think you could use this one as a sample.

 

You can find much more details about privileges at mysql.com website.

 

Good luck

Edited December 1, 2012 by jazzman1