It is currently Sun Dec 16, 2018 7:40 pm




Post new topic Reply to topic  [ 12 posts ] 
Problem with [username] filtering 
Author Message

Joined: Thu Aug 07, 2008 12:39 pm
Posts: 69
Post Problem with [username] filtering
The idea of allowing pages to filter on [username] is on the right track, but misses something important. I have several companies in the database, and each may have a dozen users. My table of log-in permissions has the login name, password, and also the CompanyID (int) that they belong to. I then need to filter the pages for the CompanyID, not the username.

What I need to do is filter based on a global variable that is the CompanyID associated with the username.

Can I change the value of [username] after the user logs-in to make it the CompanyID?

Can I make a global variable called "CompanyID" (int) and access everything via Query that has an added "WHERE MyTable.CompanyID = [CompanyID]" on it?

Is there any such thing as a "global variable" that I can set in one place and refer to in WHERE clauses throughout the session, as well as to use when INSERTING records?

HELP!

Chris


Wed Feb 25, 2009 6:42 am
Profile E-mail

Joined: Thu Feb 26, 2009 4:51 am
Posts: 3
Post Re: Problem with [username] filtering
Hello, did you ever find a solution to your issue?

I have similar situation where I need users that login using username/pass to only see records for their company. I assume this requires a query after the login is initiated.

Please drop a line if you found any good information.

Many Thanks!
Robert.


Thu Feb 26, 2009 5:17 am
Profile E-mail

Joined: Thu Aug 07, 2008 12:39 pm
Posts: 69
Post Re: Problem with [username] filtering
Will do! The idea of a filter is a good one, but username is not (usually) the best thing to filter on.

What would be great is a project-wide filter on a global (session-scope) variable. When someone logs-in, you look-up their company ID and put that in the global variable. Then all pages are filtered on that variable.

For now, I will have to create a whole new database instance for each new customer system, even if it has only a very small amount of info. That's a lot more "messy" and space-wasting.

Chris


Thu Feb 26, 2009 5:35 am
Profile E-mail

Joined: Thu Feb 26, 2009 4:51 am
Posts: 3
Post Re: Problem with [username] filtering
Hi Chris,

This is exactly an option I am considering, although its "bad form" for normal coding practice.

I'm going to try and open a help ticket and see what the folks at Qwiksite have to say.

Thanks for the reply and I'll let you know what I find.

--Rob.


Thu Feb 26, 2009 6:50 am
Profile E-mail

Joined: Sun Apr 13, 2008 9:13 pm
Posts: 25
Post Re: Problem with [username] filtering
I have overcome this problem with one of my projects, but I had to put custom code in the login program. I wrote the custom code outside of dbqwiksite and require the user to enter a company number, user name, and password. I then set company number to be the user name (as far as dbqwiksite is concerned) to enable the filtering.

Be aware that dbqwiksite user name filtering does not prevent a user from displaying a record that belongs to another user. If they play with the url they can access any record in the file. I had to manipulate all select statements (including drop down lists) to include a "where company number = user name" clause.

Please post any solution you might derive if it's a better answer.


Thu Feb 26, 2009 9:39 pm
Profile E-mail

Joined: Thu Feb 26, 2009 4:51 am
Posts: 3
Post Re: Problem with [username] filtering
This is the answer I received from tech support:

"Hi Robert

There is a way to handle it but it requires some custom coding, which in turn requires to use the 'Developer Edition'. With the developer edition it would be possible to add the code to controls this requirement. Thank you. "

With the dev edition at $300 usd, it may be worth the expense considering I'm trying to do some of the more pedestrian code in dbq rather than Jdeveloper or other such thing.

Looks like your custom code solution is pretty close to what dbq is recommending. Either way, its going to require some coding.

Big thanks to everyone who contributed to this thread. Greatly appreciated!!! --Rob.


Fri Feb 27, 2009 5:28 pm
Profile E-mail

Joined: Fri May 09, 2008 5:38 am
Posts: 271
Location: Texas USA
Post Re: Problem with [username] filtering
I only have the DEV version so I cant answer specific questions about what the other versions will do but let me have a stab at this

1st off the company id has to be available on every data page either directly from the main table for that page or by a join to another table.

Company id also needs to be on the dbqwiklogin table (or what ever table you set up for login)

After you have that add a join to each data page query where the company id = company id on the login table.

Visual Query builder makes this very easy.

If this doesnt help please post some specific examples of your tables and maybe I can be more specific (or someone smarter than me can :P )


Tue Mar 03, 2009 9:24 am
Profile E-mail YIM WWW

Joined: Fri May 09, 2008 5:38 am
Posts: 271
Location: Texas USA
Post Re: Problem with [username] filtering
I have never used this option but maybe you could hijack category to use as a 'second' filter? It doesnt provide a level of security just filtering, so not sure that meets your requirements. Most of the companies I work for would never allow their data to be mixed in with data from other companies so it is not an issue I have had to figure out just yet. Good luck.


Tue Mar 03, 2009 9:31 am
Profile E-mail YIM WWW

Joined: Tue Feb 10, 2009 5:19 am
Posts: 24
Location: Turkey
Post Re: Problem with [username] filtering
I am currently using a similar issue which is a lesson learned tool in the package. Users can address the lesson learned to a department and only department heads can view it. In another words my user name and department name must match.

This case, my user name your user name
The department is the company name/ID of your costumers

I created an sql query as in the attached picture, it looks like it works fine for me. However, I have not extensively tested it. I tested it with a few quick and dirty data only.

Please get back to to me if it helped.

Regards,

http://www.kordil.com


You do not have the required permissions to view the files attached to this post.

_________________
http://www.kordil.com
kordil


Thu Apr 09, 2009 2:29 am
Profile E-mail WWW

Joined: Thu Aug 07, 2008 12:39 pm
Posts: 69
Post Re: Problem with [username] filtering
Gentlemen - I appreciate all your input on this, but I still have the quandry. I need to be able to establish a customer account. Only the his own records should be accessible to the customer who is logged in. There are two ways to do this: One is a separate database instance for each customer, and the other is some sort of global filtering so that one instance of the database can be shared.

My question is, how do people USUALLY do this? For example: I have a Vonage account. When I signed-up, I got a black-box to plug-in and also a log-in on the web site that lets me see my configuration, billing, calls and features. Do you think that they spin a separate database instance for each customer account? Seems "bulky" somehow - but maybe it's the best way?

That raises another question: How can I make a dbQwikSite application spin a new database instance when a customer signs-up and provides a username and password? Actually, one customer could have multiple users with passwords.

So if I use a unique database instance for each customer account, I have to let my ecommerce site cause new instances to be created. I think I can write SQL SPROCs that can do that. So I am back to my other question: How can I put a button on a page that executes an SPROC? There has GOT to be a way!

Thanks, fellas.


Sun Jun 28, 2009 6:31 am
Profile E-mail

Joined: Tue Feb 10, 2009 5:19 am
Posts: 24
Location: Turkey
Post Re: Problem with [username] filtering
Comments are red, and tried to answer as much as I know.

topherfox wrote:
Gentlemen - I appreciate all your input on this, but I still have the quandry. I need to be able to establish a customer account. Only the his own records should be accessible to the customer who is logged in. There are two ways to do this: One is a separate database instance for each customer, and the other is some sort of global filtering so that one instance of the database can be shared.



My question is, how do people USUALLY do this? For example: I have a Vonage account. When I signed-up, I got a black-box to plug-in and also a log-in on the web site that lets me see my configuration, billing, calls and features. Do you think that they spin a separate database instance for each customer account? Seems "bulky" somehow - but maybe it's the best way?

I believe this is just a user name filter, 1 project 1 database.

That raises another question: How can I make a dbQwikSite application spin a new database instance when a customer signs-up and provides a username and password? Actually, one customer could have multiple users with passwords.

The way is the SQL, SQL acts as if it creates NOT A DATABASE a NEW TABLE IN DATABASE. In practicle, and normal conditions 1 database is sufficient for even large sites. Using a user filter does the same think you want


So if I use a unique database instance for each customer account, I have to let my ecommerce site cause new instances to be created. I think I can write SQL SPROCs that can do that. So I am back to my other question: How can I put a button on a page that executes an SPROC? There has GOT to be a way!

Based on above comments, for better understanding can you revise your questions again. I see you. As mentioned you should have one table for all costumers with an ID and User name. That user name can be filtered when he reviews his own profile by creating a new group.

Thanks, fellas.

_________________
http://www.kordil.com
kordil


Sun Jun 28, 2009 8:35 pm
Profile E-mail WWW

Joined: Thu Aug 07, 2008 12:39 pm
Posts: 69
Post Re: Problem with [username] filtering
Thanks for all the responses! My situation is not really "one project = one user name", because one "customer" could have many users, each with their own log-in credentials.

I have about decided that I should spawn a new database for each customer. Using the "model" database I can make a template for new accounts and then create a new DB for each customer.

I have been told that SQL Sevrer 2005 will support thousands of databases (limited by space of course).

I think this method provides the security and isolation of data that is required.

Now I need to learn to use the ecommerce stuff to allow people to purchase hardware and also buy an account, which is a database, on the server. Hmmm... :-)

Thanks again!

Chris


Sat Jul 25, 2009 1:37 pm
Profile E-mail
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 12 posts ] 


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
cron
Copyright © 2004-2008 TheDevShop Ltd. All Rights Reserved