Using C# and MySQL, is it possible to create individual user accounts to access the database, and only allow users to view/modify/delete their own data?
1
-
I guess the idea is hardening the application in case the C# code contains flaws. Therefore it may look like a good idea to establish the database connection as the user. One big problem I see here is, that you cannot just easily pool and reuse database connections like that. The connection of user Joe cannot be reused by user Jane. Establishing the connection on each user request can be costly (even when application and database run on the same system) and can completely stall your server when you are dealing with a lot of simultaneous requests.tiguchi– tiguchi2014-10-22 19:05:30 +00:00Commented Oct 22, 2014 at 19:05
Add a comment
|
1 Answer
In SQL you can grant privileges for users or user groups to all SIDU access types (Select, Insert, Delete, Update) on Tables and Views.
If you are talking about a finer granularity at the row level, I think you will need to store this information somewhere; the most direct way is to add something like an owner field and/or a privilege field to each Table you need to restrict.
Another way would be be to create one or more Tables which hold keys of restricted or of non-restricted records or contents, like projects or regions..
The first solution would be not terribly hard to code, provided you have a well-designed concept of ownership & privileges, sufficiently flexible both for now and for later on.
You would add a layer of access logic, which adds an extra where clause, in the simplest case like: ..AND WHERE table.ownerID = @ownerid or the like to every SELECT. And for every INSERT it would fill the field with the current user ID or group ID and/or his current access rights..
Plan for user groups, as one user will often need to do the work of another one, at least partially..
Extra privileges-tables will need even better planning..
Also consider tools & functions to manage these data..
Update
Since it is clear now, that you want to set up a Web based data access you could either create separate Tables for each user with the user name or a hash or encrypted version of it, used as a prefix to each Table; or, more common, you can set up a separate DataBase for each user, again, with a DB name somehow derived from the user name or user ID.
The versiom with a separated DB for each user is easier to write, since after the login, everything will work the same for each user, i.e. no need to inject anything into the SQL.
The only possible consideration may come in with technical or monetary restrictions coming from the provider. Some packages only allow for a small number of DataBases. In such a case the Table-prefix method is the next most likely solution.
So the number of users you expect may be a deciding factor, too..
14 Comments
CuriousGeorge
I'm still a bit confused.. basically, use user gets a copy of my software. In the software, there should be a create user button. That button should create a table named "username_data" or something. From that point on, that user should only be able to query/modify/delete that table. They should not be able to modify/view/delete any other table in the database that they did not create with their username/password.
TaW
OK. What do those users share? The same DataBase?
CuriousGeorge
Someone commented, then deleted their comment, but mentioned "stored routines" or something. After some quick googling, it sounded like I could have someone log onto the database as a guest, and only have access to one stored routine called "create user" which they could call to create a user and a table, which would be the only thing they had access to from then on... does that make sense?
CuriousGeorge
Yes, they share the database, but can put their own tables in it
TaW
Will they all use the same Tables? In that case could prefix the Tables with the username or id like: 'bitwise_products' etc.. and use prefexd names in all SQL statements.. I don't know about "stored routines" yet..
|