MySQL and MariaDB solution work around.

"If you're running non-SSL, that means you're exposing clear text passwords to anyone on the line..."

sure ! that's why I'm wondering why but open source don't offer that .

"If you access database instances via remote clients, only do this over an SSL/TLS secured connection.
"
good answer. by the remote client SW, right ?


"Looks like you're targeting some sort of PCI Compliance required by your Merchant Account provider or Settling Bank."

No! for this second round of comparison, comparing MySQL and MariaDB on what should we use as the next platform.

"There are many ways to accomplish your list of items to encrypt + they rely on exact OS Distro + version you're running."

how about redhat + MariaDB?


"If you're targeting PCI Compliance, be sure to read your gateway/bank docs carefully. Likely you'll find additional requirements for what's required to secure your database instance ("

I knew PCI compliance, if it is MS SQL it is very easy to pass as long as we use sth like SQL compliance manager from Idera ! I work on this kind of project before and auditor don't even want to know what is the configuration of compliance manager at all, they are not configured yet ! MS SQL always encrypted feature means encrypt all the way from client to server. everything encrypted on the Fly !

how about this:


1) NO external key management.

2) NO Authentication: SASL/SCRAM.

3) Do not supports data obfuscation for anonymization and data masking for psuedoanonymization.

4) Do not have Auditing: output – syslog.

5) Do not have Data masking (full and partial).


any workaround ?

"If you access database instances via remote clients, only do this over an SSL/TLS secured connection."

what is it is from application ? it seems application level SSL is hard to implement ?

hi,

any update for me ?

hi,

anyone can help on this : https://www.experts-exchange.com/questions/29109266/NDB-cluster-data-nodes-and-node-group.html?

Yes you've hit the other PCI requirements.

You can solve all 5x of your issues above + the most important one, which most people miss... which requires...

You keystore must have no public connections to the entire machine + traffic must never flow over any network which is accessible to anyone.

This might be what your #1 item is saying, although I've never heard any bank use this wording.

The best way around all these is to run a setup like this.

1) Machine level run Ubuntu Bionic + SNAP LXD.

2) Run 2x LXD containers.

3) 1st container provides front facing Website which will use normal, deep logging to do problem resolution.

4) 2nd container provides Keystore which can only be accessed by 1st container.

ACL the 2nd container with iptables rules which only allow traffic to/from 1st container for single port required.

Hint: Do yourself a favor + use MariaDB for your database for both containers. Some Keystore software simply won't work correctly with containers because their networking is badly written. Redis comes to mind. They use a type of network stack which breaks network namespaces, so Redis simply won't work for any PCI related type of system.

If you've never done this before, I'd strongly suggest you open a Gig + hire someone to design + implement + maintain your system for you.

A true PCI Compliant system (like PayPal, Stripe, Wells Fargo) presents many design hurdles which must be correct, if you ever expect to pass an internal audit... which is different than the joke PCI scans done by external tools.

"You keystore must have no public connections to the entire machine + traffic must never flow over any network which is accessible to anyone."

So this usually mean DIRECT connection from public network ? so only web server can connect to this DB you mean ?

"ACL the 2nd container with iptables rules which only allow traffic to/from 1st container for single port required."

this means 2nd container is the mariaDB backend itself ?

"A true PCI Compliant system (like PayPal, Stripe, Wells Fargo) presents many design hurdles which must be correct, if you ever expect to pass an internal audit... which is different than the joke PCI scans done by external tools."

here not that hard !  buy an DB security and audit SW, it will pass. that tools don't even have to configure correctly !

so you steps is to fix all that below ?

1) NO external key management.

2) NO Authentication: SASL/SCRAM.

3) Do not supports data obfuscation for anonymization and data masking for psuedoanonymization.

4) Do not have Auditing: output – syslog.

5) Do not have Data masking (full and partial).

Select allOpen in new window

1) So this usually mean DIRECT connection from public network ? so only web server can connect to this DB you mean ?

No. The Web server many not connect to your keystore instance, if you're looking for PCI Compliance which can pass a physical/premise audit.

One approach is what I describe above.

The KeyStore container must be firewalled, so it only communicates, via a local connection, with the other container.

You must also test your setup using something like tshark to ensure no packet leakage onto your public network by your LXD container connection. This usually occurs by default + I'd still test this thoroughly.

2) This means 2nd container is the mariaDB backend itself

Could be yes or no.

Typically 1st container - a normal LAMP Stack, including MariaDB containing all data encrypted using a key.

Then #2 container - KeyStore for encryption keys with an extruded API which only 1st container can access.

3) here not that hard !  buy an DB security and audit SW, it will pass. that tools don't even have to configure correctly !

No.

External scanners will only test very cursory items.

If you must pass a premises audit, where someone shows up to start auditing your technology, then no external scanner can do this.

A human or human team will do this.

And this tends to only occur in two situations.

First - Customers start complaining that their data has been stolen from you. Many people, including me personally, generate virtual credit cards for online transactions, so each credit card used tracks to a specific site. I can tell instantly who's site is hacked, by credit card number.

Second - Your transactions reach a certain monthly amount. This amount varies between companies. Many times, a company will freeze all funds + then require a premises audit to release funds. This tends to occur when you're working with very large amounts of money + revenues grow rapidly over a very short period of time.

4) so you steps is to fix all that below ?

Yes.

data sent to the client are NOT encrypted

just use ssl

If you're running non-SSL, that means you're exposing clear text passwords to anyone on the line...

no. mysql/maria does challengeresponse authentication so the password is not exposed even when not using SSL.
additionally mysql has one of the VERY few working implementation where the password is encrypted in storage and still does challenge response.
none of the concurrent commercial products i know of do the same.

many of the stated above points are also false.  2 and 4 clearly are.

you should check your facts.
... and probably focus on your needs before trying to pick whatever piece of software has "everything"  which usually comes to picking the better commercial rather than the best piece of software

David,

"If you must pass a premises audit, where someone shows up to start auditing your technology, then no external scanner can do this."

you mean no external scanner is allow when doing audit ?

so you just said when auditing like PCI compliance, they will have a human team to hack.. ?

if referring to how to solve/offer workaround on each of the point above, what can I do ?

1) NO external key management.
if you mean a pki, you can setup one for ssl access

2) NO Authentication: SASL/SCRAM.
this is supported by mysql and mariadb. no workaround needed

3) Do not supports data obfuscation for anonymization and data masking for psuedoanonymization.
i'm unsure about this point. same as 5 ?

4) Do not have Auditing: output – syslog.
fully supported in mariadb. complex auditing possible with a little scripting and/or tools provided by mysql/maria

5) Do not have Data masking (full and partial).
no workaround except manually.
note: it is likely not much more of a hassle to create a fictious dataset to work on than configure data masking in oracle

can you elaborate on the initial requirements for 3/5 ? data masking is one way to achieve your goal. there may well be others.

"2) NO Authentication: SASL/SCRAM.
this is supported by mysql and mariadb. no workaround needed"

but offical reply is a NO, any idea? it is for MySQL only! not for MariaDB.


"4) Do not have Auditing: output – syslog.
fully supported in mariadb. complex auditing possible with a little scripting and/or tools provided by mysql/maria"

this is for MySQL only, not for MariaDB.

"no workaround except manually."

again for MysQL only , what is manually means ? web coding ?


"data masking is one way to achieve your goal"

what is the other ?

Based on your questions, likely best to hire someone to do this, so they can walk through each step with you.

Setup + Maintenance (daily) of this type of infrastructure is complex.

Far better to work with an expert, at least once, before attempting to design this all yourself.

actually for MariaDB we will use vendor and even we ask MariaDB by paying them , they will only send you a link and ask you to do it yourself.

MySQL and MariaDB is here, hard to find an expertist here!

All company only start to consider this since last year ! everyone still talk about oracle and MS SQL and DB2.

so please tell me more here.

but offical reply is a NO, any idea? it is for MySQL only! not for MariaDB.

official documentation states it's a yes.  seconds googling as well. no idea where you got your official answer.

what is manually means ? web coding ?

do you even know what data masking is ? or are you simply picking words in oracle / mssql docs and expecting the same feature ?

i'd suggest copy a part of your db while replacing sensitive data with random data manually or maybe add a mysql proxy with about 8 lines of lua code to replace specific columns on the fly on your dev environment

__

it is impossible to help you efficiently as long as :

1 : you do not state your actual goal. you ask for features you may or may not need. "i do not want this specific data to be sniffable by the web server admins" is a goal. i want in flight encryption is a mean. and an inefficient one in that case.

2 : you insist into cloning the features of a product while you don't clearly understand what each of them does.

"no idea where you got your official answer."

MariaDB , not from MySQL, may be not trust able! sometime found out that MariaDB keep bad mouthing other DB by untruth statement.

"1 : you do not state your actual goal. you ask for features you may or may not need. "i do not want this specific data to be sniffable by the web server admins" is a goal. i want in flight encryption is a mean. and an inefficient one in that case."

I am comparing 2 x OSDB, so I compare them one by one .

"or are you simply picking words in oracle / mssql docs and expecting the same feature ?"

apple to apple comparsion ! MariaDB said MySQL do not have data maskign.

"i'd suggest copy a part of your db while replacing sensitive data with random data manually o"

This is by application coding.

"a mysql proxy "

you mean proxySQL ?

MariaDB , not from MySQL, may be not trust able!

i have no idea what this means.

sometime found out that MariaDB keep bad mouthing other DB by untruth statement.

i'd welcome any example of that behavior.
i've been working with both mysql and mariadb and i can vouch i never seen these folks lie purposely.

i can also vouch that oracle does lie frequently both regarding their features and others. ( for example oracle HA as described in their white papers is basically a scam because they simply push the replication problem onto the SAN )

I am comparing 2 x OSDB, so I compare them one by one .

based on what requirements ? all dbs have VERY different features. try and find a decent masterless cluster implementation or a column store in oracle. you need to focus on what you need.

mysql does not have data masking. mariadb neither. afaik they will never plan to have this feature because they consider it useless. i consider it dangerous + much more of a pita to configure than manually building a test dataset the old-school way.

This is by application coding.

if you consider a few one-liners as coding, yes

proxy

i was thinking the mysql-proxy but feel free to use a different one as long as the feature exists.

you'll find tons of proxys with features such as load sharing, failover between instances, statistics gathering, ... and possibly even data masking. in mysql-proxy data masking is achieved by replacing a named field with NULL or random data on the fly with a 6 lines code you can copy-paste from the official docs.

"i have no idea what this means."

MariaDB keep saying a lot of thing is lack in MySQL, I keep finding out it is not true ! so MariaDB bad mouth !

"i've been working with both mysql and mariadb and i can vouch i never seen these folks lie purposely."

MariaDB said MSSQL is the worse one and it is an absolute BS !! MS SQL is one of the best ! that's why a lot of BANK use it now and they don't use Oracle and DB2 any more.

So it is MS SQL or mariaDB or MySQL, so the company ONLY use MS SQL is dump ???

"i can also vouch that oracle does lie frequently both regarding their features and others. "

actually MS do not lies so much !  what they are doing is tell what is the characters of other's product and what they can do better ! in their OWN way !

but cluster technology , everyone nearly the same but for e.g. MariaDB, 3 x node with 1 node remain will make Cluster dead, it is not good as MS SQL server alwayson, even only 1 node remain still operable!

yesterday we bring out MySQL staff to tell difference between all HA solution, NDB cluster is good but TOO EASY To die ! anything break the heartbeat threshold will make NDB dead ! WTF ???? NDB can't be use in cross site cluster !

so now we consider to use InnodB cluster with each site and setup multi master replication between them.

InnoDB cluster using sth similiar to SQL alwayson, replicate in transaction level instead of transaction log level.

"based on what requirements ? all dbs have VERY different features. try and find a decent masterless cluster implementation or a column store in oracle. you need to focus on what you need."

we are doing project review and management actually simply want to kill Oracle as it is expensive! so what OTHER DB we can use ! so I am doing some detail comparsion in DB only feature ! like OLAP, MySQL don't have it but MariaDB!!

but both OSDB DO NOT HAVE ETL, much use third party.

"mysql does not have data masking. mariadb neither"

MariaDB said they have !

"i was thinking the mysql-proxy but feel free to use a different one as long as the feature exists."

MySQL guys suggest a customer to use ProxySQL if load balancer is not possible!

i'm not answering your blattant an false claims about what whoever "says". you're wrong and badmouthing nice people.

__

i've been talking about a mariadb cluster. this is not NDB which i don't like much either. multimaster replication is yet a different thing which most likely won't fit your need.

again, you should experiment with the cluster before you badmouth it : their technology is FAR SUPERIOR to oracle, ms's or mysql's.

we are doing project review and management actually simply want to kill Oracle as it is expensive! so what OTHER DB we can use ! so I am doing some detail comparsion in DB only feature ! like OLAP, MySQL don't have it but MariaDB!!

this is exactly what you should no do.

if they want to kill oracle, look at what feature your application needs, not the feature oracle has. there are chances any sql engine around is good enough for your needs and you are simply loosing time.

most of what you should be focusing on is the amount of rewrite to your apps. if you have lots of plsql, the obvious choice will probably be postgres. if you do not, you probably can switch to anything.

__

i'm pretty sure you have no idea what ETL means but for the record you can setup ETL with mysql and maria and still for the record, you most likely should not. i'm not elaborating on this even if you ask unless you clearly state how this would be useful to your project and describe your current ETL implementation.

__

MariaDB said they have !

if they say they have, they do have.
if they do not, you misunderstood which is fairly possible since your english is even worse than mine and you seem to pick and mix  random bits of information from random sources.

__

MySQL guys suggest a customer to use ProxySQL if load balancer is not possible!

no relation to the topic. i cannot follow a conversation made of random unrelated information.

"i'm not answering your blattant an false claims about what whoever "says". you're wrong and badmouthing nice people."

are you referring to my question/reply, I don't see why you say this.

"i'm pretty sure you have no idea what ETL means but for the record you can setup ETL with mysql and maria and still for the record, you most likely should not. "

I understand what ETL means but how you setup ETL for MySQL and MariaDB? what tools you will use ?

are you referring to my question/reply, I don't see why you say this.

simple enough : you have been repetitively claiming that A is badmouthing B but you do not provide any evidence nor even mention how you got that information. in this case, i've been working with the corresponding people and i know for a fact this is not the way they behave.

I understand what ETL means but how you setup ETL for MySQL and MariaDB? what tools you will use ?

if you actually understand what you're talking about, you probably know that would depend on the situation and your specific requirements.

__

as stated multiple times, there is no helping you if you keep up asking questions, not listen or believe the answer, provide zero information regarding what you are trying to do, and perform zero personal work.

"simple enough : you have been repetitively claiming that A is badmouthing B but you do not provide any evidence nor even mention how you got that information."

I don't think i need to , a lot of statement from you always DO NOT Have it.

i'm not badmouthing anyone and have the means to backup my statements. actually most of what i ever wrote in any of the threads you opened can be found in the documentations, and the rest is backed by about 15 years of experience with all kinds of databases including custom ones in some cases.

additionally what i state are facts. i'm not saying X "keeps badmouthing" Y without giving a single example of a false statement. and i'm not mixing up 6 years old posts on a mysql/mariadb forum with updated documentation which you seem to repetitively do.

anyway this is a loss of time for both of us. good luck with your project /study / or whatever you're trying to achieve.