[AccessD] Access 2013 -- MDB Back Ends Dead?

Hi John:

Pushing code from any client has great risk as the system is now open to
insertion attacks. In the over all scheme of things, parameter passing is
far more secure. 

Think about web sites. The FE is completely exposed to virtual anyone in the
world, the middle tier though isolated can be duplicated, in an attempt to
fool the system, so that leaves your DB BE as your only and best line of
defense. The DB, like MS SQL or MySQL can apply a series of very fine grain
controls and levels of security through passwords, roles, groups and so on 

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Monday, July 23, 2012 6:03 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access 2013 -- MDB Back Ends Dead?

Don't forget that the SP has to be assigned execute permissions in SQL
Server for the user trying to 
execute the SP.

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

On 7/22/2012 9:36 PM, Arthur Fuller wrote:
> Mark,
>
> Generating the SPs for Inserts a cinch. SSMS does most of the heavy
lifting
> for you. To create an Insert stored procedure for any given table in the
> database of interest, do this:
>
> 1. In SSMS, expand the database and its Tables node.
> 2. Right-click on the table name and select Script Table as | Insert To |
> New Query window.
>
> You will get something like this (from the Northwind database):
>
> <sql>
> INSERT INTO [Northwind].[dbo].[Categories]
>             ([CategoryName]
>             ,[Description]
>             ,[Picture])
>       VALUES
>             (<CategoryName, nvarchar(15),>
>             ,<Description, ntext,>
>             ,<Picture, image,>)
> GO
> </sql>
>
> I picked a small table so I could save as much space as possible in this
> reply, but the same thing happens no matter how many columns your table
> has. All you have to do now is replace the toksns with their parameter
> equivalents, twice, once in the parameter list area and then again in the
> section beneath the VALUES keyword
>
> Parameter List:
>
>             (<CategoryName, nvarchar(15),>
>             ,<Description, ntext,>
>             ,<Picture, image,>)
>
> VALUES List:
>
>             (@CategoryName
>             , at Description
>             , at Picture)
>
> Save the script to an SP name. Next Table. How hard is that?
>
> In the Access FE, for each of these generated SPs you'll need to create a
> command object and add a parameter for each of the parameters your SP will
> accept. Once you've added one parameter, you can copy and past that line
> over and over until you've accounted for all the columns. All you'll need
> to do then is indicate where the parameter values are coming from (i.e.
the
> controls on a form, most likely, which in all probability have names
almost
> identical to the columns from which they derive). One last line to execute
> the command object and you're done.
>
> Granted, it would be nice on both sides (Access and SQL) if there were
> tools to do what I just outlined. But come on, how difficult is it to do
> what I just outlined? If the Access side takes you a minute and the SQL
> side takes another minute, and you have 100 tables, in less than two hours
> you're all done. And without breaking a sweat.
>
> Arthur
>

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com