What are the GDPR rules and how do these apply to the DBA?

    Oct 25, 2018 10:00:27 AM by Dan Hotka

     Today I’m going to talk about the new GDPR rules, how they might affect your company, and steps DBAs can take to ensure compliance. 

    The General Data Protection Regulation (GDPR) is now in effect, replacing a prior, 20-year-old European Union privacy directive. If your company works with any personal data from employees, contractors, vendors, customers, or general consumers (and what company doesn’t?) then you need to give it some serious consideration—because the rules apply to companies worldwide and protect the data of all EU residents, whether they’re citizens or not.

    Does GDPR apply to my company?

    Probably! Consider:

    1. Does my company offer goods or services to individuals?
    2. Does my company monitor the behavior of individuals?
    3. Does my company have employees in the EU?

    If you can answer ‘yes’ to any of the above:

    • Do you have consent from the individual to use/process their personal data?
    • Is there a weighted interest to process personal data where this interest is not governed by another policy?
    • Does the personal data fall within the scope of public interest (including job duties)?
    • Is the personal information necessary to meet contractual obligations?
    • Is the personal information necessary to meet legal obligations?
    • Is the personal information a matter of life and death?

    If any one of these applies to your company and any of the ‘individuals’ are or could be EU residents, then you need to learn and follow GDPR rules.

    What are the GDPR requirements?

    For monitoring, GDPR really might apply to just about any website.  Think about this… just about every website tracks contact information (site visitors) at a minimum.  (I don’t personally on my site, but I do get feedback on the number of hits, etc… I leave it up to you to reach out to me J). 

    The definition of personal data is defined rather broadly as well, and this information now includes email addresses.

    GDPR also provides for individuals to be given the information a company has on them, and to be informed of who has the rights to see that information and how it is being used. Individuals must be given the ability to make changes to their information, to restrict its use, and to have their information removed from the company’s system upon request. 

    The GDPR also applies to security practices.  The company has an obligation to keep the individual’s information safe.  This is quite a challenge in today’s world where great volumes of data are copied when these larger systems are hacked into.

    Who is responsible for compliance?

    Who is responsible?  Your company’s management team is responsible.  Your company determines the data privacy practices employed at your company.  The programmer/DBA is also responsible for implementing these policies and applying them to the existing and new data.

    How to comply

    Some steps to consider when reviewing your GDPR compliance include:

    • Data necessity, retention, and deletion
    • Data integrity and quality
    • Data security
    • Data breach plans

    I know of many sites (almost all government sites) where the data has a shelf life but there is no plan to delete the old/obsolete data.  This is not only leaving more data potentially exposed in the event of a hack/breach but also has an adverse effect on performance.  I actually spend a bit of time in my SQL Performance Tuning class on how smaller data objects just plain work better…

    Data security is a must.  The DBA plays a huge part here to make sure that strong passwords are enforced and changed on a regular basis.  Oracle12 eliminated the ability to use non-case sensitive passwords.  Oracle11+ has the ability via a PL/SQL add-on to make passwords as strong as necessary.  Oracle11+ even has password history, not allowing a password to be changed, then changed back…  Also, a backup is just as useful to a person stealing information as the live database itself.  Make sure old backups are secure.  Make sure you know where ALL copies of the data are… no matter how old the data is.

    I don’t know of many sites that have a data breach plan.  I do have a chapter in my Advanced PL/SQL class that shows how to make things difficult, if not impossible, for the computer hacker to attack an Oracle database. 

    Oracle data integrity and quality features that can help

    I will finish this article on the data integrity and quality features.  Oracle12 introduced Data Redaction, the ability to hide sensitive data from the masses.  So, you can be in compliance in that you have the data but not just anyone can see the data.

    Oracle12c supports the ability to hide some or all of the data, based on redaction policies.  The user account that created the policy, SYSTEM, and SYS can see the un-redacted data; everyone else sees the output of the redaction policy.

    Figure 1. No data redaction

    No Data Redaction

    After going through the redaction policy on this table object the data becomes:

    Figure 2. Redacted data

    Redacted Data

    Oracle can redact the entire field, some of the field, use spaces, ‘*’, or random characters.  The user creating and maintaining the redaction policies must have execute privileges to the DBMS_REDACT package.

    These three steps implement the above-mentioned redaction policies.  I have included some useful tips.  This material is also covered in my Oracle12 New Features class.

    BEGIN

    DBMS_REDACT.ADD_POLICY(

    OBJECT_SCHEMA => 'HR',

    OBJECT_NAME => 'RDTEST1',

    COLUMN_NAME => 'SALARY',

    POLICY_NAME => 'REDACT_RDTEST1',

    POLICY_DESCRIPTION => 'REDACT RDTEST1 PII',

    FUNCTION_TYPE => DBMS_REDACT.FULL,

    EXPRESSION => 1=1;

    END;

    /

    This redaction policy example will change the SALARY field to 0 in the RDTEST1 table.    The EXPRESSION set to 1=1 will make it apply to everyone except the user who created the redaction policy, SYS, and SYSTEM.

    BEGIN

    DBMS_REDACT.ALTER_POLICY(

    OBJECT_SCHEMA => 'HR',

    OBJECT_NAME => 'RDTEST1',

    COLUMN_NAME => 'LAST_NAME',

    POLICY_NAME => 'REDACT_RDTEST1',

    POLICY_DESCRIPTION => 'REDACT RDTEST1 PII',

    ACTION => DBMS_REDACT.ADD_COLUMN,

    FUNCTION_TYPE => DBMS_REDACT.PARTIAL,

    FUNCTION_PARAMETERS =>

    'VVVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVVV,*,2,25',

    EXPRESSION =>

    'SYS_CONTEXT(''SYS_SESSION_ROLES'',''CLERK'') = ''FALSE''');

    END;

    /

    This redaction policy will change the last name, starting in the second position of the last name, to all ‘V’s.  Only the people with the role CLERK can see the full last name.  Also notice the First policy uses the procedure ‘DBMS_REDACT.Add_Policy’ and these latter two examples uses the ‘DBMS_REDACT.Alter_Policy’. 

    BEGIN

    DBMS_REDACT.ALTER_POLICY(

    OBJECT_SCHEMA => 'HR',

    OBJECT_NAME => 'RDTEST1',

    POLICY_NAME => 'REDACT_RDTEST1',

    POLICY_DESCRIPTION => 'REDACT RDTEST1 PII',

    ACTION => DBMS_REDACT.ADD_COLUMN,

    COLUMN_NAME => 'EMAIL',

    FUNCTION_TYPE => DBMS_REDACT.RANDOM,

    EXPRESSION => 1=1;

    END;

    /

    This redaction example will change the email address to random characters. 

    (The above examples came from Robert Freeman’s great Oracle Database 12c New Features book, recommended!)

    Final word to the wise

    The European Union provides full information on GDPR here.

    I hope this article helps you better prepare for the new data regulations.

     

    Dan Hotka

    Author/Instructor/Oracle Expert

    www.DanHotka.com

    Dan@DanHotka.com

     

    Tags: GDPR Oracle

    Dan Hotka

    Written by Dan Hotka

    Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari.

    Dan's most recent book is Toad for Oracle Unleashed