let's say if I grant the UPDATE privilge on the table to a role. and the role is granted to the user. Why do I have to specificly grant the same UPDATE to the user in the body of the procedure again????
Printable View
let's say if I grant the UPDATE privilge on the table to a role. and the role is granted to the user. Why do I have to specificly grant the same UPDATE to the user in the body of the procedure again????
becuase thats the way it works :)
but it's so weird
I have two procedures and I haven't specifically grant the UPDATE on the table to the users yet. one procedure works ok and one is not working. if you are saying that's way it supposed to work, why one is working though. I did check on the privilege on the user but the user doesn't have UPDATE privilege to him beside to the role.
Yea, privileges and procedures are confusing.
Check out definer rights and invoker rights in the concept guide.
In summary, you're probably executing with definer rights, which means that the only privilege the user running it needs is execute on the procedure. This is because when the procedure is executed, it executes with the privileges of the procedure's owner, not the user who runs it.
However, the procedure owner must have the privileges on the underlying objects granted directly to them, not through a role. This is because all roles in the session are disabled while the procedure is executing (with definer rights).
As far as your problem goes, the procedure which works does so because the procedure owner has the update privilege. You'll need to check out why the other procedure doesn't work. Maybe invokers rights/privileges/whatever (invoker rights work differently, but you can check that out yourself).