| Author |
Topic |
|
global_erp_solution
Starting Member
28 Posts |
 Posted - 2012-11-08 : 02:48:24
|
| I have an online shopping app. right now it only has 5 registered users:myself with admin privileges and 4 others as customers (Bill, John, Chen and Linda).So in this case, the app recognizes 5 different user, while the database only has 1 user : admin. all five users logs in to the database as admin. my questions are:1. is this good practice?2. do I need to also create credentials for each app users, or do I need to only create two user : admin (which is me) and customers (the other 4 users)3. I want to create a view where the customer can only see his/her own order history. Bill can only see Bill's order history, not Linda's not Chen's not anyone. how do I achieve this using view? parameterized view?thanks |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-08 : 06:03:42
|
| All five users should NOT have admin privileges. That is highly discouraged practice.As to your question on whether to create two users - admin and customer, or whether to have each user their on database login:I have seen applications that manage their own users and connect to SQL Server using a single database user (with limited privileges), but those are usually large applications from companies with a lot of resources to pour into it. Think about all the things you will have to manage - adding/removing/maintaining users, keeping passwords secure, etc etc.So my choice would be to let SQL Server do all of that. Give only the minimum required permissions to each user. But, don't grant the permissions on a user level. Create a role for customers, grant permissions to that role and add make the users members of that role.Another alternative would be to create an AD group for users, create the SQL Server login for the AD group and grant permissions to that group. That works well when you want the Windows sysadmins to manage who should have access and who should not. For online shopping applications that may not work very well (or it might - but I have not thought through what is involved). |
 |
|
|
global_erp_solution
Starting Member
28 Posts |
Posted - 2012-11-08 : 08:07:17
|
| 1. what is AD? ad-hoc SQL?2. you said that you'd prefer to let SQL server to do all the user management. what does that mean? two-role approach (admin and customer roles)? 3.what permissions should I grant? create table is obviously not permitted. but deleting, inserting, updating and reading must be granted, but with record filtering. 4.by the way, is there a list of permissions that can be granted/revoked to each roles?5.and about the parameterized view, is that possible in sql server? or should the application handle the filtering, not the DB?thanks |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-08 : 08:26:52
|
| 1. AD is Active Directory which is used for security and administration in windows networks. It allows the creation of groups which can then be granted privileges - which will give the same privileges to members of the group.2. If you create just one proxy user and then allow access to your database through that proxy, you will need to do something to manage the human users who are allowed to access your app. So you will need to manage their usernames, passwords, password expiry and all kinds of security related issues. But if you create one database user for each of your human users, the passowords and all security related aspects can be handled by the plumbing built into SQL Server.Having said that, I must admit that I don't know what the recommended pattern is for shopping applications where total strangers would be allowed to shop at your site and there can be thousands or hundreds of thousands of users.3. Regarding permissions, each object can have a different set of permissions - tables can have select, update, insert, alter etc. Stored procedures can have execute, alter, view definition etc. http://msdn.microsoft.com/en-us/library/ms188371.aspxYou should handle filtering in the database. What I would recommend is to limit access to your database through stored procedures and grant only execute permissions to the users/role. The stored proc can have parameters, one of which should be the id/name of the user. Then you can control what is returned based on that parameter. |
|
|
|
global_erp_solution
Starting Member
28 Posts |
Posted - 2012-11-08 : 08:50:47
|
| so, basically since it's impossible to create parameterized view, I should mimic this behaviour by using SP (which I know can accept parameters) to return the rows.let's focus on "placing an order" part of this app. to place an order, a guest must register and then become a user (so, in the future, I already establish the distinction between "passing by guests" and "user a.k.a customer"). I conclude from your post that there are two ways to filter the rows : let the apps do it, or let the DB do it. 1. I assume that the app does filtering by constructing the SQL string on the fly and send the string to be executed by DB. is this correct? or do you have another way for the app to do the filtering?2. and for the second approach, since parameterized view is no possible in sql server, I must resort to SP with params that returns the filtered records to mimic parameterized view.Is this correct? thanks |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-08 : 10:33:10
|
When you said "app doing the filtering", I was thinking about bringing the data for all users into the client application and then the client doing the filtering. That is not a good idea.Constructing a SQL query including the parameters is not a good idea either. SQL injection risk, less opportunities for SQL Server to reuse query plans etc.So use stored procedure with parameters. From the client code, you would send a query something like this to the server:EXEC dbo.GetOrders @startDate = '20120101', @endDate = '20120131', @userId = 'jsmith1234'; The user id would be something that you have in your code based on the login credentials etc. |
|
|
|
global_erp_solution
Starting Member
28 Posts |
Posted - 2012-11-08 : 11:31:37
|
no, what I meant about app doing the filtering is the application code will further filter the rows returned by server after app has sent query string for the server to execute. so it's like this:getData(String sqlString){ Records records = con.execute(sqlString); records.filter('customerID=' + User.username); return records;}this is just a pseudocode. this is what I had in mind when I mentioned the app doing the filtering, with "records.filter" and then returning the filtered records to client. I'm trying to verify whether my own "best practices" are actually best practices. so your opinion is : the best way to do this is with parameterized SP? but I read in google that SPs are evil and must be avoided unless there's absolutely no alternative. I'm quite confused now. thanks |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-08 : 11:58:18
|
quote:
but I read in google that SPs are evil and must be avoided unless there's absolutely no alternative
The person(s) who gave you that advice is giving you VERY BAD advice. Using stored procedures is the preferred approach - for a number of reasons - security, performance, maintainability etc.You don't have to take my word for it - I consider myself to be just one or two steps past "wet-behind-the-ears" stage when it comes to expertise on SQL Server. Start a new thread and ask; there are very many people on this forum who have deep knowledge of and insights into SQL Server. They may not read this particular thread because they may look at the number of replies in the thread and think that this has been answered. |
|
|
|
global_erp_solution
Starting Member
28 Posts |
Posted - 2012-11-08 : 12:08:55
|
| wow, "wet-behind-the-ears"? I assume you've read the article I read:http://www.tonymarston.co.uk/php-mysql/stored-procedures-are-evil.htmlok, could you please give me the link to those threads refuting the points described in the blog. because I read that blog about a month or two ago, and I feel like I need to revisit my coding standards. Right now, the point about introducing logic to storage layer and vendor lock-in is quite irrefutable. I'm not trying to start a debate or anything, just want to hear both sides and then finally need to decide how my coding standards in the future would be. whatever it will be, it's better to confirm the best practices now since the app is still in its early development stage. thanks a lot. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-08 : 12:29:54
|
| Funny, I hadn't read that article :)I posted a question in a new thread, in the New To SQL Server section. Hopefully some of the experts on the forum would comment.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=180489To be clear, I am not a fan of triggers - to the contrary, I try to avoid triggers and use it sparingly and only when absolutely needed (for example auditing purposes). But I don't subscribe to the assertions in that article regarding stored procedures. |
|
|
|
global_erp_solution
Starting Member
28 Posts |
Posted - 2012-11-08 : 12:50:00
|
| wow, thank you very much for the support boss. and it's got a reply already. reading whitefang's post now. thanks again. |
|
|
|
global_erp_solution
Starting Member
28 Posts |
Posted - 2012-11-09 : 00:33:24
|
| the thread you mentioned was a bit "lively".one last question, if I decide to go with SPs, is it good practice to use SPs for all CRUD operations?so let's say I have Order and OrderLines table:1. for insertion --> SP createOrder with params of all the fields in Order table. and I'm wondering about the OrderLines table. how do I pass the parameter? do I need separate SP for OrderLines? and how do I execute it? from inside SP createOrder or call createOrder and createOrderLines from application code?2. for update --> SP called updateOrder also with params of all the fields in Order table3. deletion --> SP deleteOrder with param with just one param : orderID4. read --> now this is different. I'm going to use view instead of SP, and then the returned rows will be filtered by app like I showed in my previous post (records.filter).is this good practice?thanks a lot. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-09 : 06:49:39
|
| I usually give access to regular users ONLY through stored procedures, for all operations.For insertions where you may need to send a table full of data, one option, of course, is to send one row at a time as parameters to the stored procedure. But there are a number of other and perhaps better ways to do it - see this article by Sommarskog for a run down: http://www.sommarskog.se/arrays-in-sql-2008.html There are older articles that he refers to in their - all of them are well worth reading. |
|
|
|
|
user management