Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hasanali00
Posting Yak Master
207 Posts |
 Posted - 2004-11-08 : 08:52:33
|
| I am developing a web application that needs to access database. Currently, I am the owner of the database so I have all the permissions. Therefore, in order for my web app to connect to the database, should I create another database-user and then use that login details in the connection string.What permissions should I allow to this user; presumably, the web app needs to read and update the database tables.regards |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-08 : 09:18:58
|
| Yes, create a new user (or better still use Integrated Security so there is no UserID/Password in plain text in the connect string).You can then set the user to have a Database Role [for the specific database(s)] of db_DataReader / db_DataWriter - which gives the user permissions to read/write any table in the database. However, this is a bit of a broad-blanket approach, and it would be better to GRANT permissions to just the objects which are needed.Even better is to write Stored Procedures - then you give EXECUTE permission to the stored procedure, and you have NO Read/Write permissions directly to the tables - so if the userID/Password is compromised the person call still [say] delete every row in the database, but they can only do it one-at-a-time using the appropriate stored procedure!Kristen |
 |
|
|
hasanali00
Posting Yak Master
207 Posts |
Posted - 2004-11-08 : 09:25:06
|
| very interesting, I think I need to read more into stored procedures.Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-08 : 09:31:29
|
| If you do SQL development for long enough you'll wind up there, so starting there will save a lot of hassle downstream!Kristen |
|
|
|
hasanali00
Posting Yak Master
207 Posts |
Posted - 2004-11-15 : 07:13:36
|
| Does anyone knwo if we can create new users with a shared SQL server (hosted by another company).I am trying to create a new user for my DB without any success.in 'SQL Server Login Properties - New Login' window, I enter a new username and choose SQL Server Authentication option. Then enter the pasword, then specify my database. When I click OK, a new window pop-up asking for Old password and New password. What Old password do I enter here?? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-16 : 00:47:40
|
| Try doing it with SQL instead?EXEC sp_addlogin 'MyName', 'MyPassword', 'MyDefaultDatabase'Add the user to a Role, if you are using them:EXEC sp_addrolemember 'MyRole', 'MyName'Of allow Read/Write to all tables:EXEC sp_addrolemember 'db_datareader', 'MyName'EXEC sp_addrolemember 'db_datawriter', 'MyName'Kristen |
|
|
|
hasanali00
Posting Yak Master
207 Posts |
Posted - 2004-11-16 : 12:10:36
|
| Hi Kristen,I tried your appraoch as well, but again it did not work.So I contacted my hosting company. According to my hosting company, I cannot create additional users for my database.So what are my options now?Do I have to use my username and password (I am the owner of the DB) in the connection string, or are there other options??? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-16 : 14:19:30
|
| Ask the hosting company for a second UserID? Seems pretty daft if they only allow you to connect to your database as "God" because that would be bound to have serious security implications.For the Apps we create we create "User" and "Admin" roles. This allows client's users to conenct through the application, and their techies to connect using the Admin role -which is still restricted, but allows them to muck about a fair bit. Sounds like you need something similar - i.e. two "roles", or "User IDs", I reckon.Kristen |
|
|
|
hasanali00
Posting Yak Master
207 Posts |
Posted - 2004-11-22 : 06:34:07
|
quote:
Originally posted by Kristen
Even better is to write Stored Procedures - then you give EXECUTE permission to the stored procedure, and you have NO Read/Write permissions directly to the tables - so if the userID/Password is compromised the person call still [say] delete every row in the database, but they can only do it one-at-a-time using the appropriate stored procedure!Kristen
Where can I read more about this approach?thanks |
|
|
|
|
|
|
|
|
creating web users