Was able to work out solution via helpful user in dbt slack channel:

• Create new database role (this role cannot have any future grants in order to work with a share)
• GRANT DATABASE ROLE <name> TO SHARE <share_name> (need to run this only once)
• Then in the on run end hook use this instead: - "{% if target.name | lower == 'prod' %} grant select on table <database.schema.table> to database role <name>; {% endif %}"

So you grant select privilege on desired table to the database role (which is already granted to the share) instead of granting the privilege directly to the share. I still do not know why the original method does not work though.