I have several tables owned by DBO and residing in the DBO schema.
I need to hide several sensitive columns by creating VIEWs and then grant SELECT on them to the end-users.
I have read about ownership chaining and kept all VIEWs in a separate schema that is still own by DBO.
However, I do not want to want to be login as DBO everytime for the following tasks
1) the creation of the required VIEWs
2) the granting of SELECT permission of the VIEWs to end-user
I tried creating a separate login/user to do so but failed to achieve my purpose.
Here how it goes
-- Login as SA
CREATE LOGIN APPVIEW WITH PASSWORD='abc123',
-- Creating the DB user and Schema for containing the VIEWs
CREATE SCHEMA APPVIEW AUTHORIZATION dbo;
CREATE USER APPVIEW FOR LOGIN APPVIEW WITH DEFAULT_SCHEMA=APPVIEW;
GRANT ALTER ON SCHEMA::APPVIEW TO APPVIEW;
GRANT CREATE VIEW TO APPVIEW;
I have a APPVIEW login which is connected to the APPVIEW user in APPROD DB.
In APPPROD DB, I have an APPVIEW schema owned by DBO
I also granted ALTER APPVIEW schema to the APPVIEW user and also grant CREATE VIEW to APPVIEW user
-- As sa/dbo, grant target dbo.TABLE to APPVIEW and allowing APPVIEW user to CREATE VIEW
GRANT SELECT ON DBO.PRODUCT TO APPVIEW;
-- Login as APPVIEW user and CREATE VIEW
CREATE VIEW APPVIEW.PRODUCT_VW AS
SELECT PRODUCT_CODE, PRODUCT_NAME FROM DBO.PRODUCT;
-- View was created successfully but here comes the problem
SELECT * FROM APPVIEW.PRODUCT_VW -- failed
Q1) As APPVIEW user, even though I have ALTER privilege on the schema APPVIEW and I am able to CREATE VIEW in it, I am not able to SELECT
the view I have created, is that normal ?
-- i guess this is because the VIEWs thought created by APPVIEW user, are owned by DBO; hence without DBO granting the SELECT on the VIEW created, APPVIEW user is not able to SELECT the view it has created -- am I right ?
Hence, I granted SELECT ON schema::APPVIEW to APPVIEW user
GRANT SELECT ON SCHEMA::APPVIEW TO APPVIEW;
Yes, I am now able to use APPVIEW user to perform on behalf of DBO for
i) CREATE VIEW (as long as DBO has granted SELECT on the base table to APPVIEW user)
ii) SELECT the VIEW it has created
But...I need to GRANT the VIEWs to other end users using APPVIEW user, but it failed.
GRANT SELECT ON APPVIEW.PRODUCT_VW to USERA;
Msg 15151, Level 16, State 1, Line 9
Cannot find the object 'PRODUCT_VW', because it does not exist or you do not have permission.
Q2) My guess again would be PRODUCT_VW is not owned by APPUSER, but is owned by DBO.
With SELECT privilege on the SCHEMA, APPUSER is able to SELECT PRODUCT_VW, but is not able to GRANT it to other users. -- am I right ?
I am left with the choice to login as DBO and grant the VIEW to the end user -- but that defeat my original purpose of letting another user/login (APPVIEW) handle the creation and granting of SELECT to other users.
Q3) Is my Q1) and Q2) 's assumption correct ? What should I do to achieve my requirement ?