Dhileepan
Dhileepan

Reputation: 2047

Read only table in mysql

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

Answers (5)

Mr.Wang from Next Door
Mr.Wang from Next Door

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

rogal111
rogal111

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

JaMaBing
JaMaBing

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

Jagmag
Jagmag

Reputation: 10356

Use the REVOKEcommand 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

Mayank Pathak
Mayank Pathak

Reputation: 3681

GRANT SELECT ON tbl_user TO user;
GRANT SELECT, INSERT, UPDATE, DELETE ON tbl_user TO user;

Upvotes: 2

Related Questions