In a normal scenario, the DBA's grant select access to a user on a view which directly points to a table but the table will not be given a direct access. The views usually are in a different database(level 1) than in the database where the table resides. In some cases the views(level 2) point to other views(level 1) which are ina different database.
For a user to select the view in the 2nd level, the database at the 1st level should be given a SELECT WITH GRANT access. Only then a user can select from the 2nd level to the 0th level(where table resides). if instead only a select privilege is given on 0th level to the 1st level database then the 2nd level view cannot select from the base 0th level table and results in an error shown below.
SELECT Failed. 5315: An owner referenced by user does not have SELECT WITH GRANT OPTION
Also the level1 database needs to have select with grant on the level 0 else the intial select also will not work(from dblevel1 to dblevel0)
in short select will be given to a user on a database(unless views are created by user and he has to grant select to other users) and Select with grant will be given to a database on another database.
When we give select on db1 to db2 with grant option, any user who has all accesses on DB1 will be able to create views using DB2.
I want to give access -> select on DB1 to DB2 with grant option; but want to restict using it for some users.
Is there any way to achieve this?
"any user who has all accesses on DB1 will be able to create views using DB2."
The user would need to have select access on DB2 to create views in DB1 selecting from DB2.
The grant option would allow these views to work.
So just grant select on DB2 to only those users of DB1 that you want to be able to create views on DB2.