Page 1 of 1
SQL Query
Posted: 17 Nov 2009, 14:59
by Monty
If someone wanted to have a very simple WAP site and wanted to restrict access to a certain group (based on a forum usergroup) -would they be able to do it with a simple username and password query box and compare it to the forum database?
I guess the code would be something like this (note, this is not proper code):
Code: Select all
Pseudo Code
username = x;
Password = y;
validate x & y;
check username permisions; //Checks username against usergroup names
return true - allow access;
return false - authorized users only;
Re: SQL Query
Posted: 17 Nov 2009, 15:02
by RuadRauFlessa
Monty wrote:If someone wanted to have a very simple WAP site and wanted to restrict access to a certain group (based on a forum usergroup) -would they be able to do it with a simple username and password query box and compare it to the forum database?
I guess the code would be something like this (note, this is not proper code):
Code: Select all
Pseudo Code
username = x;
Password = y;
validate x & y;
check username permisions; //Checks username against usergroup names
return true - allow access;
return false - authorized users only;
Yes if you make use of stored procedures which does the checking. And then pass the username to the stored proc with all the other parameters.
Then you can actually make the access transparent from the codes side.
Re: SQL Query
Posted: 17 Nov 2009, 15:09
by Monty
Thanks Raud!
Re: SQL Query
Posted: 17 Nov 2009, 15:19
by rustypup
never... never... send credentials in plain...
use the provided username and password to produce a comparable fingerprint, (even a simple MD5 hash).
secondly, don't confirm access via a boolean or bit switch... use an opaque code, or deliver a failed page...
Re: SQL Query
Posted: 17 Nov 2009, 15:27
by Monty
rustypup wrote:never... never... send credentials in plain...
use the provided username and password to produce a comparable fingerprint, (even a simple MD5 hash).
secondly, don't confirm access via a boolean or bit switch... use an opaque code, or deliver a failed page...
I kind of understood Raud's post (with much mental straining and lots of slow reading), but Rusty, you just shot way over my head
(well, mainly the bold bit)
Re: SQL Query
Posted: 17 Nov 2009, 15:53
by RuadRauFlessa
rustypup wrote:never... never... send credentials in plain...
use the provided username and password to produce a comparable fingerprint, (even a simple MD5 hash).
secondly, don't confirm access via a boolean or bit switch... use an opaque code, or deliver a failed page...
Passing the username does not mean it is in clear text. TBH it is more like passing a search parameter to a stored procedure on a SQL server over an encrypted tunnel to port 1433 on a SQL server. Nothing llike passing credentials. The user already has to be logged in. The only thing done is that the stored proc uses the, already authenticated, user's name to internally check access rights to specific data and then return what is relevant to the user's rights rather than returning everything.
psudo code..... customer search...... whatever...
Code: Select all
create stored proc with parameters userid, customernamewildcard
begin
create a table variable in which to temporarily store data in the memory of the server rather than using a temp table which will cause extra disk IO
create a cursor which will loop through the customers which is returned when searching for the customernamewildcard in the customer's name field
for each customer
begin
if user is allowed access in the customer's region
begin
add the customer to the table variable
end
end
return the contents of the table variable
end
Doing it this way ensures that the access rights is abstracted from the actual front end code. It is however still a bundle of extra work for you and the server which has to run the database. So unless you have plenty of time and you have a killer of a server it is generally not a good idea to go and do this.
But lets consider what the above will do....
If the user has rights in Gauteng and he runs a customer search the stored proc will only return what the user has rights to see. If the customer he is searching for is located in Limpopo then the customer won't be returned by the stored proc and thus the user won't see the customer.
Re: SQL Query
Posted: 17 Nov 2009, 16:07
by rustypup
opaque as in non-transparent... it's as easy to spoof standard success codes, (in standard HTTP/FTP traffic these are all in the 2XX range..), as it is to spot a boolean or bit flag...
better yet, serve up the relevant page based on a short-lived url mask, which goes some way toward foiling this type of spoofing... that way, whether login succeeds or fails, the user is exposed to something as benign and informative as
hKJKnkjjhg2HJHJKjkhkh8KLJHJK
process would be:
- :-post credentials fingerprint to server
:-server confirms credentials fingerprint
:-server creates temporary URL mask pointing to HOME, if credentials cleared or FAILED if not
:-server responds with temporary URL
@Ruad: serving a WAP site means it's going to be clear text at some point, hence my hesitation..
Re: SQL Query
Posted: 17 Nov 2009, 16:12
by Monty
I've just found out more information - the guys I was looking into this for aren't going to implement a system like this as not all the users of the wap site will be on the forum
Re: SQL Query
Posted: 17 Nov 2009, 16:15
by RuadRauFlessa
rustypup wrote:@Ruad: serving a WAP site means it's going to be clear text at some point, hence my hesitation..
+1
But I was under the impression we are talking about back end as he was asking about queries.
Re: SQL Query
Posted: 17 Nov 2009, 16:17
by Monty
RuadRauFlessa wrote:rustypup wrote:@Ruad: serving a WAP site means it's going to be clear text at some point, hence my hesitation..
+1
But I was under the impression we are talking about back end as he was asking about queries.
My heading may be misleading - I meant query as in question
Re: SQL Query
Posted: 17 Nov 2009, 16:20
by RuadRauFlessa
Monty wrote:RuadRauFlessa wrote:rustypup wrote:@Ruad: serving a WAP site means it's going to be clear text at some point, hence my hesitation..
+1
But I was under the impression we are talking about back end as he was asking about queries.
My heading may be misleading - I meant query as in question
Hehe hence me answering it from a SQL perspective.