February 28, 2018 at 10:52 am
Hi all,
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',
DEFAULT_DATABASE=APPPROD,
CHECK_EXPIRATION=OFF,
CHECK_POLICY=ON;
-- Creating the DB user and Schema for containing the VIEWs
use APPPROD;
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 ?
Regards,
Noob
March 5, 2018 at 12:25 pm
szejiekoh - Wednesday, February 28, 2018 10:52 AMHi all,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-userI 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',
DEFAULT_DATABASE=APPPROD,
CHECK_EXPIRATION=OFF,
CHECK_POLICY=ON;-- Creating the DB user and Schema for containing the VIEWs
use APPPROD;
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 VIEWCREATE 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 -- failedQ1) 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 createdBut...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 ?
Regards,
Noob
If a user has permissions to create views, they would still need permission to select from that view. Creating a view does not imply that they can select from it.
If your original purpose of all of this is to allow the appview user to grant select to other users, you can use the with grant option when granting select on the view to appview. This allows appview to grant permissions on that view.
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply