Author Topic: how would you handle this  (Read 1503 times)

Jake

  • Jackass In Charge
  • Posts: 8161
  • Karma: +83/-16
how would you handle this
« on: June 10, 2016, 11:57:26 AM »
I would like to create a KPI portal for my customers with their sales data. This sales data would be generated in a form of a webpage with data pulled realtime from the store's SQL database.

I think this webpage should be generated and served by the store server - but then what would be the best and safest way for my customers to access it remotely through a webpage?
Do not follow where the path may lead. Go instead where there is no path and leave a trail.

ober

  • Ashton Shagger
  • Ass Wipe
  • Posts: 14217
  • Karma: +72/-788
  • mini-ober is taking over
    • Windy Hill Web Solutions
Re: how would you handle this
« Reply #1 on: June 10, 2016, 12:31:26 PM »
I'm not sure I understand the disconnect between generation of the page and accessing it.  Install Apache/PHP on the same server as the DB and start writing a small web app (unless the server with the DB is not connected to the web?).  If the latter is the case, I would replicate the data to a hosted server somewhere.

tgm

  • Guitar Addict
  • Jackass In Charge
  • Posts: 3647
  • Karma: +46/-18
Re: how would you handle this
« Reply #2 on: June 10, 2016, 03:05:34 PM »
So you're thinking something like this?

Code: [Select]
        ┌────┐             
        │ db │             
        └────┘             
           ▲               
    ┌──────┴──────┐         
    │             │         
┌──────┐      ┌──────┐     
│store1│      │store2│     
└──────┘      └──────┘     
    ▲             ▲         
    │ https       │ https   
    │             │         
┌───────┐     ┌───────┐     
│client1│     │client2│     
└───────┘     └───────┘     

Or does each store have their own database locally as well?

Either way I don't know that I'd throw a web server on a store machine for clients to use. Securing it would be tricky. You're probably better off going with a centralized server somewhere and having a dns or subdomain kind of access for clients to login with.

I guess I'd need more detail on the system architecture to say but a local webserver wouldn't be on the top of my list of things to do.
This signature intentionally left blank.

ober

  • Ashton Shagger
  • Ass Wipe
  • Posts: 14217
  • Karma: +72/-788
  • mini-ober is taking over
    • Windy Hill Web Solutions
Re: how would you handle this
« Reply #3 on: June 10, 2016, 03:14:27 PM »
Valid points.  Again, the architecture is too vague to give a reasonable solution.

tgm

  • Guitar Addict
  • Jackass In Charge
  • Posts: 3647
  • Karma: +46/-18
Re: how would you handle this
« Reply #4 on: June 10, 2016, 03:28:49 PM »
Oh, in case anyone was curious, I used http://monodraw.helftone.com/ for the diagram. Super cool text based visio kind of thing.
This signature intentionally left blank.

micah

  • A man on the Internet.
  • Jackass In Charge
  • Posts: 6508
  • Karma: +55/-55
  • Good man. Nixon's pro-war and pro-family.
    • micahj.com
Re: how would you handle this
« Reply #5 on: June 10, 2016, 04:41:47 PM »
I think this webpage should be generated and served by the store server - but then what would be the best and safest way for my customers to access it remotely through a webpage?

is the "store server" already running as a web server (does it have IIS, Apache or nginx etc)? If so, that's how you'd run a webpage remotely to access it. You'd open up a port (like 443 if you're using SSL) to access the web files running on the server.  Security wise you'd probably be ok doing this as long as the database "user" used by the site has read only access.  It doesn't sound like you'r expecting loads of traffic but if this server isn't really used for the web, I wouldn't think you want to run a website off it though.

You could set up your site with all the customer log in access on a 3rd party server (like cheap $15/mo VPN) and then just have that site access the store's server's database as needed.  Again, w/ read-only access. 

"I possess a device, in my pocket, that is capable of accessing the entirety of information known to man.  I use it to look at pictures of cats and get in arguments with strangers."

Mike

  • Ass Wipe
  • Posts: 11951
  • Karma: +168/-32
  • Ex Asshole - a better and more caring person.
Re: how would you handle this
« Reply #6 on: June 10, 2016, 10:45:57 PM »
As mentioned it is a bit vague but one thing that jumped out of me was "webpage with data pulled realtime from the store's SQL database".  Never promise real-time.  I'll give you near real-time with a defined threshold but real time is a pipe dream and the closer you get the more complex and costly it gets.

I'd probably approach it like this:

1. Thin REST API on a web server that can connect to the SQL servers.  The purpose of this is to provide the data.  This is also where you'll enforce your data security.
2. Web page using a JS framework that can talk to the API server.  Splitting it out let's you separate out the display from the data.  It also reduces the amount of traffic as you are sending full webpages.
3. User authentication and security.  We generally use a simple username / pasword combination.  What auth mechanisms do you have right now?  We are using JSON web tokens (JWT) to transmit the user authentication on the calls to the API server.

Added bonus:  You could make use of websockets to alert the web client when new data is available.  I haven't actually done that (been wanting to).  You can poll as well.

Other thoughts:
1. Don't put the webserver on the same system as the DB server
2. Don't promise anything under 30 second latency
3. Use an actual system architect and designer

tgm

  • Guitar Addict
  • Jackass In Charge
  • Posts: 3647
  • Karma: +46/-18
Re: how would you handle this
« Reply #7 on: June 11, 2016, 01:34:11 AM »
There are also other more contemporary things like Kafka, Firebase and RethinkDB that you might take advantage of for more "realtime" data solutions. Streams seem to kind of be a big thing these days.

For security, I generally try and do something like OAuth and defer the credentials, it's pretty easy to support a wide variety and you can look at Auth0 or something too, in addition it's a really good idea to use a 2-factor auth solution as well, text a code or use a code generator like Google or Authy.
This signature intentionally left blank.

Mike

  • Ass Wipe
  • Posts: 11951
  • Karma: +168/-32
  • Ex Asshole - a better and more caring person.
Re: how would you handle this
« Reply #8 on: June 11, 2016, 02:54:57 AM »
OAuth is NOT a authentication protocol; it is a authorization protocol.

micah

  • A man on the Internet.
  • Jackass In Charge
  • Posts: 6508
  • Karma: +55/-55
  • Good man. Nixon's pro-war and pro-family.
    • micahj.com
Re: how would you handle this
« Reply #9 on: June 11, 2016, 11:54:58 AM »
OAuth is NOT a authentication protocol; it is a authorization protocol.

That's a great distinction to make; not sure I've heard anyone make it before.

"I possess a device, in my pocket, that is capable of accessing the entirety of information known to man.  I use it to look at pictures of cats and get in arguments with strangers."

tgm

  • Guitar Addict
  • Jackass In Charge
  • Posts: 3647
  • Karma: +46/-18
Re: how would you handle this
« Reply #10 on: June 11, 2016, 02:27:14 PM »
True if you don't trust the authentication provider then you'd probably want to do openid instead of oauth then but openid seems to have fallen out of favor for oauth login flows.

I've been trying to leverage email only and 2nd factor auth schemes in many of my projects lately. In reality many people forget their password and simply do a password reset which generates a one time token clicked on or entered from an email, which basically means you're doing email only login anyways. But any system these days really should have 2-factor auth even if both of those factors are probably unsecured on you phone. ;)

Which leads me to: put a password on your phone!!!!
This signature intentionally left blank.

Mike

  • Ass Wipe
  • Posts: 11951
  • Karma: +168/-32
  • Ex Asshole - a better and more caring person.
Re: how would you handle this
« Reply #11 on: June 11, 2016, 07:10:28 PM »
The problem with using OAuth is (to my knowledge) a lack of validation that the provider is who they say they are.  Example:  We use SAML for single sign-on and authentication; the idp (server) signs the response to the sp (client) so that the sp knows the request came from the idp.  You can also sign the request from the sp to the idp.

As much as I dislike our SAML solution it does address a lot of issues.

Jake

  • Jackass In Charge
  • Posts: 8161
  • Karma: +83/-16
Re: how would you handle this
« Reply #12 on: June 13, 2016, 09:39:15 PM »
hey guys. Thank you for the replies. After getting your ideas looks like this is something I am not going to be attempting myself! mostly for PCI (credit card security) reasons. I looked up a service called geckoboard.com that should do the trick though - kind of pricey.
Do not follow where the path may lead. Go instead where there is no path and leave a trail.

micah

  • A man on the Internet.
  • Jackass In Charge
  • Posts: 6508
  • Karma: +55/-55
  • Good man. Nixon's pro-war and pro-family.
    • micahj.com
Re: how would you handle this
« Reply #13 on: June 14, 2016, 11:52:25 AM »
cool.

So how is the data getting from the store DB to the geckoboard cloud?

"I possess a device, in my pocket, that is capable of accessing the entirety of information known to man.  I use it to look at pictures of cats and get in arguments with strangers."