Preventing Injection - Client Side

Thanks
I am building an office application which uses Microsoft Access as a
client and SQL Server as the server. I am new to SQL Server and I
don't want to anything that is too stupid. I am assuming the db
administrator would create a database specifically for this
application. The program must be able to create and drop tables and
works fine when hosted over the internet.
I am preventing everything but characters and numbers in my WHERE
clauses and data to prevent injection.
But besides this measure, the program has these access/security
needs/issues.
Tables - Create Drop Read Write
SPs -- Create Execute
In addition the program needs to read from
system_user
db_name()
information_schema.tables
Information_schema.columns
sysobjects
Am I doing anything too stupid if the admin would prefer a more secure
situation, maybe on a company database?
Willywilly wrote:
> Tables - Create Drop Read Write
> SPs -- Create Execute
> In addition the program needs to read from
> system_user
> db_name()
> information_schema.tables
> Information_schema.columns
> sysobjects
Who is the owner of these tables? Are they owned by "dbo"? If so, all
users would have to be aliased as the dbo in the database, which means
they probably have too many rights. They could be limited to creating
tables under their user names (e.g. Joe.MyTable), but this would not
give other users access to these tables. Creating and Dropping tables is
generally a system admin or database owner function and not normal for
an application. Unless, of course, you mean temp tables.
To perform DML operations on tables, I would use stored procedures. You
can use them for SELECT statements as well, but some users choose to
embed SQL in the app (I prefer SPs all around).
The other objects/functions are available to all users.
Maybe you could explain the need to create/drop tables a little more.
Same for stored procedures.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> I am preventing everything but characters and numbers in my WHERE
> clauses and data to prevent injection.
Consider using parameters rather than concatenating user-supplied values to
build the SQL statement. This is more secure.
Hope this helps.
Dan Guzman
SQL Server MVP
"willy" <willrich33@.yahoo.com> wrote in message
news:1131557696.739603.25830@.g14g2000cwa.googlegroups.com...
> Thanks
> I am building an office application which uses Microsoft Access as a
> client and SQL Server as the server. I am new to SQL Server and I
> don't want to anything that is too stupid. I am assuming the db
> administrator would create a database specifically for this
> application. The program must be able to create and drop tables and
> works fine when hosted over the internet.
> I am preventing everything but characters and numbers in my WHERE
> clauses and data to prevent injection.
> But besides this measure, the program has these access/security
> needs/issues.
> Tables - Create Drop Read Write
> SPs -- Create Execute
> In addition the program needs to read from
> system_user
> db_name()
> information_schema.tables
> Information_schema.columns
> sysobjects
> Am I doing anything too stupid if the admin would prefer a more secure
> situation, maybe on a company database?
> Willy
>|||Dan,David:
Thank you for making me think about security.
My application does not allow the user to use tables under any other
username (such as dbo) so he is fairly isolated. I realize Access
defaults to dbo but I will be shutting the database window down.
I tried to import a file to my database under a different username (like
dbo) but Access converted it back to the username of the new
database/login so that seems somewhat secure. This is why I am using
Access for a client program.
I am not coding for characters other than Like *[A-Z0-9] so the purging
of other characters from the column and table names that the user has
control of will have to do for now.
In the documentation I am going to highly recommend "isolating" the
application in separate db for "security reasons."
Thank you for suggesting parameters and DML as solutions for preventing
injection. I will keep my eye out for information on them.
I really need to learn more about SQL Server permissions and security.
Willy
*** Sent via Developersdex http://www.codecomments.com ***|||Comments inline
"willy" <willrich33@.yahoo.com> wrote in message
news:1131557696.739603.25830@.g14g2000cwa.googlegroups.com...
> Thanks
> I am building an office application which uses Microsoft Access as a
> client and SQL Server as the server. I am new to SQL Server and I
> don't want to anything that is too stupid.
You already missed this goal by using Access as the front end.

> I am assuming the db
> administrator would create a database specifically for this
> application. The program must be able to create and drop tables and
> works fine when hosted over the internet.
>
This would require opening a SQL port to the internet. Not a particularly
good idea from a security standpoint, especially when you are talking about
the privileges necessary to do what you ask.

> I am preventing everything but characters and numbers in my WHERE
> clauses and data to prevent injection.
>
Doesn't matter. With open network ports and SQL credentials in the Access
app, your server is open to conection via Query Analyzer or any other SQL
client app. Who cares about SQL injection when I can send any SQL command I
desire.
> But besides this measure, the program has these access/security
> needs/issues.
> Tables - Create Drop Read Write
> SPs -- Create Execute
> In addition the program needs to read from
> system_user
> db_name()
> information_schema.tables
> Information_schema.columns
> sysobjects
> Am I doing anything too stupid if the admin would prefer a more secure
> situation, maybe on a company database?
Maybe on a throwaway system. I certainly wouldn't allow any such
application anywhere near any of my servers.
> Willy
>
Sorry if I sound harsh, but I am trying to discourage you from making some
fundamental mistakes in building a SQL application. Access front end
'applications' have caused me more headaches than any other single app dev
environment when connecting to SQL Server.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP