none
Grating execute permission on a stored procedure without grating permission on base table not working RRS feed

  • Question

  • Our production DB server supports a complex web application and the problem we currently face is a kind of a basic security issue and I was asked to resolve it in a week time.  The scenario is that  a few of the members from web application development team by the virtue of being developers know the user Id and password of the DB account which is intended to be used by the web application only.  Some of these developers manipulate the data in the table and that is causing a serious havoc. We do have triggers created on major tables that tell us who changed and what changed etc. But as theses guys use the same userId of the web application , all we can see from the trigger is that same service account user Id only.  I was asked to remove the direct table insert,update & delete permission of the web application user. This should be straight forward because the web application is interacting with the database only through a set of defined stored procedures and a few functions. The application code never touch (neither select nor change) any of the tables directly. As such the web application user doesn’t need to have the write permission to these tables. It should only have execute permission on the defined SPs. I thought I could remove it easily. But that was not the case. We have more than 1000 SPs and all of them are created in one database. The underlying tables the SPs insert,update & delete are in a different database on the same server instance. In that case SQL server expect the user have the permission on the underlying table as well. The execute permission on the SP is not enough. I tried all the methods I can think of such as creating a role and changing the SP code to use with EXECUTE  AS OWNER etc. but none worked. Can someone please help?

    Basically I need the following  test case to work. Thanks in advance.. 

    -- Step1. creating a server login and mapping the login to two databases with read access to all tables. (NO write access)

    CREATE LOGIN testUser WITH PASSWORD=N'passowrd', DEFAULT_DATABASE=[master]
    
    GO
    USE Database1
    GO
    CREATE USER [testUser] FOR LOGIN [testUser]
    ALTER ROLE [db_datareader] ADD MEMBER [testUser]
    
    
    GO
    USE Database2
    GO
    CREATE USER [testUser] FOR LOGIN [testUser]
    ALTER ROLE [db_datareader] ADD MEMBER [testUser]


    --Step 2. create one table in first database and one SP in the other. The SP inserts to table in database1.

    use Database1
    go
    create table testTable(a int, b varchar(100))
    
    use Database2
    go
    create proc testProc @a int, @b varchar(100) as
    Begin
    insert into Database1.dbo.testTable(a,b) values (@a,@b)
    end


    --Step 3 .grating the user execute permission on the SP without grating modify permission on the base table

    GRANT EXECUTE ON testProc TO [testUser]

    --Final step. login as the test user and  try to execute the SP

    use Database2
    execute testProc 100,'aaa'

    now I get the error "The INSERT permission was denied on the object 'testTable', database 'Database1', schema 'dbo'." 

    How can I get it working without making the user able to directly modify the table? 



    • Edited by sqlca Thursday, September 19, 2019 9:51 PM
    Thursday, September 19, 2019 9:47 PM

All replies

  • Hi

    Firstly, thanks for sharing the question detail with detail scripts.

    To achieve your requirement small changes in Step-2 would fix it.

    Below is the updated code in Step-2.

    use Database1
    go
    create table testTable(a int, b varchar(100))
    
    use Database2
    go
    create proc testProc @a int, @b varchar(100) 
    WITH EXECUTE AS OWNER 
    as
    Begin
    insert into Database1.dbo.testTable(a,b) values (@a,@b)
    end

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Friday, September 20, 2019 4:09 AM
  • use Database2
    go
    create proc testProc @a int, @b varchar(100) as
    Begin
    insert into Database1.dbo.testTable(a,b) values (@a,@b)
    end

    Ownership chaining works within the same database & schema, but you have here a cross database access and by default ownership chaining don't work here.

    One Option is to enable cross database ownership, but that's not a good idea and a security risk: Enabling Cross-Database Access in SQL Server

    Other Option is to use a certificate user as executer: Using Certificates for Cross-Database Access


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Edited by Olaf HelperMVP Friday, September 20, 2019 5:27 AM
    • Proposed as answer by NALUTO Monday, September 23, 2019 5:23 AM
    Friday, September 20, 2019 5:26 AM
  • Hi sqlca,

     

    There is three ways to implement it:

     

    1.Enabling the cross database ownership chaining. As mentioned by Olaf, it may cause a security risk.

    2.Setting the first database as TRUSTWORTHY and granting its owner AUTHENTICATE in the second database.

    3.Signing the stored procedure with a certificate that exists in both databases.

     

    For more details,  please refer to https://www.mssqltips.com/sqlservertip/2549/options-for-cross-database-access-within-sql-server/

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, September 20, 2019 7:49 AM
  • The best would be to move the stored procedures into the same database as the tables.

    I have an article on my web stite which discusses solutions for "advanced permissions", and this includes cross-database scenarios. You find the article at http://www.sommarskog.se/grantperm.html. It is quite a long article, and unfortunately you will need to read it all to really understand the cross-database chapter.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, September 20, 2019 9:45 PM
  • Hi

    Just checking in to see if my initial answer helped or you still facing any issues.

     

    If my initial reply answers your query, do click “Mark as Answer” and Up-Vote for the same which might be beneficial to other community members reading this thread .

    And, if you have any further query do let us know.

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, October 22, 2019 3:30 AM