SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CREATING VIEW / ASSIGNING PERMISSION / Without breaking Ownership Chains - double thoughts


CREATING VIEW / ASSIGNING PERMISSION / Without breaking Ownership Chains - double thoughts

Author
Message
szejiekoh
szejiekoh
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3731 Visits: 285
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

Sue_H
Sue_H
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77624 Visits: 15517
szejiekoh - Wednesday, February 28, 2018 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


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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search