Jump to content


Photo

Not Allowing Certain Tables While Backup


  • Please log in to reply
10 replies to this topic

#1 s4surbhi2218

s4surbhi2218

    Advanced Member

  • Members
  • PipPipPip
  • 54 posts

Posted 30 November 2012 - 03:21 AM

Hey,
I want to not allow certain tables while anyone takes a backup of my mysql db .How can that be done?
Any suggestions?
Have a great 2013!!

#2 parkerj

parkerj

    Advanced Member

  • Members
  • PipPipPip
  • 167 posts
  • LocationBoston, MA

Posted 30 November 2012 - 07:41 AM

There's an --ignore-table option

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

eduTrac ERP | College Management System


#3 s4surbhi2218

s4surbhi2218

    Advanced Member

  • Members
  • PipPipPip
  • 54 posts

Posted 30 November 2012 - 07:48 AM

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 ?
Have a great 2013!!

#4 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,659 posts
  • LocationMississauga, Canada

Posted 30 November 2012 - 07:56 AM

There is a where clause.

#5 s4surbhi2218

s4surbhi2218

    Advanced Member

  • Members
  • PipPipPip
  • 54 posts

Posted 30 November 2012 - 07:59 AM

There is a where clause.


how to use that for this purpose?
Have a great 2013!!

#6 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,659 posts
  • LocationMississauga, Canada

Posted 30 November 2012 - 08:16 AM

Example:

--where="name='john'"


#7 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,659 posts
  • LocationMississauga, Canada

Posted 30 November 2012 - 08:36 AM

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

#8 fenway

fenway

    MySQL Si-Fu / PHP Resident Alien

  • Moderators
  • 16,195 posts
  • LocationToronto, ON

Posted 30 November 2012 - 09:19 PM

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

Edited by fenway, 30 November 2012 - 09:19 PM.

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 s4surbhi2218

s4surbhi2218

    Advanced Member

  • Members
  • PipPipPip
  • 54 posts

Posted 01 December 2012 - 12:27 AM

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 by s4surbhi2218, 01 December 2012 - 12:30 AM.

Have a great 2013!!

#10 s4surbhi2218

s4surbhi2218

    Advanced Member

  • Members
  • PipPipPip
  • 54 posts

Posted 01 December 2012 - 12:29 AM

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.
Have a great 2013!!

#11 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,659 posts
  • LocationMississauga, Canada

Posted 01 December 2012 - 12:04 PM

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 by jazzman1, 01 December 2012 - 12:12 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com