• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

complicated mySQL statement

I am creating a login,  so I have a login form with three fields, companyId, username, password. customers and companies can both have one to many. which is why I have the affiliation table becasue he can have access to more then one company and he could have been granted or revoked permissions to access information from different companies.
I need to make sure that the query will not just login to any company cause he knows the ID and his username and password for his user profile are correct the record in the affiliation connecting the user and the company together and he must be granted access.

Thanks

my tables are as follows:

companies
companyId
allowAccessId

users
userId
username
password

companyUsersAffiliation
id
companyId
userId
allowAccessId

sysAccess
id
description (either active or inactive)
allowAccess (either true or false)
0
prowebinteractiveinc
Asked:
prowebinteractiveinc
  • 4
  • 2
1 Solution
 
lwadwellCommented:
What is your question?
0
 
prowebinteractiveincAuthor Commented:
looking for help on building the sql statement
0
 
lwadwellCommented:
>> I need to make sure that the query will not just login to any company
If you are looking to verify whether the userid and companyid are valid combinations ... as I am unaware of the programming language and methods you are using to call MySQL I will use the generic @username, @passwd and @companyid as variables in the SQL, try
SELECT 1
FROM users 
INNER JOIN companyUsersAffiliation cua ON users.userId = cua.userId
INNER JOIN sysAccess ON cua.allowAccessId = sysAccess.id AND sysAccess.allowAccess = 1
WHERE users.username = @username
AND users.password = @passwd
AND cua.companyId = @companyid

Open in new window

This also assumes that sysAccess.allowAccess is a numeric datatype with 1 meaning True.  It returns only the value 1 as it would only be an existence check ... if a row is returned, allow access; if no rows, deny access ... if more than 1 - there is is something wrong with the data in the tables.  If you need values returned ... add them to the SELECT.

Personally I think the use of the sysAccess is not necessary and that instead two columns into companyUsersAffiliation are added instead of allowAccessId.  They being grantAccessDate and revokeAcessDate (both date datatypes).  This allows the full history of access to be logged and access to be more finely controlled by date ranges.  The above SQL would then become:
SELECT 1
FROM users 
INNER JOIN companyUsersAffiliation cua 
ON users.userId = cua.userId and curdate() BETWEEN grantAccessDate and revokeAcessDate
WHERE users.username = @username
AND users.password = @passwd
AND cua.companyId = @companyid

Open in new window

This does not address any encryption of passwords or how company data access is restricted post login either.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
lwadwellCommented:
I forgot to mention for my alternative suggestion, that revokeAcessDate is populated with a silly future data (i.e. 2999-12-31) until set to an earlier date OR if left as NULL to indicate active, the SQL would need to be
    and curdate() BETWEEN grantAccessDate and ISNULL(revokeAcessDate,curdate())
0
 
prowebinteractiveincAuthor Commented:
the only thing is I I user a company login username not Id which is in the companies table so line 7 is not good
0
 
lwadwellCommented:
I thought you might but your description of the companies table did not include an name.  So I guess it would be something like:
SELECT 1
FROM users 
INNER JOIN companyUsersAffiliation cua ON users.userId = cua.userId
INNER JOIN companies ON cua.companyId = companies.companyId
INNER JOIN sysAccess ON cua.allowAccessId = sysAccess.id AND sysAccess.allowAccess = 1
WHERE users.username = @username
AND users.password = @passwd
AND companies.companyName = @companyname

Open in new window

Be warned however ... if the company name is being typed in ... matching on name may need to include doing an UPPER() on each value to remove case differences; spelling mistakes would be problematic.  If they were selecting from a drop-down (or ilk) from a list built from the companies table ... you should have kept the companyid with it in the background and used it on validation.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now