Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Unanswered: "TRUNCATE TABLE" in a sproc w/locked-down user

    Hullo folks, I'm having what I assume is a fairly mundane security issue.

    I have a SQL login that I am trying to restrict as much as possible. This account's sole goal in life is to hit the server, return some usage statistics, then truncate the table it received the statistics from. I would like to refrain from granting this login permissions on the physical target table if possible.

    Usually I can wrap up "protected" operations in a stored procedure, then grant exec permissions for my user and I'm good to go. However, TRUNCATE TABLE gets cranky with me when I attempt the above method. I suspect that has to do with the fact that TRUNCATE TABLE is effectively modifying the table itself as opposed to merely deleting data.

    Is it possible to grant this login ONLY execute permission on a stored proc that TRUNCATE's tables without giving the user any physical permissions? Am I going about this the wrong way?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  2. #2
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    The only people who can use 'truncate' are the table owner, sysadmin, db_owner, and db_ddladmin; so if you have a user whose permissions are severely restricted I would not think 'truncate' would work, or if it would it would be a phaff to set up.

    Why not just DELETE FROM, or use a temp table to create the statistics and return them?

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    This is effectively what I ended up doing after making the same permissions determination. I had my heart set on Truncate do to the nature of the process itself, but I was able to work around the restriction using DELETE FROM and a local scheduled maintenance job.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Is there a way he can call an osql command via xp_cmdshell that connects with a trusted connection that can truncate the table ? I tried some stuff with a server link to itself, but cannot truncate table link.db.owner.table, same goes with openquery (truncate not supported).

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That would present a larger security issue then outright granting the user required permissions. I definately don't want to expose trusted command line functionality to this login.

    What I have now works, it's just not exactly the way I want it to. All of my research indicates that it will just have to be good enough as I cannot get around that pesky permissions requirement for TRUNCATE.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In the past I have allowed a user to own a single table for a similar purpose. But that was for an import table, where the data was truncated, reloaded, diced up, and distributed to other tables. No other process even knew the table was there. It gets you out of having to have db_owner rights, but there is no other way around the requirement of owning the table.

  7. #7
    Join Date
    Jun 2004
    Location
    Philly
    Posts
    51
    why dont you leave the table there and run a nightly process that truncates any table that had statistics grabbed from it

    have an admin account run the nightly process, but the restricted user still gets their statistics

    just a thought

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by xarfox
    why dont you leave the table there and run a nightly process that truncates any table that had statistics grabbed from it

    have an admin account run the nightly process, but the restricted user still gets their statistics

    just a thought
    That's the current working solution. The process runs a couple times a day, but same idea.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •