Not Allowing Certain Tables While Backup
Posted 30 November 2012 - 03:21 AM
I want to not allow certain tables while anyone takes a backup of my mysql db .How can that be done?
Posted 30 November 2012 - 07:48 AM
There's an --ignore-table optionmysqldump -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 ?
Posted 30 November 2012 - 07:59 AM
There is a where clause.
how to use that for this purpose?
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.
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.
Posted 01 December 2012 - 12:04 PM
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:
[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.
Edited by jazzman1, 01 December 2012 - 12:12 PM.
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users