Is using SQL security a big security hole?

If a user of my application is connecting to their sql server using
integrated security doesn't this allow them to fire up something like QA and
do things to the database that they shouldn't. I know I should have
everything secure even if the user is connected that way but assuming at
least some security functionality is implemented in the app wouldn't they be
able to get around at least what is implemented in the app?
Cheers,
MichaelCorrect. They can login using their Windows account using QA and do whatever
that login has
permissions to do. Consider having your app using application roles.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:OQAf%23ZerFHA.1168@.TK2MSFTNGP11.phx.gbl...
> If a user of my application is connecting to their sql server using integr
ated security doesn't
> this allow them to fire up something like QA and do things to the database
that they shouldn't. I
> know I should have everything secure even if the user is connected that wa
y but assuming at least
> some security functionality is implemented in the app wouldn't they be abl
e to get around at least
> what is implemented in the app?
> Cheers,
> Michael
>|||Hello,
Yes. But it doesn't matter if they connected via Intergrated Security or SQL
security if the the database isn't secure then they will be able to change
what they wanted.
You can look at Application Roles (which are ) or by using SP for all
your interactions with the data. You should be using SP anyway what you may
not be doing is removing the access rights from the tables.
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:OQAf%23ZerFHA.1168@.TK2MSFTNGP11.phx.gbl...
> If a user of my application is connecting to their sql server using
> integrated security doesn't this allow them to fire up something like QA
> and do things to the database that they shouldn't. I know I should have
> everything secure even if the user is connected that way but assuming at
> least some security functionality is implemented in the app wouldn't they
> be able to get around at least what is implemented in the app?
> Cheers,
> Michael
>|||As pointed out, it doesn't matter HOW they get in. Once in, it's up to your
SQL Server security to keep them from doing things you don't want them to.
They could just as easily use an application log in/password to connect with
QA (assuming they could see it).|||"Todd Beaulieu" <nothanks@.enoughspam.bad> wrote in message
news:ObMbQmirFHA.2876@.TK2MSFTNGP12.phx.gbl...
> As pointed out, it doesn't matter HOW they get in. Once in, it's up to
> your
> SQL Server security to keep them from doing things you don't want them to.
> They could just as easily use an application log in/password to connect
> with
> QA (assuming they could see it).
Oops, I forgot I asked this questions :-) The thing is if they login using
SQL security they won't necessarily have the password because it might be
stored by the program they are using and entered by an administrator. So if
users are using integrated security you need to make your sql database
bulletproof. You could use stored procs and security to control it but that
means all of your security has to implemented in the database and none in
your app, which would be fairly hard to achieve.
I'll look into application roles as suggested to see if this gives me what I
want.
Regards,
Michael|||> You could use stored procs and security to control it but that
> means all of your security has to implemented in the database and none in
> your app, which would be fairly hard to achieve.
Yes, that's a design that's commonly used. Deny users permissions on
tables and other objects. I don't know why you say that's hard to
achieve.
Other alternatives are application security or an N-tier architecture
where the users only have access to the middle tier. In those scenarios
you should still generally use stored procedures for all data access -
procs have plenty of advantages other than security.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> Yes, that's a design that's commonly used. Deny users permissions on
> tables and other objects. I don't know why you say that's hard to
> achieve.
I mean it's hard to achieve fully. Sure if I want to stop someone creating a
customer without a surname that's easy to do but I could have some
extrememly complicated restrictions on what data could be entered and in
what way, these restrictions would be impossible or very difficult to
implement within the database. In all of those cases connecting direct to
the database would give the user greater access. I just can't see any app
having every single restriction implemented in the database. Surely there'd
be hundreds of restrictions placed within the source code outside the
database.

> Other alternatives are application security or an N-tier architecture
> where the users only have access to the middle tier. In those scenarios
> you should still generally use stored procedures for all data access -
> procs have plenty of advantages other than security.
True, although not likely to happen unless my app gets a full rewrite.
Michael