What are the changes in Oracle database after Oracle DB Vault Installations Part -1

After Oracle DB vault installation; there can be some changes which affect your business. Therefore; you should know these changes and you should be ready to revert these changes

Before Oracle DB Vault installation; you should note all configuration ( system parameters, privileges). And after DB vault installation; we should compare before and after values to prepare the revert script ( If it is necessary )

Let’s start with DAtabase parameters;

Changed Database Parameters after DB Vault Installation

After DB Vault installation; below database parameters change like these;

  • AUDIT_SYS_OPERATIONS  becomes TRUE
  • OS_ROLES becomes FALSE
  • RECYCLEBIN becomes OFF
  • REMOTE_LOGIN_PASSWORDFILE becomes EXCLUSIVE
  • SQL92_SECURITY becomes TRUE

You can use the below query to checks changes. You have to run and keep the result of this query before and after DB Vault installation.

Capture

Changed Privileges after DB Vault Installation

After DB Vault installation; Below privileges will be removed from some roles.

The privileges which are removed from DBA role.

BECOME USER
SELECT ANY TRANSACTION
CREATE ANY JOB
CREATE EXTERNAL JOB
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
MANAGE SCHEDULER
DEQUEUE ANY QUEUE
ENQUEUE ANY QUEUE
MANAGE ANY QUEUE

The privileges which are removed from IMP_FULL_DATABASE role

BECOME USER
MANAGE ANY QUEUE

The privileges which are removed from EXECUTE_CATALOG_ROLE role

EXECUTE ON DBMS_LOGMNR
EXECUTE ON DBMS_LOGMNR_D
EXECUTE ON DBMS_LOGMNR_LOGREP_DICT
EXECUTE ON DBMS_LOGMNR_SESSION
EXECUTE ON DBMS_FILE_TRANSFER

The privileges which are removed from PUBLIC user

EXECUTE ON UTL_FILE

The privileges which are removed from SCHEDULER_ADMIN role

ANY JOB
CREATE EXTERNAL JOB
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
MANAGE SCHEDULER

After DB vault installation maybe these changes can affect your business. In that case, you have to use below script to revert these changes.

grant execute on UTL_FILE to Public;
grant BECOME USER to DBA;
grant SELECT ANY TRANSACTION to DBA;
grant CREATE ANY JOB to DBA;
grant CREATE EXTERNAL JOB to DBA;
grant EXECUTE ANY PROGRAM to DBA;
grant EXECUTE ANY CLASS to DBA;
grant MANAGE SCHEDULER to DBA;
grant DEQUEUE ANY QUEUE to DBA;
grant ENQUEUE ANY QUEUE to DBA;
grant MANAGE ANY QUEUE to DBA;

grant BECOME USER to IMP_FULL_DATABASE;
grant MANAGE ANY QUEUE to IMP_FULL_DATABASE;

Grant EXECUTE ON DBMS_LOGMNR to EXECUTE_CATALOG_ROLE;
Grant EXECUTE ON DBMS_LOGMNR_D to EXECUTE_CATALOG_ROLE;
Grant EXECUTE ON DBMS_LOGMNR_LOGREP_DICT to EXECUTE_CATALOG_ROLE;
Grant EXECUTE ON DBMS_LOGMNR_SESSION to EXECUTE_CATALOG_ROLE;
Grant EXECUTE ON DBMS_FILE_TRANSFER to EXECUTE_CATALOG_ROLE;

Grant CREATE ANY JOB to SCHEDULER_ADMIN;
Grant CREATE EXTERNAL JOB to SCHEDULER_ADMIN;
Grant EXECUTE ANY PROGRAM to SCHEDULER_ADMIN;
Grant EXECUTE ANY CLASS to SCHEDULER_ADMIN;
Grant MANAGE SCHEDULER to SCHEDULER_ADMIN;

You can get these all scripts from GitHub.

Thanks for reading this note.

Y. Anıl Akduygu

Advertisements

Orapki Utility to Manage Oracle Wallets

The Oracle wallet is a secure place to keep keys and certificates. The Oracle wallet is the part of PKI ( Public Key Infrastructure). There are many ways to manage Oracle wallets. One of the methods is to use orapki utilities. In this note, I will show you some practical examples to use orapki utilities. The other methods can be used for managing wallets are owm GUI tool and mkstore command. These tools will be the subject of another note.

I will explain orapki utility with a sample. Imagine that you want to get service from an https website by using  PL/SQL procedures in Oracle database  ( Although I never advise reaching https services from Oracle databases, in some cases, it is required). And the service provider sent to you certificates.

The First Step:  Create the Oracle Wallet

orapki wallet create -wallet /home1/oracle/wallet –pwd passwd123 -auto_login

With this code, you created a wallet in /home1/oracle/wallet directory with passwd123 password. You should send wallet location and password to the developers because developers will use these parameters in  UTL_HTTP package.

After this command, two files ( which are the wallet)  are created in the wallet location.

rw——- 1 oracle dba 6800 Nov 23 15:42 ewallet.p12
rw——- 1 oracle dba 6877 Nov 23 15:42 cwallet.sso

The Second Step:  Add Certificates

Now put certificates which were sent by service providers into wallet location and add these certificates into the wallet with below command, first add the root certificate.

orapki wallet add -wallet /home1/oracle/wallet  -trusted_cert -cert  “root.cer”  –pwd  passwd123

orapki wallet add -wallet /home1/oracle/wallet  -trusted_cert -cert  “app.cer”  –pwd  passwd123

root.cer and app.cer is the file name of certificates.

If you get below messages after adding the certificate. It means the operation is successful.

Oracle PKI Tool : Version 11.2.0.4.0 – Production

Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

 

The Third Step Check Oracle Wallet

orapki wallet display -wallet /home1/oracle/wallet

With this command, you can see all certificates which are in the wallet.

The Fourth Step Check the https service

Now you can write a small PL\SQL code to check your wallet . Simply the below  code open your wallet by giving the location of wallet and its password and then it makes request to the web service with the wallet. If you get 200 from this code  it means you are successful.

set serveroutput on

DECLARE

lo_req  UTL_HTTP.req;

lo_resp UTL_HTTP.resp;

BEGIN

UTL_HTTP.SET_WALLET (‘file:/home1/oracle/wallet’,’passwd123′);

lo_req := UTL_HTTP.begin_request(‘https://webservice.asmx‘);

lo_resp := UTL_HTTP.get_response(lo_req);

dbms_output.put_line(lo_resp.status_code);

END;

/

200

Thanks for reading this note.

Y. Anıl Akduygu

 

 

 

 

 

 

 

Oracle Security Alert for CVE-2017-10269

Oracle announced a new security Alert CVE-2017-10269  on November 14th, 2017. Oracle strongly recommends applying this patch. This security alert is affecting only the Jolt server within Oracle Tuxedo. Nothing to do with Oracle database or any other products. This vulnerability is remotely exploitable without authentication and therefore its base score is 10.0.

It means it is very critical.

Affected Products in detail is Oracle Tuxedo, versions 11.1.1, 12.1.1, 12.1.3, 12.2.2

The Patch Availability Document is ( Metalink Document )

Oracle Security Alert CVE-2017-10269 Patch Availability Document for Oracle Tuxedo (Doc ID 2326009.1)

This patch solves the problem of below vulnerabilities.

CVE-2017-10269, CVE-2017-10272, CVE-2017-10267, CVE-2017-10278,  CVE-2017-10266

The link for the vulnerability.

http://www.oracle.com/technetwork/security-advisory/alert-cve-2017-10269-4021872.html

 

Thanks for reading this note.

Anıl Akduygu

https://www.linkedin.com/in/an%C4%B1l-akduygu-26129b28/

Install Ubuntu 17 on Oracle VirtualBox

Ubuntu is an open system  Linux-like operating system runs on Debian architecture. It can be used like Linux Server or a desktop. In this note ; I will show you how you can install the latest version of Ubuntu Desktop ( version 17) to Oracle Virtual Box.

Let’s start with downloading Ubuntu iso from

https://www.ubuntu.com/download/desktop

After downloading Ubuntu iso ; goto Oracle VirtualBox and Press ; New Button

Chosoe Linux as a type of Operating system and choose Ubuntu (64bit) as a type of Version

Capture1

Now choose memory size; The size of memory depend on what you want to with this desktop. If you want to use to learn some programming ( like Python) 1,5 GB will be enough, but if you want to use it like database server ; you should choose 2 GB and more.

Capture2

Now Choose Hard Disk type as shown below;

Capture3

And normally choose VDI  disk file type

Capture4

If you want to save disk on your host machine; Choose dynamically allocated hard disk. Because this kind of  disk fills up when you use it and it will shrink automatically.

Capture5

Now Choose File locatin and size. This completely depends on what you want to do in this computer. For example I chose 50 GB disk size. But Oracle VirtualBox  does not create a file with the size of 50 GB automatically, because I chose  Dynamically allocated Disk Type .

Capture6

Now Choose Storage part of the machine

Capture7

And attach your downloaded Ubuntu iso to CD  like bekow.

Capture8

And the most complicated part of the installation , skip this by choosing NAT ( Network Address Translation). I will explain this part in another note.

Capture9

Now save this configuration and start your virtual machine from Oracle VirtualBox .

And the Ubuntu installation will start

Capture10

I chose Download updates to get the latest updates during installation.

Capture11

If this is your new installation ; choose erase disk and Install Ubuntu

Capture12

Choose your location. As you know I am in the middle of the earth.

Capture13

and choose you language. Turkish is supported in Ubuntu as you know.

Capture14

And choose user name . This part is very important because in Ubuntu you do not need to use root user. Therefore just choose one user and its password. Guess my password 🙂

You will use this user for nearly everything.

Capture15

 

And Welcome to Ubuntu ;  installation will start

Capture16

 

And Ubuntu is ready  to use

 

Capture17

 

Thanks for everyone  for reading this note.

Anil Akduygu

https://www.linkedin.com/in/an%C4%B1l-akduygu-26129b28/

 

 

Oracle Security Alert CVE-2017-10151

Oracle announced a new security Alert CVE-2017-10151 . This is   affecting only Oracle Identity Manager. Nothing to do with Oracle database or any other products. This vulnerability is remotely exploitable without authentication. Ant it is base score is 10.0.

It means it is very critical.

The Patch Availability Document is Doc ID 2322316.1

( Oracle Security Alert CVE-2017-10151 Patch Availability Document for Oracle Identity Manager (Doc ID 2322316.1)

The workaround is very simple; Just change the password for the user OIMINTERNAL.

If you use Oracle Identitiy Manager use this workaround as soon as possible.

Thanks

Anıl Akduygu

https://www.linkedin.com/in/an%C4%B1l-akduygu-26129b28/

 

Oracle Critical Patch Update-October2017

Oracle announced  Critical Patch Update – October 2017 today. More or less this PSU  affects all Oracle Products.The general document that covers all information about October-2017 PSU is found in the Metalink.

Patch Set Update and Critical Patch Update October 2017 Availability Document (Doc ID 2296870.1)

At this note; we will focus on Oracle Database , Oracle WebLogic Server and Mysql products.

Let’s start with Oracle Database;

This PSU contains two important new security fixes for Oracle database. With these vulnerabilities , Oracle database may be exploited over a network without requiring user credentials. The base score of these vulnerabilities is 8.8. If you compare these scores with July-2017 PSU, these scores are low.

Actually , there are 6 new security fixes at this PSU. But I will show only two critical fixes at this note.

Screen Shot 2017-10-18 at 22.15.55

If you want to apply these patches ; you can find them at Metalink

For Oracle Database 12.2.0.1

Patch 26636246: COMBO OF OJVM RU COMPONENT 12.2.0.1.171017 + GIRU 12.2.0.1.171017

For Oracle Database 12.1.0.2

Patch 26636270: COMBO OF OJVM COMPONENT 12.1.0.2.171017 DBPSU + DBPSU 12.1.0.2.171017

For Oracle Database 11.2.0.4

Patch 26636315: COMBO OF OJVM COMPONENT 11.2.0.4.171017 DB PSU + DB SPU 11.2.0.4.171017

Continue with Oracle Fusion Middleware. The Base score for this product starts from 9.8. It is very high if you compare to Oracle Database. Screen Shot 2017-10-18 at 22.26.30

 

If you want to install this PSU. You can find patch from Doc ID 2296870.1

Patch number for Oracle WebLogic Server are given below.

Screen Shot 2017-10-18 at 22.28.24

Now go on with MySQL; It is base score is lower than Oracle Database and two of them are criticalScreen Shot 2017-10-18 at 22.32.33

As a result; I advice you to apply this PSU as soon as earlier.

Data Redaction Part – 2 Full Redaction

At this note ; we will start to work on Data Redaction by explaining Full Redaction . If you want to get a brief introduction about Data Redaction ; you can read the first  part of this note.

Let’s start with Full Redaction.

Put in a simple ; in Full Redaction ; The table columns are completely masked.
Numeric columns become 0 and character columns become a space character.

Let’s show it with an example. In the example ; we will redact salary column (SAL) column of  SCOTT.EMP table.  First start our application user. We will use this user to check how SAL column  is redacted.

c1

 

Now  check the original table. ( The emp table of Scott user ).  Before redaction all columns are visible as you see.

 

c3

 

Now by using DBMS_REDACT package we will redact SAL column. Simply by using add_policy procedure we can create a policy and add a column to this policy. The parameters of add_policy procedures are self explained. Therefore I am not given any explanation about these parameters. But the only expression parameter is very important.  The expression parameter should contain a logical expression. If the expression is TRUE, column mentioned in the column_name parameter  will be  redacted. If the expression is FALSE , the column will not redacted. In this example I use a simple logical expression which is ‘1=1’. As you know It is always TRUE. This redacts SAL column for all users ( except users who have EXEMPT REDACTION POLICY privilege – I will explain it in the next note)

c4

Now Check redaction polices by querying REDACTION_POLICIES view.

 

c5

If you want to query which column is redacted , you can use REDACTION_COLUMNS view.

 

c6

Lets’s check Data redaction is working on or not. You can check it with  previously created user USERA01. Connect with this user and query; SCOTT.EMPT table

As you see; All SAL column is redacted with 0. As a result ; USERA01 can not see the salary of employees.

c7

I hope that this small example will be a good start for  the Data Redaction. I will explain this subject with examples in the next notes.

You can find all these scripts at github

And one more thing ; If you want to get much more information about Data Redaction; you can read my book . It is written by Turkish language  but the examples will be very beneficial.

Anil Akduygu