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

Mark D Powell wrote:
> 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".

Yes, that is what I first tried, but since the col_data_owner is a number (the database comes from somewhere else, I do not like to modify it), the "user" should be a number. I could use sys_context() function, but since there are millions of rows in the table which has the column, function evaluation is too slow. At first I tried a package, where package variable had the owner id. Statement

select count(*) from the_view

took more than two days, where are view where the user id is directly stored last only about 20% more then query ower the table

>
> 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.
>

Yes, but we cannot (that is: our project does not have euros enough) use VPD, since it need Enterprice Edition.

> 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.

Check the query example.

>
> HTH -- Mark D Powell --
>

--
Arto Viitanen, CSC Ltd.
Espoo, Finland