Search code examples
mysqldatabaseoracle-databaseprivilegessql-grant

how to revoke update grant on one table in one schema(crm) and user(cus)

i have lots of table in one schema with given grant(select,update,insert,delete) And now i want to remove the update grant on the attendence table how can i do schema(crm) user(cus) table(attendence) DB (dev)

Solution

  • Connect as user who owns the ATTENDANCE table

    connect owner_username/its_password@database_name

    and then

    revoke update on attendance from some_user;

    [EDIT, after reading your comment]

    If there are many users in the database, consider creating roles. For example, roles RS1 and RS2. Then

    grant update, delete, select, insert on a1 to rs1;
grant select, insert on a1 to rs2;

    Then you'd grant those roles to appropriate users. The ones that can do everything would be granted RS1 role, while the others would be granted RS2, such as

    grant rs1 to s1;
grant rs2 to s2;
grant rs2 to s3;
grant rs1 to s1;

    Otherwise (i.e. if you don't want to use roles), you'd grant all those privileges directly to each user.