Reputation: 2047
I'm using mysql database. In that I have table called tbl_user
I need to change this as read only table to every user. How to change the table as read only ?
Upvotes: 7
Views: 29445
Reputation: 14780
Besides revoking the permission, another way is to take use of trigger
/* triggers */
delimiter //
DROP TRIGGER IF EXISTS stop_table_insert;
CREATE TRIGGER stop_table_insert
BEFORE INSERT ON `table`
FOR EACH ROW
BEGIN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Database maintainance';
END;//
DROP TRIGGER IF EXISTS stop_table_update;
CREATE TRIGGER stop_table_update
BEFORE UPDATE ON `table`
FOR EACH ROW
BEGIN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Database maintainance';
END;//
DROP TRIGGER IF EXISTS stop_table_delete;
CREATE TRIGGER stop_table_delete
BEFORE DELETE ON `table`
FOR EACH ROW
BEGIN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Database maintainance';
END;//
delimiter ;
Upvotes: 5
Reputation: 5933
You need REVOKE update/modify/GRANT privileges to this table for all users and add SELECT.
REVOKE ALL ON readonly_table FROM tbl_user ;
GRANT SELECT ON readonly_table TO tbl_user;
If user have grant option on readonly_table execute this:
REVOKE GRANT OPTION ON readonly_table FROM tbl_user;
Read about The MySQL Access Privilege System and REVOKE documentation
Upvotes: 3
Reputation: 1041
To grant all user select, use public instead of a complete user list
REVOKE ALL ON tbl_user FROM PUBLIC
GRANT SELECT ON tbl_user TO PUBLIC
Upvotes: 7
Reputation: 10356
Use the REVOKE
command and revoke INSERT / UPDATE / DELETE rights from that table for the user
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
Refer Documentation
Upvotes: 3
Reputation: 3681
GRANT SELECT ON tbl_user TO user;
GRANT SELECT, INSERT, UPDATE, DELETE ON tbl_user TO user;
Upvotes: 2