Thread: Create a cache DB between web portal and internal DB?

Create a cache DB between web portal and internal DB?

From
"Dearman, Rick"
Date:
I have a requirement from my security manager but I can't seem to find a good solution. So I wondered if someone had
donesomething similar. 

We have a web portal and a DB in PostgreSQL (obviously) which contains user data. The portal is accessed by account
managerswho have access to only specific user accounts. This all works fine however the concern is that if you ever got
accessmore directly into the DB through a hack, or poorly designed site code, you could potentially access information
thatyou shouldn't.  

So the idea is that he is floating is we create a cache DB between the portal and the main DB which will only keep the
informationcurrently being worked on by the person logged in, and that any inserts/updates/deletes are passed on to the
mainDB through additional layers of security. 

Any ideas?

Rick Dearman





-----------------------------------------------------------------------
The information in this email is confidential and may be legally
privileged. It is intended solely for the addressee(s). Access to this
email by anyone else is unauthorised. If you are not the intended
recipient, please delete this e-mail and notify Telstra. Any disclosure
of the contents or recipients, distribution of, copying of or decisions
based on this email by unauthorised persons is prohibited and may be
unlawful.

Telstra Europe Ltd
3 Finsbury Square
London EC2A 1AE

Re: Create a cache DB between web portal and internal DB?

From
Dick Davies
Date:
* Dearman, Rick <rick.dearman@teamuk.telstra.com> [1245 05:45]:
> I have a requirement from my security manager but I can't seem to find a good solution.
> So I wondered if someone had done something similar.
>
> We have a web portal and a DB in PostgreSQL (obviously) which contains user data.
> The portal is accessed by account managers who have access to only specific user accounts.
> This all works fine however the concern is that if you ever got access more directly
> into the DB through a hack, or poorly designed site code,
> you could potentially access information that you shouldn't.
>
> So the idea is that he is floating is we create a cache DB between the portal and the
> main DB which will only keep the information currently being worked on by the person logged in,

If I was you, I'd have major concerns and have a chat with the manager in question.

How is that going to help anything - surely the cache DB would have to do a
query to populate itself anyway, which you have to check to gain any security benefit?
You're in a similar boat for updates.

If you're going to check the queries it makes somehow, just do those checks on the queries
you get in the first place.

This sounds like a pain in the arse to implement, maintain and debug with no benefits.
Far better to  spend that time cleaning up your application code and implementing a decent backup
policy.

--
'A little rudeness and disrespect can elevate a meaningless interaction
into a battle of wills and add drama to an otherwise dull day.'
        -- Calvin discovers Usenet
Rasputin :: Jack of All Trades - Master of Nuns

Re: Create a cache DB between web portal and internal DB?

From
Bruno Wolff III
Date:
On Mon, Dec 20, 2004 at 17:21:02 -0000,
  "Dearman, Rick" <rick.dearman@teamuk.telstra.com> wrote:
> I have a requirement from my security manager but I can't seem to find a good solution. So I wondered if someone had
donesomething similar. 
>
> We have a web portal and a DB in PostgreSQL (obviously) which contains user data. The portal is accessed by account
managerswho have access to only specific user accounts. This all works fine however the concern is that if you ever got
accessmore directly into the DB through a hack, or poorly designed site code, you could potentially access information
thatyou shouldn't.  

If the managers access the web site using their DB credentials then you can
enforce the proper access in the DB using views and/or security definer
functions.

If the managers don't have their own DB accounts, then the web application
needs to be trusted and should can spend your effort securing that.
Implementing a cache inbetween doesn't seem help in solving your problem.

Re: Create a cache DB between web portal and internal DB?

From
"Frank D. Engel, Jr."
Date:
A much better way to do this, assuming that the database login uses the
actual username/password of the manager in question (if not, see if
this can be arranged securely):

Assume you have a table with the restricted information.  Call it
real_data:

CREATE TABLE real_data
(
     accountID INTEGER,
     ...
)


Then you have another table storing the user id's of the managers:

CREATE TABLE managers
(
     managerUserID  TEXT,
     managerID           SERIAL
)


Now keep one more table, this one mapping which managers may access
which accounts:

CREATE TABLE access_control
(
     managerID    INTEGER,
     accountID      INTEGER
)


Rather than granting access to the tables, create a view which the user
software has privileges on:

CREATE VIEW data AS
     SELECT * FROM real_data
     WHERE accountID IN
         (SELECT accountID FROM access_control WHERE managerID =
             (SELECT managerID FROM managers WHERE managerUserID =
$CURRENT_USER))


Note: this code is off the top of my head and untested, so it may need
to be tweaked.

Assuming I got all of that right, this should set up a view, which you
would then GRANT privileges on, so that managers would only be able to
see data from the accounts the access_control table says they may see.
You can set up RULEs to permit UPDATEs and DELETEs, if these are
desired.

Note that for the security to work, any such RULEs you create will also
need to check access rights, and you will need to grant access to the
view (data), but *not* to the table (real_data), otherwise this makes
no real difference.


On Dec 21, 2004, at 5:31 AM, Dick Davies wrote:

> * Dearman, Rick <rick.dearman@teamuk.telstra.com> [1245 05:45]:
>> I have a requirement from my security manager but I can't seem to
>> find a good solution.
>> So I wondered if someone had done something similar.
>>
>> We have a web portal and a DB in PostgreSQL (obviously) which
>> contains user data.
>> The portal is accessed by account managers who have access to only
>> specific user accounts.
>> This all works fine however the concern is that if you ever got
>> access more directly
>> into the DB through a hack, or poorly designed site code,
>> you could potentially access information that you shouldn't.
>>
>> So the idea is that he is floating is we create a cache DB between
>> the portal and the
>> main DB which will only keep the information currently being worked
>> on by the person logged in,
>
> If I was you, I'd have major concerns and have a chat with the manager
> in question.
>
> How is that going to help anything - surely the cache DB would have to
> do a
> query to populate itself anyway, which you have to check to gain any
> security benefit?
> You're in a similar boat for updates.
>
> If you're going to check the queries it makes somehow, just do those
> checks on the queries
> you get in the first place.
>
> This sounds like a pain in the arse to implement, maintain and debug
> with no benefits.
> Far better to  spend that time cleaning up your application code and
> implementing a decent backup
> policy.
>
> --
> 'A little rudeness and disrespect can elevate a meaningless interaction
> into a battle of wills and add drama to an otherwise dull day.'
>         -- Calvin discovers Usenet
> Rasputin :: Jack of All Trades - Master of Nuns
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>
-----------------------------------------------------------
Frank D. Engel, Jr.  <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$



___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com