Dear All
How is it possible to give a Java application sufficient permission to
create tables in SQL Server 2005?
Thanks
DanielDaniel Malek (DanielMalek@.discussions.microsoft.com) writes:
> How is it possible to give a Java application sufficient permission to
> create tables in SQL Server 2005?
You cannot give an application per se permission to do anything. The ways
to go in your case are:
o A stored procedure signed with a certificate, associated with a user
that has the privs.
o A stored procedure with EXECUTE AS.
o Use an application role.
o Authenticate users in the middle layer, and connect to SQL Server with
proxy login that has the required privs.
I discuss these methods in different levels of details on
http://www.sommarskog.se/grantperm.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Showing posts with label tocreate. Show all posts
Showing posts with label tocreate. Show all posts
Sunday, February 19, 2012
Giving access to outside web site to SQL Server stored procedure
Hello, I have a sql server 2000 production database that will need to have
multiple web sites insert contact information into a table. I plan to
create a new sql user and only give it permission to execute one stored
procedure (that will do the insert row into the contact table).
Then I will provide to any outside vendor the SQL Server name, database,
user id, password and stored procedure(and input parameters) name. What are
the security concerns with this?
Would it make any sense to create a database with only this table in it, or
is this just overkill. I see that when I assign a new user to just one
database and one stored procedure that they can see the master database. Is
this an issue? Should I give the user
db_denydatareader on the master database?
Thanks
DonDon (dons100@.ameritech.net) writes:
> Hello, I have a sql server 2000 production database that will need to have
> multiple web sites insert contact information into a table. I plan to
> create a new sql user and only give it permission to execute one stored
> procedure (that will do the insert row into the contact table).
> Then I will provide to any outside vendor the SQL Server name, database,
> user id, password and stored procedure(and input parameters) name. What
> are the security concerns with this?
> Would it make any sense to create a database with only this table in it,
> or is this just overkill. I see that when I assign a new user to just
> one database and one stored procedure that they can see the master
> database. Is this an issue? Should I give the user db_denydatareader
> on the master database?
I would rather not expose an SQL Server on the Internet at all, least of
all SQL 2000. SQL 2000 no protection against brute-force attacks whatsoever.
Even if you have a really strong password for sa, someone might be able to
crack it. The server would also be open for denial-of-service attacks.
I think you should better implement some sort of web service. Not
necessarily with XML. But something that listens on a TCP/IP port, possibly
authenticates the user, and then connects to SQL Server with this low-
priv user you described. Username and password would be hard-coded into
this service, or read from a file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
multiple web sites insert contact information into a table. I plan to
create a new sql user and only give it permission to execute one stored
procedure (that will do the insert row into the contact table).
Then I will provide to any outside vendor the SQL Server name, database,
user id, password and stored procedure(and input parameters) name. What are
the security concerns with this?
Would it make any sense to create a database with only this table in it, or
is this just overkill. I see that when I assign a new user to just one
database and one stored procedure that they can see the master database. Is
this an issue? Should I give the user
db_denydatareader on the master database?
Thanks
DonDon (dons100@.ameritech.net) writes:
> Hello, I have a sql server 2000 production database that will need to have
> multiple web sites insert contact information into a table. I plan to
> create a new sql user and only give it permission to execute one stored
> procedure (that will do the insert row into the contact table).
> Then I will provide to any outside vendor the SQL Server name, database,
> user id, password and stored procedure(and input parameters) name. What
> are the security concerns with this?
> Would it make any sense to create a database with only this table in it,
> or is this just overkill. I see that when I assign a new user to just
> one database and one stored procedure that they can see the master
> database. Is this an issue? Should I give the user db_denydatareader
> on the master database?
I would rather not expose an SQL Server on the Internet at all, least of
all SQL 2000. SQL 2000 no protection against brute-force attacks whatsoever.
Even if you have a really strong password for sa, someone might be able to
crack it. The server would also be open for denial-of-service attacks.
I think you should better implement some sort of web service. Not
necessarily with XML. But something that listens on a TCP/IP port, possibly
authenticates the user, and then connects to SQL Server with this low-
priv user you described. Username and password would be hard-coded into
this service, or read from a file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)