A grant to grant a role without granting the grant to the role

A grant to grant a role without granting the grant to the role

Post by Mark D Pow » Sat, 18 Feb 2006 23:50:21


Arto, there are two alternate approaches to what you are doing that I
think deserve mentioning. One is rather than grant access directly to
user_a's tables create a view with check option that filters the roles:
"where col_data_owner = user".

The other approach would be to use Row Level Security, RLS, and then
you can guarentee that a user can access only certain associated rows.

Otherwise you will have to live with "Management" having rights to
user_a's tables though you might try writing a DBA owned stored
procedure that grants the role to a user and grant execute on this
procedure to user Management. The procedure could have a test that the
passed in user <> Management.

HTH -- Mark D Powell --