|
Hi chaps,
I've got a question about security. I understand that you don't want a user to be able to trash your database, so parameterised SQL / Stored procs are the sensible way of doing things. But i'm wondering more about permission based security, and at what locations it should be checked.
Lets say you have a web app, you would security check the following at the back end:
* View Record
* Perform Action
But what structure would you put in place to validate functionality on the Layer side of things and catch things further down the chain? Would this have to be performed on a per function basis? In which case, would it prohibit the use of ORM / Full Record Updating and merely refine the database access down to a rigid API?
Cheers
Tris
-------------------------------
Carrier Bags - 21st Century Tumbleweed.
|
|
|
|
|
Normally you have two choices when you connect to a sql database. Sql user and NT authenticated user. You can do both of these with a web app. Either way you should only have one user. It is in your best interest to have that one user only have rights to execute stored procedures. So if you want to select something, call a stored procedure. If you want to insert update delete something, call a stored procedure. That way if your security gets compromised you limit your exposure since that user can only execute stored procedures. It may seem like more work, but in the end it really makes things a lot more secure and you know exactly what your web site can and can't do since all the code is in the stored procedures.
I am not sure if that answered your question.
Ben
|
|
|
|
|
Hi Ben,
I'm more interested in providing a security saftey net in each layer of the application. My problem is that, using full table insert / updates, even with stored procs, the only place to reliably catch illegal incoming data (based on permissions), is on post back. In the event of a security loop hole, i'd like to have something that can catch it in the database layer, which would probably involve an ACL, in which case, IF a user has privelages for that particular record, how do you determine that they have for instance, unlocked a locked thread that should be locked.
Cheers
Tris
-------------------------------
Carrier Bags - 21st Century Tumbleweed.
|
|
|
|
|
Are we talking about database access here or have we moved on to a different subject? The most secure web app to sql access I can think of:
Create a new app pool for your web app.
Create a windows authenticated username and password.
Set the identity of the app pool to be that windows user.
Set your sql server to authenticate via windows authentication only, no sql users and passwords.
Only give stored procedure rigths to that windows user that the app pool runs as.
So at this point there isn't any way that anyone could get the password of that windows user since it isn't stored anywhere. It isn't in the connection string etc.
The web application runs as that identity, but it is just a normal windows user that only has access to stored procedures on the sql server. So at that point I don't know what else you want. It seems pretty secure to me, but then again perhaps I am missing something.
Ben
|
|
|
|
|
Hi Ben,
Thanks for the reply, but it wasn't a question on securing web apps in the traditional sense, but on a 3 tier architecture where the Database has its own permission table above and beyond an authenticated user.
The Web app would have its security checks, which it determines by querying the DBLayer security functions, then when invoking a function in the DBLayer it would internally query the security functions and if the permissions are not valid, raise an exception. That would make 2 security checks, as opposed to one.
This would however make it difficult to use full table access with raw CRUD, and instead limit any manipulation to fine granularity stored procs.
I was just wondering about the practicality of that.
Tris
-------------------------------
Carrier Bags - 21st Century Tumbleweed.
|
|
|
|
|
Not very practical. In all the applications I have done recently we have several layers. The bottom line is still the same. In some layer you have a connection string to the sql database. You really have two options for that connection string, sql user and nt authentication. You have modes you can run your sql server security in. Mixed allowing both windows authentication and sql users and only windows authentication. This is where your security is. I don't know what you would try to add your own second check on security on top of that. If you have an avoidance to using only stored procedures to improve your security because you think it is too much work, in the end you are making more work by trying to add your own security and it probably won't be that much more secure.
I would go with what sql server can provide you since that is what most people are doing. That way when someone has to support code you have written, they can understand quickly how the securty works, verses they need to learn this custom system you wrote that will probably be a nightmare to support.
Ben
|
|
|
|
|
Hi Ben,
Using an ACL, authenticated and authorised users could both view / edit / delete, however, normal users can only edit / delete their own unlocked records, not edit / delete someone elses records, OR their own records if they are locked, and an admin user could do anything.
Alternatively, a normal user could not Lock / Unlock any records, wheras an admin user could.
Therefore, merely being authorised on the top level via windows / db security would not be enough to stop a logged in user from deleting any records, and further permission checking would be required.
This isn't about the ability to access the database, but the ability to restrict certain sets of functionality on the database whilst logged in. I am already using a role based system, but was looking to make it slightly deeper than just testing the user roles when recieving a HTTP request.
The problem here is that, if a table contains certain fields that only an admin user should be able to change, and i am running a full record CRUD system (I think thats table direct), it would be an easy matter to enable a normal user to perform admin tasks by mistake, and not have a way to test this via permissions.
-------------------------------
Carrier Bags - 21st Century Tumbleweed.
|
|
|
|
|
If you are talking about a web application your web app would have to be internal only NOT on the internet if you wanted it to use windows authentication. I would still choose to use a forms base authentication which means your web app has one identity for all. If you have one identity you don't have the issues you are talking about above since you can very clearly control what access that web app identity has.
If you are talking about a windows app, sure things can be different, but you should approach windows and web app differently.
Ben
|
|
|
|
|
I think we are talking about 2 different things mate. 
I use forms authentication for my web apps, with a user specific connection string to the application DB. It's not a problem with the web app identity, but with authenticated users being restricted on specific records dynamicaly according to a set of dynamic rules and asp.net authorisation roles.
T
-------------------------------
Carrier Bags - 21st Century Tumbleweed.
|
|
|
|
|
I want to update a table directly with the help of dataset. I want to pass a dataset as a parameter to sql server.I dont want to iterate a dataset in the front end. Because i have to update a nearly 1 lac rows in a single data base operation. I am using Sql server 2005 as a back end and C# language for development. If is there any solution please update me.
sankargmca
|
|
|
|
|
There are many data access layer tools , so I do not know where to start . I just want to get out of repetitive boiler plate coding . But I have a few requirments:-
1) Works with MsSQL . ( Virtually all should)
2) Produce C# code (Again - all should)
3) Must produce a data object that maps onto a row of a table .( This has been the stumbling block)
4) The data object must have no references to any DB/connection . I.e pure data . Maybe a few Ctors , ideally a struct , but if a class must support ICloneable .
5) Icing on the cake is if the data object overrides = in order to make a value comparison ( I know MS advise against it but its useful).
6) Collections of the data objects would be nice
Why the 1:1 row to object ? Well I found over the years that if I put all my effort into a normalised DB then the code almost falls into place if I produce a 1:1 row - > object . Then I use an object that maps to a table or stored procs in order to communicate with the underlying tables . Not haveing any reference to the DB in the data object means it is as light as possible in the GUI
|
|
|
|
|
Do a search for LLBLGen and download the free version. You'll have to do some coding changes to take advantage of technology advances, but I believe it will give you most if not all that you need. I added a generated method of my own to the code PopulateBy(DataRow row) since all of the selects it generates returns a DataTable. This keeps the use of the data at the row level.
|
|
|
|
|
Please how can I pass input/output params and execute an stored procedure from within an C++ code ? Is it doable with CDatabase and CRecordset MFC classes and a random database source already configured with ODBC ?
Thank you all.
|
|
|
|
|
Hi to all
I have a problem with database in vb.net using sqlexpress.
My problem is:
I have created a .mdf database file and tables in it, and written code for inserting data to this table through command object. But my real prob is when i supply the connection string which is created by the "data configuration wizard" to this cmd object then on runtime insert executes and also data is visible in datagridview but when i check the data in the table it is not there and all fields are NULL.
given connection string.
("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\LIB.mdf;Integrated Security=True;User Instance=True")
But on the otherhand if i give connection string as ::
"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\LIB\LIB\LIB.mdf;Integrated Security=True;User Instance=True"
then data is successfully inserted in the database and i m also able to view it in my database table.
So if Any one can help me it will be highly appreciable.
Thanks.
|
|
|
|
|
Is'nt this a problem of relative Vs global paths ?
I give you an example I know: if you call GetCurrentDir within a code that you execute through Debug-->Start Without Debugging command then you'll have something different as when you go to the Debug directory of your executable and run it from there.
Thus this may lead to different behaviours as when we have relative entries, such that things will work fine, in one case, and fail in the other.
Ahmed. Tunisia.
|
|
|
|
|
I remember this being much shouted about way back, but can't remember the correct term for doing this to search on. Can anyone remind me please?
|
|
|
|
|
|
Accessing database file (.sdf) after changing extension (say .abc) causes error in connection string.
Hi,
I have requirement of creating database .sdf file with some different extension say .abc.
If I do so and in connection string if I try to give the string as
Dim strConnectionString As String = "C:\SQLCEDatabases\DBFileName.abc; Password ="ABCD123";"
Then it causes an error/Exception saying
"Unknown connection option in connection string: C:\SQLCEDatabases\DBFileName.abc; password."
Can I get the solution by which I can access the database file after changing its extension and accessing it through code (C# or VB.NET)
Thanks & Regards
Manish Jape
|
|
|
|
|
CListCtrl m_table;
CDaoDatabase db;
db.Open("D:\\ktt.mdb");
short n_table=db.GetTableDefCount( );
CDaoTableDefInfo TableInfo;
for( int i=0; i
|
|
|
|
|
how can i remove the system objects such as MSystemQueries,... in the list
|
|
|
|
|
Hi..
i need sql query for the ollowing requierment
input -anydate (9/05/2007)
output-given dtae presents which quater(2 quater)
Revathi Raj
|
|
|
|
|
|
I have created reports using reporting services and also created reportview control in my web applicaion.When i select the report from the dropdown it displays correct report but why it still shows the report paramter of the report even if i slelect some other report from drop down.Pls let me know how i can implement this that when user select some other report before clicking on the report button it should diplay blank page.It should not show the parameters of some other report.
|
|
|
|
|
I have a small search function that searches a telephone database on e.g. surname, suburb, and tel no. The data source is two tables, TelPerson and TelNumber, with one TelPerson record to many TelNumber records. I'm battling a bit with my search SQL, which joins the two tables to allow a search on person details as well as on phone number, but I only want one record returned if the search is on person details only.
For example, Investec Bank have 419 telephone numbers, but if the user searches for 'Investec', I only want to return one record, but if the user also includes a telephone number search criterion, I want to show all the records for Investec where the number matches.
|
|
|
|
|
Its a bit ugly, but I think the following should do the trick:
select P.Name, T.PhoneNumber
from TelPerson as P
inner join TelNumber as N
on N.PersonId = P.PersonId
where (@SearchName = '' or P.Name LIKE '%' + @SearchName + '%')
and (N.PhoneNumber = @SearchPhone or (
@SearchPhone = '' and
N.PhoneNumber in (
select min(PhoneNumber) from TelNumber where PersonId = P.PersonId)
))
order by P.Name, P.PersonId, T.PhoneNumberThis assumes that a blank-string equals no criteria. If possible then I would recommend using a stored procedure to make the code more logical.
Regards
Andy
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
