"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?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?|||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.|||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).|||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.|||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.|||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|||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.

"order by" is slowing page load

hi guys,
the following test script works fine and displays a list of cars from the fairly small database, but if I specify the sort order in the querystring, the page takes ages to display and usually times out. Can someone look over it please and tell me where I can fine-tune it for performance or redundant code?
thanks
M

<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Dim oRS,oConn,myOrder,strSQL

Set oRS = Server.CreateObject("ADODB.Recordset")
Set oConn = Server.CreateObject("ADODB.Connection")
'next, a couple of test lines to prevent timeout (seems to have no effect)
oConn.CommandTimeout = 0
Server.ScriptTimeout = 0

Set strOrder = Request.QueryString("Order")

oConn.ConnectionString = "Provider=MSDASQL;DRIVER=SQL Server;SERVER=address;UID=userID;PWD=password;DATA BASE=name"
oConn.Open
%>

</head>

<body>
<%
strSQL = "Select make,model,price from vehicles where cat = 'car' AND active = 'yes'"
if strOrder <> "" then
strSQL = strSQL & " ORDER BY " & strOrder
end if

oRS.Open strSQL, oConn, 2, 3

oRS.moveFirst
Do while not oRS.eof

make = oRS("make")
model = oRS("model")
price = oRS("price")


%>
<%=make%> <%=model%> <%=price%><BR>
<%
oRS.MoveNext
loop

oRS.close
set oRS= nothing
oConn.close
set oConn=nothing
%>
</body>
</html>well you could use a bubble sort to handle the sort in the app code (where presentation issues should be handled) or you could index your order by column.|||thanks, urm...what's a bubble sort and how do I do it?
and how do I create an index?
the columns to sort by are text and updated regularly by the CMS.

I have Googled indexing SQL Server and cursor types, but all the stuff I find assumes an already high level of understanding which is really annoying (the opposite of a Microsoft help file which states the obvious - "this a whim whom slurping valve, use it to slurp whim whoms...") sorry, I digress.|||Yeah? Go read the IBM or Oracle Manuals....

In any case, are we talking about SQL Server in the first place?

Do you have a dba or are you "it"

And why aren't you using stored procedures?|||This nifty asp you've written is wide open to sql injection. Do you know what that is?|||Oh God, Oracle manuals are the worst...|||I think the bubble sort thing was a little joke by Sean that went over your head. :)

bubble sort is a sorting algorithm, and one of the least efficient ones. it's a CS 101 kind of algorithm. search for it on google, but don't use it. In fact most pages you find that describe it should tell you not to use it.|||Can you elaborate on the sql injection problem please?:o
I have had some scum bags trying to put their smutt into my database before.

Brett, what do you mean by "Do you have a dba or are you "it""
Feel free to direct me in the right direction|||Who administers your database?

Like, who creates tables for eaxmple?|||Can you elaborate on the sql injection problem please?:o
I have had some scum bags trying to put their smutt into my database before.http://www.rockyh.net/AssemblyHijacking/AssemblyHijacking.html

Gist is - if you concatenate strings from user input and submit to the database for execution and you are wide open to SQL Injection. Parameterise your data accces (don't try cleaning your strings).|||Parameterise your data accces (don't try cleaning your strings).

Why can't we clean up strings?
I've always used simple replace commands to stop SQL injection in my Access projects...|||Why can't we clean up strings?
I've always used simple replace commands to stop SQL injection in my Access projects...

Oh yeah...like what?|||replacing apostrophes, equals signs, asterix' and percentages...

Eg. replace(<fieldname>,"'","")|||Great..parse this

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
EXEC(@.sql)|||Sounds like a problem around tempdb. Query the waittype value in the sysprocesses table, to see what this is hanging up on. Your tempdb is not set to autoshrink, is it?|||DECLARE @.sql varchar(8000)
SELECT @.sql = 'DECLARE @.x varchar(8000) '
SELECT @.sql = @.sql + 'SET @.x =
CHAR(68)
+CHAR(82)
+CHAR(79)
+CHAR(80)
+CHAR(32)
+CHAR(68)
+CHAR(65)
+CHAR(84)
+CHAR(65)
+CHAR(66)
+CHAR(65)
+CHAR(83)
+CHAR(69)
+CHAR(32)
+CHAR(78)
+CHAR(111)
+CHAR(114)
+CHAR(116)
+CHAR(104)
+CHAR(119)
+CHAR(105)
+CHAR(110)
+CHAR(100)
SELECT @.x'

EXEC(@.sql)|||suffice it to say that scrubbing strings will address only the most elementary injection attacks.

the real fix is: NEVER execute sql that was pasted together based on user input. if you do this, you are vulnerable no matter how tricky your parsing code is.|||Great..parse this

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
EXEC(@.sql)
Ahh clever... I did say I was using access but I never considered this|||Great..parse this

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
EXEC(@.sql)

actually, that statement would probably fail because of the GO. :)

remove the GO and you are in business.|||I think the bubble sort thing was a little joke by Sean that went over your head. :)

you guys ruin all of my fun. i am too busy these days to lead the posters around by the nose for several posts anyways.|||Haha I remember learning (not to) use bubble sorts 2 years ago... Man those were long lessons!

Imagine your old, grey, chunky, droning computing teacher mumbling on for an hour and a half just to turn round and go "..and that's why you won't ever use this" >:(|||bubble sort is actually ok if you only have a few items to sort. in practice even the most hairbrained algorithm will be fast if there are only 10 items.

as soon as you have 100k to sort though, your code will fall over if you use it.|||Thanks jezemine
is it likely that my small database of 30 or 40 cars with descriptions and specs will slow to the point of timeouts when I ask it to order by 'price' for instance?
my DB seems to have suddenly done this, could it be thatthe amount of data has suddenly broken some threshold? or as I suspect, could 1and1 have changed my DB at a low level and cripled its sort capabilty. It was fine last week.
NB: please keep it simple, all this archane terminology is just making matters worse for my little brain!|||I don't think so...

http://www.autotrader.com/fyc/searchresults.jsp?num_records=&search_type=both&distance=10&address=07052&style_flag=1&make=ASTON&model=&make2=&start_year=1981&end_year=2008&min_price=&max_price=&transmission=&engine=&drive=&doors=&fuel=&max_mileage=&color=&keywords_display=&sort_type=priceDESC&body_code=0&certified=&advanced=&default_sort=priceDESC&keywordsrep=&keywordsfyc=|||actually, that statement would probably fail because of the GO. :)

remove the GO and you are in business.

Ummm, no

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO SELECT * FROM Employees'
EXEC(@.sql)|||I think you can always substitute a semicolon for the GO, anyway.|||Guys...what are you talking about?
what does this do?

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO SELECT * FROM Employees'
EXEC(@.sql)

- is this part of the thread or are you off on a tangent?|||Ummm, no

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO SELECT * FROM Employees'
EXEC(@.sql)

I stand corrected!

I was under the impression that "GO" was only understood by the client tools like osql/sqlcmd/QA as a batch separator. apparently it's also understood by EXEC and sp_executesql.|||Guys...what are you talking about?
what does this do?

- is this part of the thread or are you off on a tangent?
They are off on a semi-tangent, regarding whether the code you are using is safe from SQL Injection attacks.|||Update: it was 1and1 Internet, (again) their DB server was down.
Note to self: ditch them.
Thanks for you help folks, although now, I am totally paranoid that i'm going to be injected.
I guess I should be...|||Update: it was 1and1 Internet, (again) their DB server was down.
Note to self: ditch them.
Thanks for you help folks, although now, I am totally paranoid that i'm going to be injected.
I guess I should be...

Ummmm...you...will be...you will...be...

In any case there are ways...but you need control, you must learn control

Use stored procs access only and you'll be fine|||a poorly written proc is just as vulnerable to sql injection. consider this proc:

create proc ExecSql (@.sql varchar(max)) as exec(@.sql)