Post reply

Blocking caused by implementing object level permissions

  • November 17, 2004 at 4:47 am

    #91528

    I have inhereted a system where all application access to the database is via a SQL Login with db_owner level permissions. Nice!

    In a fit of madness I thought that I would implement object level permissions to make the system more secure.

    All the database objects are owned by dbo, but calls to stored procedures, and table/view access both direct (which is being converted to sp's) and within sp's rarely specifies the 'dbo.' object owner prefix.

    Having created a db role, assigning it all the object permissions required for the application, assigning the new role to the SQL Login, and removing db_owner from the SQL Login - we are now seeing blocking under peak load.

    Reverting to db_owner perms, the blocking disappears.

    Therefore resolving object permissions must be causing enough of an overhead to cause the blocking.

    I am aware that non-dbo object ownership can be a performance problem unless the owner is always specified in the code, but I have never seen this before with dbo.

    We know the table being blocked - would my best route forward be to change all the sp's using it to specify 'dbo.' wherever that table is used, or am I stuck with using db_owner for the application login?

    Any thoughts would be appreciated.

    Thanks

  • November 17, 2004 at 9:01 am

    #530497

    I have a very similar application / database where the users have db_owner, and I can tell you that one of the thing you may want to investigate before continuing down this road of object level permissions is if the client side .exe code also uses SQL to access the database or not. 

    I had changed all the stored procedures and functions to reference all objects as dbo.<objectname>  and then found out that there was quite a bit of front end embedded SQL code for the application that I could not change (no access to application's source code).  So.... it was a wasted effort for me.  Hopefully you don't have a similar issue.



    A.J.
    DBA with an attitude

  • November 18, 2004 at 2:50 am

    #530602

    Thanks for the comment A.J.

    Fortunately the client side code is 99% stored proc calls, and we're working on converting the remaining to sp's.

    I'm still not convinced changing to dbo. prefix everywhere will solve my problem though - but it's the only thing I can think to do unless anyone knows otherwise...

     

  • November 18, 2004 at 4:25 am

    #530616

    procs called with sp_  and not qualifying owner names for objects will cause performance problems, I'm actually surpised you can see this happening, which is worrying as I can image many third party app providers using this as a justified reason for using dbo for the user connections and so on.

    You should be able to figure out which procs are causing the problems to your table and look to optimise the code better.

    I would say that a call to  sp_xxxxx  where the procs are in user databases will give problems with procedure cache locking - drop the sp_ and get a dbo. in front of all the object names AND every procedure call.

     

     

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 4 posts - 1 through 3 (of 3 total)

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