Inserting Records with limited privileges

[stacey.michols]

I am trying to insert records via ASP, with a user that has only write
access to the table (db_datawriter, db_denydatareader).
That way, if the server is ever compromised, the access information
stored in the source code's connection string will not allow anybody to
actually read the database.

The problem is that I would like to use ADO methods to insert the data
(to prevent SQL injections), but I can't seem to get the right
connection. It works in plain SQL, but I'd rather not use it.

My current code looks like this:
connection="Provider=SQLOLEDB.1;User ID=DBwriter;Password=XXX;Data
Source=MYSERVER;Initial Catalog=MYDB;"
set conn=server.createobject("ADODB.Connection")
conn.mode=2 ' adModeWrite
conn.open connection

Set rs = Server.CreateObject ("ADODB.Recordset")
rs.Open "MYTABLE", conn, adOpenKeySet, adLockPessimistic, adCmdTable
rs.AddNew
rs.Fields("testfield") = "TESTDATA"
rs.Update
And the error I get is:
Microsoft OLE DB Provider for SQL Server (0x80040E09)
SELECT permission denied on object 'MYTABLE', database 'MYDB', owner
'dbo'.

(If I use a User with read privileges in the connection string
everything works fine.)

[David Portas]

Create a stored procedure to do this and then call the proc from ASP.

Stored procedures are a powerful way to create a secure data-access
layer because you can deny users all permissions on table objects and
just grant them execute permission on the procs you want them to use.
This approach also protects against SQL Injection, provided you avoid
dynamic code in your procs.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

(st************@gmail.com) writes:

My current code looks like this:
connection="Provider=SQLOLEDB.1;User ID=DBwriter;Password=XXX;Data
Source=MYSERVER;Initial Catalog=MYDB;"
set conn=server.createobject("ADODB.Connection")
conn.mode=2 ' adModeWrite
conn.open connection

Set rs = Server.CreateObject ("ADODB.Recordset")
rs.Open "MYTABLE", conn, adOpenKeySet, adLockPessimistic, adCmdTable
rs.AddNew
rs.Fields("testfield") = "TESTDATA"
rs.Update
And the error I get is:
Microsoft OLE DB Provider for SQL Server (0x80040E09)
SELECT permission denied on object 'MYTABLE', database 'MYDB', owner
'dbo'.

(If I use a User with read privileges in the connection string
everything works fine.)

When you use adCmdTable, ADO reads the row in the table - or rather
it tries to do.

You should not use Open at all. Just send parameterized INSERT statements
with adCmdText:

cmd.CommandText = INSERT tbl(col1, col2) VALUES (?, ?)
cmd.CreateParameter("@par1", ad..., ,, Value)
cmd.CreateParameter(...)
cmd.Execute ,,adExcuteDirect

Please see the ADO documentation for all details on CreateParameter.

You can also use stored procedure as suggested by David. In such case
you should use command type adCmdStoredProcedure. You would pass
parameters in the same way as in the snipped above. Stored Procedures
can prove to be a performance booster in your case.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[stacey.michols]

Thanks for the pointer towards stored procedures!

I just got into 'industrial strength' databases (coming from Access),
and after checking out all the things you can do with SPs, I'll NEVER
go back!