Re: sql connection security
From: Aris (aroutis_at_yahoo.com)
Date: 04/30/03
- Next message: Aris: "How do I audit insert/delete/update etc"
- Previous message: karthik: "Re: sql connection security"
- In reply to: karthik: "Re: sql connection security"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 30 Apr 2003 01:43:19 -0700
When one connects to the SQL server, they connect
with what one calls priviledges.
That means they can 'do' or 'not do' certain things.
hense the roles.
So when you connect as 'abc/abc' you can perhaps only
execute stored procedures. You cannot view tables, or
views. This way you cannot see the contents of the db.
Now if the execution of the stored procedures will give
you the answers you seek.. that's another issue.
this is what BP was trying to explain to you:)
Aris Routis
DBA/Senior Software Developer
Delta Singular
Greece.
>-----Original Message-----
>Hi,
>
>Thanks a lot on telling me about application roles. I do
have some
>clarifications though.
>
>If we have the concept of application roles, why should i
hard code the user
>name and password ?
>
>The user name that the user uses to login to my system
will become the login
>credentials for SQL Server, would it not ?
>
>That is if i login to my windows app as "abc" / "abc",
then my sql server
>user name would be "abc" / "abc", would it not ?
>
>Regards,
>Karthik.
>
>"BP Margolin" <bpmargo@attglobal.net> wrote in message
>news:e84PsitDDHA.2572@TK2MSFTNGP11.phx.gbl...
>Karthik,
>
>In general, if you provide a user with a login and
password then that user
>is going to be able to connect to SQL Server using that
login and password
>from any application ... Query Analyzer, Enterprise
Manager, MS Access, etc.
>
>However, you might check out the documentation on
"application roles" in the
>SQL Server Books Online. "Application roles" sounds like
it is what you
>want, except for the fact that it still is going to
require a hard-coding of
>the login and password somewhere ... in your code, in the
registry, or
>somewhere else ... so I don't know that it really helps
you achieve your
>apparent goal.
>
>But bottom line, with the exception of "application
roles", there is NO way
>that you can assign a user a login and password, and then
restrict that user
>only to your application.
>
>Well perhaps there is one, not necessarily fail-safe,
alternative. Assuming
>that you have coded your application using stored
procedures, you can grant
>EXEC permissions on the stored procedures, and DENY
permissions on
>everything else (tables, views, etc.). You can then make
sure that your
>application, when connecting to SQL Server sets the
"Application Name"
>parameter in the connection string, and then in each and
every stored
>procedure, use the APP_NAME ( ) function to verify that
the connection has
>the correct application name. The "catch" here is that
**any** application
>can set the "Application Name" parameter to anything it
wants, thus
>providing an end-run around this particular approach.
>
>-------------------------------------------
>BP Margolin
>Please reply only to the newsgroups.
>When posting, inclusion of SQL (CREATE TABLE ..., INSERT
..., etc.) which
>can be cut and pasted into Query Analyzer is appreciated.
>
>"karthik" <karthik@maximizelearning.com> wrote in message
>news:eTvNoTtDDHA.3072@TK2MSFTNGP11.phx.gbl...
>> Hi,
>>
>> I have a windows application that has a list of users
and their passwords.
>> All this information is stored in a SQL 2000 database.
My application at
>> present connects to SQL server via a fixed user name and
password say
>> test/test.
>>
>> This results in me hardcoding the value somewhere in the
code. What i
>> thought i could do is, create similar SQL users as the
one that is there
>in
>> the database. Like lets say i have a user called "abc"
with password "abc"
>> in my user database, i will create a similar sql user.
>>
>> That way my connection to the application will not have
hardcoded user
>names
>> and passwords.
>>
>> But what i need to make sure now is that a user should
not get access to
>sql
>> server directly... i mean just by connecting from one
enterprise manager
>to
>> my server's enterprise manager. How can i do this ?
>>
>> In short, what i need is that i have to make sure that
users in SQL server
>> should not be able to connect directly to SQL. They
should only connect
>via
>> my windows application. How can i achieve this ?
>>
>> And if someone can throw more light into the way i
authenticate users it
>> would be of great help!
>>
>> Thanks a ton!
>>
>> Regards,
>> Karthik.
>>
>>
>
>
>.
>
- Next message: Aris: "How do I audit insert/delete/update etc"
- Previous message: karthik: "Re: sql connection security"
- In reply to: karthik: "Re: sql connection security"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
