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

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 -- Received on Fri Feb 17 2006 - 08:50:21 CST