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

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply