ADP Parameterized list box stored proecedure /function

I am trying to populate a list box (MS Access - don't ask it is what my boss wants) with a table-valued function or a stored
procedure. The list box is used to navigate among records that populate the
form, so the same query can be used for the form and the list box thereby,
theoretically, reducing calls to the DB. The records are filtered on two
fields, we'll call them type (GUID) and name (nvarchar), whereby the values
are in two different controls on the form.

The form is using a function, fActiveCompanies, and the imput parameter
propety is completed. The form is able to navigate through the records with
no problem.

I am having difficulty create a SMOOTH method of populating the list box.
currently i am using a stored procedure that is called in VB and populates
the list box with a list of values.

I would prefer to not use a list of values and set the row source type to
Table/View/Stored Procedure.

Please point me in the correct direction for populaitng hte list box not
using a list of values... thank you

Access 2003 - Access Data Projects (ADP) - ADO
SQL Server 2000 (production) SQL Server Express 2005 (devlopment)

my current code for populating the list box is below.

Dim str As String: str = ""
Dim lst As String: lst = ""

Dim rds As ADODB.Recordset
Set rds = New ADODB.Recordset

Forms!frmcompanies!lstCompanies.RowSource = lst
str = "EXEC spActiveCompanies @.pIdTypeCompany='" &
Nz(Forms!frmcompanies!cboTblTypeEntity, "%") & "', @.pNameLegal='" &
Nz(Forms!frmcompanies!txtNav, "%") & "'"

rds.Open str, CurrentProject.Connection

Do Until rds.EOF

If lst = "" Then
lst = """" & rds(0) & """;""" & rds(1) & """"
Else
lst = lst & ";""" & rds(0) & """;""" & rds(1) & """"
End If

rds.MoveNext
Loop

Forms!frmcompanies!lstCompanies.RowSource = lst
Forms!frmcompanies!lstCompanies.Requery

rds.Close: Set rds = Nothing

Forms!frmcompanies.Requery

i mis-read one document, the parameters must be the same as the controls...

field = @.ComboBoxControlName

ADO.NET parameterized query security

I am developing a website for multiple clients, each with their own separate database on SQL Server 2005. The database structures are identical for all clients. I like to use SQL stored procedures for the security advantages (i.e., don't need to grant access to the tables, only exec permissions on the stored procedures), but maintaining and deploying many sp's across all databases is becoming unwieldy and error-prone.

Is there a way to use parameterized queries (SqlCommand, SqlParameter) in C# code (which could be reused for all databases by changing the connection string) without having to grant access to the tables?

From your description, you are relying on ownership chaining to access tables only from SPs; this is possible only because the SP owner is the same as the underlying tables and the security checks are bypassed; but sqlcommand will not be able to use any chaining directly.

In any case, we would like to understand your needs in order to give better advice as well as to understand our customer needs. For example, is the reason behind not granting permissions directly on the table to protect from ad-hoc queries, or to help in managing permissions? If it is for managing permissions, what may be the obstacle from using the existing permission model?

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

You don′t need to grant permissions on the tables as long as the owner of the tables are the same as those for the stored procedure. This is called owner ship chaining, there is more to read about that in the BOL. Don′t breaking the ownership chain means that the permission is checked once at the procedure level. SQL Server assumes then lateron during the access of the table that if the grantee has access to the proc and the grantor created the stored procedure accessing the base tables th grantor also wanted the grantee to access the base tables. Permissions are not checked twice then. If the ownership chain is broken (Another owner of the base tables than the stored procedure) permissions are checked for every underlying base object. Using SQL Server 2005 you can also use impersonation within your stored procedures, acessing data / base tables using the WiTH EXECUTE AS syntax.

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

Thank you all for your quick response and comments!

The reason to avoid granting access directly on the tables is to protect from ad-hoc queries, if an unauthorized user gains access through the login used by the website. To minimize the damage if that were to happen, we want this login to have only minimal rights to the database.

From a wider perspective, I normally prefer using stored procedures, but sometimes we need the flexibility of building a parameterized query in the Web application. I would like to find a way of doing that without giving up the security advantage of using stored procedures. What is the best way to accomplish this?

|||

I would recommend following Jens’ suggestion and use either EXECUTE AS (or digital signatures) in order to change the execution context before accessing the tables. Here are a few good starting points in BOL for this topic:

· Context switching (http://msdn2.microsoft.com/en-us/library/ms188268.aspx)

· Module signing (http://msdn2.microsoft.com/en-us/library/ms345102.aspx)

If you decide to use this mechanism, I would also like to strongly recommend following the least privilege principle. For example, if for this application the application (impersonated) context only needs to have SELECT on a couple of tables, make sure that the permissions are limited only to the proper tables.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine