0

So, let's say I have 3 tables:

  1. stocks table, it have columns: id, product_id, stock
  2. sell table, it have columns: id, product_id, qty
  3. buy table, it have columns: id, product_id, qty

What I want to do:

  • Prevent update stocks.stock value directly.
  • But allow update stocks.stock value by trigger from buy or sell table.

Is this possible? If it's possible, how? If its impossible, why?

2 Answers 2

0

create trigger has a definer = {user} option for this.

You need to ensure that this defined user has update access to the stocks.stock however the other users only have select table access.

GRANT can control access at a table level.

0

Write Stored Procedure(s) to do buy/sell. Define it with SECURITY DEFINER. Install the proc as root so that it will have access.

Grant EXECUTE to the users so they can run the proc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.