Post reply

Setting flexible security for reporting

  • January 2, 2020 at 10:40 pm

    Go to Answer

    #3711977

    I have an increasing number of reports distributed to users. Managing the security is providing challenge. Reporting code is stored in a repository database on the same server the production DB. A user needs to have EXECUTE on stored procedures in the repository DB, though stored procedures that call SP's in the production DB, and the production DB the user must have SELECT on whenever tables are included in the reports. Users come and go pretty quickly.

    It seems like this is an opportunity for me to learn about impersonation and EXECUTE AS. Does that make sense? If it does, can somebody point me towards some good documentation help me get started? Or, if there's a better way please point me towards that.

  • January 2, 2020 at 11:21 pm

    #3711987

    I'd think roles would really matter here as well, but it's hard to know unless you explain a little better with some details in what you do now, or what you need for users to access the reports/procs.

  • January 2, 2020 at 11:44 pm

    #3711990

    Thanks for the response.

    There’s an enterprise application the db (db 1) for which sits on the server. I don’t want to add or change anything in db1. Therefore, I created another db (db2) on the same server to hold my code, supporting tables, etc. I often get data from db1 by executing sp’ s from db1 within my sp’s in db2. I can get away with that in developing my stuff, but managing permissions in the two db’s covering the various resources in each trips up admins, users, and me. The reports and analyses I provide for them are almost all delivered in Excel front ends, which call my sp’s from MS Query. Those Exel files get passed around to users I don’t know about, but who need them to do their jobs.

    It occurred to me that I might encapsulate just the specific permissions required to run my reports and use them at runtime, but I’m unclear on how to do that with a user logged in under a Windows account, running a query from Excel, that goes to an sp in one db (where I have complete control), which in turn calls code and reads data in another db where I have very little control.

    Does that help?

  • January 3, 2020 at 12:44 am

    #3712011

    1. Create the procedures that support the reporting with EXECUTE AS OWNER. (This can easily be added existing procs)
    2. Create a new role in the reporting database called db_Execute.
    3. Grant the EXECUTE priv to the new db_Execute role.
    4. Create a new Active Directory (AD) user group.
    5. Add that AD user group as a login to the server.
    6. Grant connect and db_Execute privs to the AD user group for the reporting database.
    7. Inform management to request that individual logins be made members of that AD user group if/when they need such reporting rights as what you're currently having to deal with.
    8. When someone leaves the company, the Active Directory login is disabled and possibly dropped.  That also disables or drops their membership in the AD user group.
    9. You no longer have to juggle logins, users, or privs for the reporting database.  It's all handled at the AD level, which they do anyway.

    p.s.  The users basically will have no privs except to execute stored procedures in the reporting database unless you have some other AD group that gives them privs.  This is where the uses of xp_LoginInfo and sp_ValidateLogins comes into play.  They both look into Active Directory and give you all of the information you need as to what path is taken to provide someone or some AD group privs.

    p.p.s.  If you need to have multiple access controls based on stored procedures, you can still do it all with AD user groups as laid out above.  You just need to create more than one db_Execute_{ReportGroupHere} and then grant execute privs to that role by AD user group that should be allowed to execute a group(s) of individual procs.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 3, 2020 at 2:17 am

    Answer

    #3712020

    I’m certain there are devils lirking in the details, but that ranks as one of the most succinct and concise answers I have received in 30 years of asking questions in online forums.

    Thanks

  • January 3, 2020 at 3:32 am

    #3712022

    Thanks, Richard.  It's only because I have a long beard and that it took me 30 years of answering questions on forums to get there. 😀

    To be honest, I don't believe a fellow war horse will have a problem with any of that but, if you have any problems instituting any of that, please don't hesitate to ask on this very thread.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 3, 2020 at 3:36 am

    #3712023

    p.s.  And, yeah... it even works with refreshable spreadsheets that call stored procedures.  As you know, the spreadsheets should absolutely be calling stored procedures so that you're not chasing down spreadsheets to make changes even though the gazintas and the gazotas haven't changed.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 4, 2020 at 6:51 pm

    #3712395

    Yo, Jeff --

    So, here's a detail...

    When I compile my sp's, in 'my database', with EXECUTE AS OWNER it's fine, but now there are problems in the production db. The sp in the production database that are called from my sp are bitching. Tables in production that I join to in the sp in 'my database' are uncooperative. When I compile without the EXECUTE AS statement all is well. I can't make random changes in the 'other database', especially to security. I can log into production with my own credentials  Before I start hacking I thought I'd run it past you.

  • January 5, 2020 at 5:07 pm

    #3712445

    You should review this article: http://www.sommarskog.se/grantperm.html

    There are several options available for EXECUTE AS - but to be safe I would recommend using a certificate and signing your procedures with that certificate.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • January 5, 2020 at 5:15 pm

    #3712446

    Excellent reference, thanks!

  • January 5, 2020 at 7:07 pm

    #3712450

    RichardFRay wrote:

    Yo, Jeff --

    So, here's a detail...

    When I compile my sp's, in 'my database', with EXECUTE AS OWNER it's fine, but now there are problems in the production db. The sp in the production database that are called from my sp are bitching. Tables in production that I join to in the sp in 'my database' are uncooperative. When I compile without the EXECUTE AS statement all is well. I can't make random changes in the 'other database', especially to security. I can log into production with my own credentials  Before I start hacking I thought I'd run it past you.

    To answer your questions, I need to know... What are they bitching about?  What are the errors or notifications?  And what privs does the actual owner of the database have and who is the owner?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 6, 2020 at 7:01 pm

    #3712793

    Production DB is (RTPOneReporting) DB Owner is 'sa'

    Cross database ownership chaining is NOT enabled.

    My repository DB is (JHMRReporting) DB Owner is 'dbo'

    Cross database ownership chaining is NOT enabled.

    My sp, [JHMRReporting].[CallBuyerReportWork], creates a temp table, then calls

    [RTPOneReporting].[proc_reportRetailInventoryOnHandByDateJHMRv2]

    to populate it, then joins back to the table [RTPOneReporting].[RetailProductProfile] to add two columns from the table.

    When I use my own account (a member of an AD Group with the sysadmin role), all is well, but success on for users requires giving undesired access.

    When I use the EXECUTE AS OWNER option in compiling my sp, the result is:

    Msg 229, Level 14, State 5, Procedure proc_reportRetailInventoryOnHandByDateJHMRv2, Line 1 [Batch Start Line 0]The EXECUTE permission was denied on the object 'proc_reportRetailInventoryOnHandByDateJHMRv2', database 'RTPOneReporting', schema 'dbo'. 
    Msg 229, Level 14, State 5, Procedure  [CallBuyerReportWork], Line 103 [Batch Start Line 0]The SELECT permission was denied on the object 'RetailProductProfile', database 'RTPOneReporting', schema 'dbo'.

    [RTPOneReporting].[proc_reportRetailInventoryOnHandByDateJHMRv2] and [RTPOneReporting].[RetailProductProfile] are owned by dbo.

    • This reply was modified 4 years, 10 months ago by  RichardFRay.
    • This reply was modified 4 years, 10 months ago by  RichardFRay.
    • This reply was modified 4 years, 10 months ago by  RichardFRay.
    • This reply was modified 4 years, 10 months ago by  RichardFRay. Reason: Added information on cross database ownership chaining. (In red)

  • January 7, 2020 at 6:11 pm

    #3713322

    The issues you are running into are related to cross database access. The article Jeffery Williams posted is an excellent reference for these issues. There is a section named Cross Database Access that goes into the issues as well as ramifications of the ways to get around this. Certificate signing does not have the same issues and is generally a more secure approach and has some other benefits as with auditing which is also addressed in the article.

    Sue

  • January 7, 2020 at 6:57 pm

    #3713347

    RichardFRay wrote:

    Production DB is (RTPOneReporting) DB Owner is 'sa'

    Cross database ownership chaining is NOT enabled.

    My repository DB is (JHMRReporting) DB Owner is 'dbo'

    Cross database ownership chaining is NOT enabled.

    My sp, [JHMRReporting].[CallBuyerReportWork], creates a temp table, then calls

    [RTPOneReporting].[proc_reportRetailInventoryOnHandByDateJHMRv2]

    to populate it, then joins back to the table [RTPOneReporting].[RetailProductProfile] to add two columns from the table.

    When I use my own account (a member of an AD Group with the sysadmin role), all is well, but success on for users requires giving undesired access.

    When I use the EXECUTE AS OWNER option in compiling my sp, the result is:

    Msg 229, Level 14, State 5, Procedure proc_reportRetailInventoryOnHandByDateJHMRv2, Line 1 [Batch Start Line 0]The EXECUTE permission was denied on the object 'proc_reportRetailInventoryOnHandByDateJHMRv2', database 'RTPOneReporting', schema 'dbo'. 
    Msg 229, Level 14, State 5, Procedure  [CallBuyerReportWork], Line 103 [Batch Start Line 0]The SELECT permission was denied on the object 'RetailProductProfile', database 'RTPOneReporting', schema 'dbo'.

    [RTPOneReporting].[proc_reportRetailInventoryOnHandByDateJHMRv2] and [RTPOneReporting].[RetailProductProfile] are owned by dbo.

    Lordy... not sure what you have in your proc but I've not had that particular problem in the past.

    I DO absolutely agree with the others about Erland's post... it's probably the best one out there on the subject.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 7, 2020 at 7:00 pm

    #3713351

    Thanks much, I'm on Chapter 3 now, reading the whole thing. I'll be better informed when I complete it, and understand it, I'm certain!

Viewing 15 posts - 1 through 14 (of 14 total)

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