Oracle #JSON check constraint #quicktip

I came across an interesting problem, we are using JSON in one of our applications and have no control over the people sending us the JSON data. This has caused some issues with data quality. One thing we needed to do is to check that all the required fields have a value when the data is loaded. I found solving the problem to be quite simple by adding check constraints on the JSON column.

alter table payload add constraint 
payload_first_name_check check
(json_exists(json_document, ‘$.first_name));

alter table payload add constraint
payload_last_name_check check
(json_exists(json_document, ‘$.last_name’));

Now when the documents load, if the first or last name are null, the check constraint is violated and ORA-02290  will be raised.

 Failed to execute: soda insert payload /home/oracle/data/04.json

 java.sql.SQLIntegrityConstraintViolationException: ORA-02290: check constraint (RLOCKARD.SYS_C0012523) violated

Posted in Database Stuff | Tagged , , , | Leave a comment

#Oracle #SQLcl #quicktip

If you’re not following Jeff Smith and Kris Rice, you should be. These are two really smart guys, who are designing the tools we use everyday to do our jobs.

If you’re like me, 99% of your data is in relational tables. So how do you get the data out of your relational tables into a JSON format. SQLcl has a command for that.

Getting data out of your relational tables and into JSON format is pretty easy; only, up until now, it was not easy to read. 

set sqlformat json-formattedSQL> select * from hr.employees  2* where rownum = 1;{“results”:[{“columns”:[{“name”:”EMPLOYEE_ID”,”type”:”NUMBER”},{“name”:”FIRST_NAME”,”type”:”VARCHAR2″},{“name”:”LAST_NAME”,”type”:”VARCHAR2″},{“name”:”EMAIL”,”type”:”VARCHAR2″},{“name”:”PHONE_NUMBER”,”type”:”VARCHAR2″},{“name”:”HIRE_DATE”,”type”:”DATE”},{“name”:”JOB_ID”,”type”:”VARCHAR2″},{“name”:”SALARY”,”type”:”NUMBER”},{“name”:”COMMISSION_PCT”,”type”:”NUMBER”},{“name”:”MANAGER_ID”,”type”:”NUMBER”},{“name”:”DEPARTMENT_ID”,”type”:”NUMBER”}],”items”:[{“employee_id”:100,”first_name”:”Steven”,”last_name”:”King”,”email”:”SKING”,”phone_number”:”515.123.4567″,”hire_date”:”17-JUN-87″,”job_id”:”AD_PRES”,”salary”:24000,”department_id”:90}]}]}

json-formatted is new in SQLcl 19.2 this gives a pretty print of the JSON data.

<code>
SQL> set sqlformat json-formatted
SQL> select * from hr.employees where rownum = 1;
{
  “results” : [
    {
      “columns” : [
        {
          “name” : “EMPLOYEE_ID”,
          “type” : “NUMBER”
        },
        {
          “name” : “FIRST_NAME”,
          “type” : “VARCHAR2”
        },
        {
          “name” : “LAST_NAME”,
          “type” : “VARCHAR2”
        },
        {
          “name” : “EMAIL”,
          “type” : “VARCHAR2”
        },
        {
          “name” : “PHONE_NUMBER”,
          “type” : “VARCHAR2”
        },
        {
          “name” : “HIRE_DATE”,
          “type” : “DATE”
        },
        {
          “name” : “JOB_ID”,
          “type” : “VARCHAR2”
        },
        {
          “name” : “SALARY”,
          “type” : “NUMBER”
        },
        {
          “name” : “COMMISSION_PCT”,
          “type” : “NUMBER”
        },
        {
          “name” : “MANAGER_ID”,
          “type” : “NUMBER”
        },
        {
          “name” : “DEPARTMENT_ID”,
          “type” : “NUMBER”
        }
      ],
      “items” : [
        {
          “employee_id” : 100,
          “first_name” : “Steven”,
          “last_name” : “King”,
          “email” : “SKING”,
          “phone_number” : “515.123.4567”,
          “hire_date” : “17-JUN-87”,
          “job_id” : “AD_PRES”,
          “salary” : 24000,
          “commission_pct” : “”,
          “manager_id” : “”,
          “department_id” : 90
        }
      ]
    }
  ]
}
</code>
Posted in Database Stuff | Tagged | Leave a comment

Effective Emails #BLUF

Yes, this is a bit outside of my specialty; however, I receive somewhere between four to five hundred emails a day. Some are informational, some require me to make a decision, others are request. There is a US DOD standard for emails that makes things much easier for everyone involved. It starts with the subject line and goes into how the body of the email should be crafted.

The Subject Line key words: ACTION, INFO, DECISION, SIGN. You can put all kinds of key words in the beginning of your subject line. This will tell the reader what to expect and what kind of actions they may need to do.

BLUF: Bottom Line Up Front. The most important part of the email goes up front. If you are asking for an action, a decision, making a request, or just an FYI, you address it in the first paragraph of your email. Also, and this is a pet peeve of mine, people place links in their emails, and burry them somewhere in the text. Please, right after that first paragraph, you should put all the importing links that will be needed.

Next be concise. Make your emails short and sweet. I’ve lost count of the number of times I’ve received an email that was over five pages long. Unless you have authored a couple of books and have a good editor review your email, they are difficult to read and rarely provide the information that is really needed. So keep it short and too the point.

If you require more than one screen to get the message across in an email, use bullet points and use active verbs. If you can’t get everything across like in one screen, follow up the email with a phone call. Yes, people still talk on the phone and quite frankly, get a lot done that way.

Posted in infosec | Leave a comment

Upcoming talks.

On 20 October I’ll be speaking at the Spanish Oracle Users Group on Holistic Database Security. Registration URL to follow.

On 29 October I’ll be giving the talk “Staying Current in a constantly changing IT Environment” This talk is open to the public and free of charge. It is being offered through Moscow Polytech and Oracle Academy.

There is one thing you can count on in life and that is change. The IT space is changing at breakneck speed, wait a six months, and you’re dealing with an emerging technology, wait a year and you’re behind. I’ve been fortunate to be surrounded by some of the most successful people in the industry who taught me the mindset and skills needed to stay current, stay in demand, and succeed. This talk will introduce you to the the strategies, tactics, and mindsets I’ve learned over the past 40 years.  Some are easy, some are difficult, and when done, it will help you stay relevant in the IT space for decades to come.

Here is the registration link in Russian and English

https://leader-id.ru/event/56738/?fbclid=IwAR1PdR610-8kpiGgb7JcuFtAD1meNQQ90rDJTq3vwddiqDnnmiTcCwWo8oI

Here is the English language registration link.

https://docs.google.com/forms/d/e/1FAIpQLSd6Uz5KWkHnnmY0u8X-jIruX2a0bGWSM9QUrVTCvCplq29Llw/viewform

And November 2, I will be speaking at the East Coast Oracle Users Conference on Holistic Database Security. Bellow is the registration link.

https://www.eastcoastoracle.org/

Posted in infosec | Leave a comment

AI Ops with Sandesh Rao

Monday August 31st at 1800 Sandesh Rao VP AI Ops and Machine Learning for Oracle Corporation will be giving a talk on AI Ops. Predict failures, make IT Operations easy. This is a no cost webinar. Registration link at the bottom.

Registration:

https://us02web.zoom.us/webinar/register/WN_72IaUGLaTRiTCpIhq2S-4A

Posted in infosec | Leave a comment

You may wonder about all the interviews.

When COVID-19 hit, many of us started working hard to stay in contact. Also, I have an interest in peak performance and getting to know people who excel at almost every thing they do. Here are some of the top people in the world and I’m happy to call them my friends.

Posted in infosec | Leave a comment

My interview with Heli Helskyaho

We all know her as Heli from Finland. For years I have tried to keep up with her; and she sill books more miles then me.

#GrowthMindset #Success #NeverStopLearning

Posted in infosec | Leave a comment

My interview with Christian Berg

#GrowthMindset #Success #NeverStopLearning

Posted in infosec | Leave a comment

My interview with Bobby Curtis. Golden Gate expert.

#GrowthMindset #Success #NeverStopLearning

Posted in infosec | Leave a comment

My interview with Martin Widlake

#GrowthMindset #Success #NeverStopLearning

Martin is not only an amazing gentleman, he is also the President of the UK Oracle Users Group.

Posted in infosec | Leave a comment

My Interview with Luiza Nowak of the Polish Oracle Users Group. #AmazingLady

#GrowthMindset #Success #NeverStopLearning

Luiza and I get to discuss her path to success and happiness.

Posted in infosec | Leave a comment

#Blockchain and Supply Chain. Opinion

Over the past forty plus years I have seen people try to take a technology and force it on a business problem. Trust me on this one, if you want to waste money and time, go ahead; however the odds of you solving the business problem are somewhere between slim and none. The case of using blockchain to solve supply chain problems is just not there. The only thing blockchain and supply chain have in common is the word chain.

My professional world is technology and ensuring the security of information systems. Because of this I’m constantly reading and listening to see what is happening in my world and applying that knowledge to help my customers solve their problems. One publication I go to is the World Economic Forum for a broad picture of what’s happening. Most of the time I find their writing good, but this time they seem to have had some marketing intern write about how blockchain will solve the supply chain problem. Actually, what I see here is trying to get an emotional response (it’s hacking the human brain, that’s a subject for another day) to get buy in on a solution in search of a problem.

What is blockchain? Pretty simple, it’s a data structure that is designed that once data is put on the blockchain, it is difficult to change. Notice I said the word difficult and not impossible because you can change data in a blockchain. In Blockchain a primer, I said that a blockchain is immutable; that statement is incorrect and I need to go back and correct it and perhaps rewrite. Once hailed as unhackable, blockchains are now getting hacked. Some blockchains support “smart contracts” and some people say this is a feature. I say it’s a security bug; but I digress. This is not about smart contracts that have proven to be buggy. See the above link from MIT Technology Review.

So, what is blockchain good for? Recording transactions that you don’t want changed. Financial transactions, yea’ we’re doing it. I have also proposed using it in Criminal Justice Information Systems to record everything from the assignment of Judges to logging of evidence and Government Purchasing Systems to record Request For Proposals, Bids, and Awards. In short, blockchain is a good tool if you absolutely do not want the data to be changed. If you need something to fix the supply chain, first define what problem you are trying to solve. Again, forcing a specific technology on a business problem is ass backwards. First, define the problem, then look at the available solutions and select the one that fists best.

Now, if you see how blockchain will solve supply chain problems, I’m all ears and will be happy to listen to you. Until someone comes forward and offers specifics; I’m skeptical.

——-UPDATE 15 May 2020 ——–

I had a chat with a friend on this issue, he gave me one use case where blockchain can be used to track the supply chain. It involves high value goods or combating counterfeit goods. This is because there is a tipping point where the cost of putting something on the blockchain. Say we have coffee beans, are you going to scan each coffee bean? How about one kilo of coffee beans? Now 100,000 kilos of coffee beans? When do you start putting things on the blockchain? How about office chairs as this article mentioned? Does blockchain solve this problem, or do you use a well designed RDBMS system complete with a well built audit trail? By the way, Oracle 20c has native blockchain tables if you really want to go this route.

Posted in infosec | Tagged , , | Leave a comment

Interview with That Jeff Smith.

His path to becoming an Oracle Product Manager. Great story.

Posted in infosec | Leave a comment

Interview with Olesya Zmazneva, PhD. Associate Professor Moscow Polytech. Artist, and Journalist

My interview with an extraordinary lady and one of my favorite people Olesya Zmazneva. We get to chat about soft skills, social media, communications, saints, critical thinking, education, five times why, think like a child and so much more.

Posted in infosec | Leave a comment

Interview with Connor McDonald.

My interview with Connor McDonald, part of the AskTom team, we talk about everything from robotics, the Commodore 64 to the Cloud Perth born and breed, support nice guy. #Growthmindset #KeepLearning #Success

Posted in infosec | Leave a comment

Interview with Steven Feuerstein.

From punch cards to Oracle 19c. Author, Oracle developer advocate, PL/SQL guru and all around nice guy. My interview with Steven Feuerstein. #GrowthMindset #KeepLearning #Success

Posted in infosec | Leave a comment

Interview with Chris Saxon.

#Oracle #Success #Growthmindset #KeepLearning

Sarandipity, don’t accept magic, and keep learning.

Posted in infosec | Leave a comment

interview with Flora Barriele. An incredible young lady who is doing amazing things.

#GrowthMindset #Success #Oracle

Posted in infosec | Leave a comment

My interview with Cary Millsap.

Oracle #Performance #GrowthMindset Smart and all around nice guy.

Posted in infosec | Leave a comment

Interview with Debra Lilley

Interview with Debra Lilley, We get to discuss how she got into IT, advice to university students, and STEM. I’m still having some audio issues, there seems to be an echo in the audio.

#oracle #learn #growthmindset #curious #ACED #STEM

Posted in infosec | Leave a comment

Interview with Milena Gerova with the Bulgarian Oracle Users Group

Milena Gerova and I discuss the growth of the Bulgarian Oracle Users Group / BGOUG /, some of the challenges she’s faced, what she looks for in a team member and other exciting things. Now, us southerners are proud of our Hospitality; Milena and her team demonstrate a level of hospitality I have rarely experienced. She is indeed, one of my favorite people in the world.


#oracle #learn #growthmindset #curious #ACED

Posted in infosec | Tagged , , , | Leave a comment

Interview with #Oracle ACE Director, OCM, and member of the Oak Table Network Kamil Stawiarski

Kamil is passionate about IT, Oracle Databases, and information security. Kamil will be talking about how he got into IT, what challenges he’s facing, and what it takes to succeed. Tune in Friday March 27th at 1430 (GMT-5)

Posted in infosec | Tagged , , , , , , , | Leave a comment

Finland Security Meetup 2020

Tuesday February 4th 2020 there will be a security meetup in Helsinki Finland. I will be there speaking on Secure coding and the impact of emerging technology will have on security.

Julian Dontcheff will be speaking on “The Power of Autonomous Database Security”

Kamil Stawiarski will be doing two hacking sessions.

  • Oracle Hacking Session The live demo, presenting a real-life experience in penetration testing of the consolidated Exadata database environment
  • Oracle Hacking Session – part II. We believe so much in technology, that we stopped believing in people.

Hope to see everyone there, this will be exciting. https://www.ougf.fi/events/security-meetup/

Posted in infosec | Leave a comment

#Oracle Database Application #Security book is finally out. #infosec #encryption #audit #SecureCoding #PrivilegeAnalysis #OID #OAM #OIM

https://www.amazon.com/Oracle-Database-Application-Security-Directory/dp/1484253663/ref=sr_1_1?keywords=oracle+database+lockard&qid=1573050833&sr=8-1

It’s been a year long process now the book is finally been released. There are a few things I would have written different and a few other subjects I would have liked to cover. Perhaps that will come in my next book or future posts.

In this book we cover Secure Coding, setting up Encryption, and audit. We also dive deep into performing privilege analysis.

Posted in Audit, encryption, infosec, PL/SQL, Security, Trusted Path | Tagged , , , , , , , | Leave a comment

Emerging Technology Security Day 2019

Between ongoing data breaches and emerging technologies constantly coming out, you need to ask the question. “Is my information secure?” On October 11th we will be hosting a security day with Oracle Corporation in Reston Virginia.

When: October 11, 2019

Where: 1910 Oracle Way, Reston, VA, 20190

Executive level presentations on: Cybersecurity Challenges, 5G Security, Machine Learning and AI Security, Blockchain, Cloud Security, and Cybersecurity Maturity Model Certification (CMMC)

We will be having executive level and technical discussions on the information security challenges we will be facing from emerging technology. All talks are geared to Executives and Senior Technical Leads.

8:00 – 8:30   – Arrival and Registration

8:30 – 9:00  – Morning snack (pastries), Welcome, and Introductions

9:00 – 11:30  – Executive Sessions
9:00-9:30 Cloud Security
9:30-10:00 Governance and Compliance
10:00-10:30 Data Security
10:30-11:00 Emerging Tech Security
11:00-11:30 5G/Communications Security
11:30-12:30  – Lunch
12:30 to 2:55  – Track Sessions   Track 1:  Cloud Security
12:30-1:15 Multi-Cloud Considerations
1:20-2:05 Cloud Access Security
2:10-2:55 Mitigating Threats (Insider, lateral movement, exfiltration, spoofing, etc)
Track 2:  Data Security
12:30-1:15 Database Application Development Tools Security (SQL Developer)
1:20-2:05 Maximum Security Architecture (MSA) – Data Architecture
2:10-2:55 Data Encryption/Key Management
Track 3:  Governance and Compliance
12:30-1:15 CMMC/IDAM
1:20-2:05 STIG/Patching
2:10-2:55 NIST/FedRAMP
Track 4:  Emerging Technology Security
12:30-1:15 AI/ML, Augmented Reality Security
1:20-2:05 Blockchain Security
2:10-2:55 IOT/Connected Device Security
Track 5:  5G/Communications Security – 12:30 to 2:55
12:30-1:15 5G Security
1:20-2:05 SDWAN Security
2:10-2:55 Session Border Control, Hardware Security

Closing Discussion/Q&A with Experts – 3:00-3:30

Thank you to our Primary Sponsor is Oracle Corporation for helping put on this event.

Register Here.

Posted in AI, blockchain, encryption, infosec, Machine Learning, Security, Trusted Path | Comments Off on Emerging Technology Security Day 2019