locked
View creation / Ownership Chaining / ALTER vs SELECT RRS feed

  • Question

  • Hi all,

    I have read about ownership chaining and make sure that views, though created in another schema, belong to the same owner as their base tables.

    Base tables - in dbo schema, owned by dbo
    Views - created in appview schema, owned by dbo too.

    ===========

    However, I encounter issue when I attempt to assigned another user (e.g. appviewuser), to have permission to create views in appview schema and possibly to grant SELECT on the views created to other end-users

    =========

    Q1) Does having ALTER permission on a schema, gives you permission to SELECT objects inside the schema ?
    As SA, I granted "ALTER" permission on schema:appview + CREATE VIEW to appviewuser, appviewuser is able to CREATE VIEW inside schema::appview but failed to SELECT from the view it has created -> is that normal ?
    (-- appviewuser has already been granted SELECT on the base table used inside the view)

    =========

    Q2) Does having SELECT permission (with grant option) on a schema, give you permission to grant SELECT on individual objects in the SCHEMA to other users,  or it just allow you to GRANT SELECT on the schema as a whole to other users.

    As SA, I granted "SELECT" permission with GRANT option on schema::appview to appviewuser, I realize it is still unable to grant SELECT on individual objects in the appview schema to other end-users, but it could grant SELECT on the schema itself as a whole to other end-users. --> is that normal ?

    =========

    Hope gurus here can sort me out.
    I am on SQL 2014 Enterprise.

    Thank you!

    Regards,
    Noob





    • Edited by szejiekoh Thursday, March 1, 2018 3:13 PM clarity
    Thursday, March 1, 2018 3:03 PM

All replies

  • q1) No. The Alter permission would allow you to change the object but not select items inside; however, you could grant "Select" on the SCHEMA and that will allow you to select all objects in the schema.

    q2) Yes. The WITH GRANT option gives the ability to GRANT the permission to other users. You want to keep the users that can GRANT permissions to an absolute minimum.

    You can also create a Stored Procedure and grant EXECUTE to the user. The user would be able to run the PROC that could SELECT from an object but not SELECT from the object directly. This is the preferred way to assign permissions since you can control what the user is able to do through the PROC code rather than giving them permission to access all the data.

    I hope that helps!


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)


    Thursday, March 1, 2018 3:19 PM
  • q2) Yes. The WITH GRANT option gives the ability to GRANT the permission to other users. You want to keep the users that can GRANT permissions to an absolute minimum.

    Hi Daniel,

    Thanks for your reply.  Can I clarify if having "SELECT with GRANT option"  on SCHEMA level, allows the grantee to grant individual objects inside the schema to another user ?

    E.g. As sa

    grant SELECT ON schema::appview to appviewuser WITH grant option;

    Does that allow appviewuser to grant SELECT on a particular object inside that schema to another end-user  ?

    As appviewuser

    grant SELECT ON appview.PRODUCT_VW to user_a;

    Regards,
    Noob


    • Edited by szejiekoh Thursday, March 1, 2018 4:54 PM clarity
    Thursday, March 1, 2018 4:54 PM
  • It does not. I just tested to be sure. I created a user named Bill. Bill only has the following:

    GRANT ALTER ON SCHEMA::[blah] TO [bill] AS [dbo]
    GO
    
    GRANT SELECT ON SCHEMA::[blah] TO [bill] WITH GRANT OPTION  AS [dbo]
    GO


    Next, I created a user named Bob and then logged in as Bill. Running the GRANT SELECT from Bill succeeds.

    GRANT SELECT ON SCHEMA::[blah] TO [Bob]
    

    However, attempting to GRANT SELECT from BILL to any individual objects in the SCHEMA fails.

    grant select on blah.vwdemo to Bob
    Go
    
    
    RESULT: 
    Msg 15151, Level 16, State 1, Line 3
    Cannot find the object 'vwdemo', because it does not exist or you do not have permission.

    So the net affect is that the appviewuser CAN grant select to other users on the SCHEMA only and the schema does include ALL objects in the schema. But the individual objects cannot be granted themselves.

    Hope that helps!


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Thursday, March 1, 2018 7:21 PM
  • So the net affect is that the appviewuser CAN grant select to other users on the SCHEMA only and the schema does include ALL objects in the schema. But the individual objects cannot be granted themselves.

    Hope that helps!


    Hi Daniel,

    Thanks for the clarification. Now here come the problem,

    1) granting CREATE VIEW + ALTER on schema::appview allow appviewuser to CREATE VIEW on behalf of dbo
    2) granting SELECT on schema::appview allow appviewuser to SELECT from the VIEW it created

    3) but it doesn't allow appviewuser to grant individual views to end-users

    How do I dedicate someone for VIEW creation and granting SELECT on VIEWs created ?
    I do not want to login as dbo for the above activities.

    Regards,
    Noob

    • Edited by szejiekoh Friday, March 2, 2018 12:27 PM clarity
    Friday, March 2, 2018 12:27 PM
  • You can use GRANT CREATE VIEW to allow a user to create views in the database.

    GRANT CREATE VIEW TO Bill

    It's best to use a group instead of granting permissions to an individual user.

    GRANT SELECT is a permission that is on individual objects, such as views, tables, schemas, ...

    the DB_DATAREADER database role grants SELECT on ALL objects (which may be too much but is often used)

    SQL 2016 has data masking that can be used to secure sensitive data when DB_DATAREADER is used.

    I think using the GRANT SELECT on the schema as you've done is a good choice if you don't want the users to access the physical tables.

    Regards,

    Daniel


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Friday, March 2, 2018 3:40 PM
  • Hi Daniel,

    Sorry to not elaborate clearer.

    The base tables are owned by DBO, due to ownership chaining, the views need to be in a schema that is owned by DBO as well.

    However, I wanted a "in charge"  that is able to i) create VIEW in the schema owned by dbo + ii) able to SELECT the view it has created + iii) grant that VIEW to someone else.

    To fulfil the above

    i) GRANT CREATE VIEW + ALTER SCHEMA on schema::appview to appviewuser
    ii) GRANT SELECT on schema::appview to appviewuser
    iii) Using appviewuser, I can't grant the VIEWs created to another end-user, because the VIEWs (though created by appviewuser) is owned by DBO.

    -- i cant grant SELECT on schema::appview to end-user because that would allow the end-user to see all the views in the schema.

    -- i do not want to login as DBO, as we want to limit logins using this user.

    What should i do ?

    Regards,
    Noob

    Saturday, March 3, 2018 8:01 AM
  • Hi Daniel,

    Still around ?

    Regards,
    Noob

    Wednesday, March 7, 2018 12:24 PM
  • Hi Daniel,

    Still around ?

    Regards,
    Noob

    I am. Sorry. I don't check the forums daily. By ownership chaining are you referring to Cross database ownership chaining?

    Granting CREATE VIEW and ALTER SCHEMA allows you to:  

    • Create a new view
    • Alter or drop any object in the schema

    Granting SELECT on the Schema allows you to:

    • Select from any object in the schema

    Granting SELECT on the Schema WITH GRANT allows you to:

    • Select from any object in the schema
    • Grant SELECT to anyone on the schema

    In my earlier example you'll find that the "BLAH" schema is owned by DBO. Bill is not a member of DBO and is not impersonating one. Bill only has the following:  SELECT ON Blah WITH GRANT;  ALTER Blah; CREATE VIEW

    So I think you're just missing "WITH GRANT" on the appviewuser for the SELECT. Keep in mind that GRANT SELECT WITH GRANT on a schema won't give you access to individual objects such as newly created views.


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Wednesday, March 21, 2018 10:16 PM