ALTER USER system privilege in Oracle 12cR2

In Oracle 12cR2 a new security enhancement has been introduced, the “alter user” system privilege when granted to an account….this account won’t be able to change “SYS” password anymore.

Let us explore “ALTER USER” system privilege in Oracle 12cR1 first:

1

2

 

3

 

4

while in Oracle 12cR2:

i have granted SYSTEM account the “ALTER USER” system privilege:

SQL> SELECT * FROM DBA_SYS_PRIVS where grantee=’SYSTEM’;

11

As a SYSTEM user I can’t alter the SYS user password as shown below:

12

The oracle error raised is:

ORA-01031: insufficient privileges

Not only you can’t change the password, you can’t change any activity that is related to the “ALTER USER” command such as changing the “profile”, “quota” ….etc

This feature will be very helpful if you have a segregation of duties in your database infrastructure  where account management is maintained by different entity which will definitely be granted “ALTER USER” system privilege for password management and reset….in this case they wont have the power to change the “SYS” user password.

Is there any workaround to go over this security restriction ?

Yes.

Under SYS schema create the following procedure:

SQL> create procedure sys.sys_pass_change(my_pass in varchar2) as

begin

execute immediate ‘alter user sys identified by ‘||my_pass;

end;

/

SQL> grant execute on sys.sys_pass_change to system;

Then, accessing the database as “SYSTEM” user:

SQL> exec sys.sys_pass_change(‘yarn_9ns’);

33

in this example The SYSTEM account will execute the procedure with “definer’s” right.

 

 

 

 

 

 

 

 

 

 

Applying April 2018 PSU Patch ERROR :Files check failed: Some files under ORACLE_HOME are not patched. Please see log file for details.

while applying latest Oracle database PSU (April 2018) on Oracle 12cR1 (12.1.0.2) on IBM AIX 7.1 Operating System the following errors were thrown:

Verifying the update…

There are 1 copy files under ORACLE_HOME that are not patched.
Files check failed: Some files under ORACLE_HOME are not patched. Please see log file for details.

ApplySession failed in system modification phase… ‘Verification of patch failed: Files are not updated completely.’

Restoring “/orcl/oratest/product/12.1.0.2” to the state prior to running NApply…

Checking if OPatch needs to invoke ‘make’ to restore some binaries…

OPatch was able to restore your system. Look at log file and timestamp of each file to make sure your system is in the state prior to applying the patch.

NApply restored the home. Please check your ORACLE_HOME to make sure:

– files are restored properly.

– binaries are re-linked correctly.

(use restore.[sh,bat] and make.txt (Unix only) as a reference. They are located under

“/orcl/oratest/product/12.1.0.2/.patch_storage/NApply/2018-04-19_12-37-29PM”

UtilSession failed: ApplySession failed in system modification phase… ‘Verification of patch failed: Files are not updated completely.’

Log file location: /orcl/oratest/product/12.1.0.2/cfgtoollogs/opatch/opatch2018-04-19_12-37-29PM_1.log

Solution:

use the latest Oracle OPatch utility you can download it from here:

https://updates.oracle.com/download/6880880.html

latest Opatch utility version (at the time of writing this article 🙂 ) is 12.2.0.1.13

Latest OPatch Utility

Migrate Database to CDB environment with different DB_BLOCK_SIZE

If you face a situation where you need to plugin a database that has a different block size than the destination CDB then you need perform the following:

Let us assume that the source database that will be plugged is configured with 16k block size , but the destination CDB environment is configured for 8k as shown below:

SQL> show parameter db_block_size

P1

In the CDB$root container execute the following:

SQL> alter system set db_16k_cache_size=200M scope=spfile;

//note i am using db_nk_cache_size parameter

Restart your CDB instance. And then try to plug-in the database.

Always check pdb_plug_in_violations view for any issues:

SQL> select message,time from pdb_plug_in_violations;

If I try to create a tablespace with 16K size ….. you will be able to create a tablespace successfully  with no issues:

P2

 

 

 

 

 

 

 

 

 

 

 

 

 

ORACLE_MAINTAINED column in Oracle 12c dictionary

Lets say someone asked you what are the Oracle database built-in accounts (Oracle internal) in your current database ?

Most oracle database dictionary views have a column called ORACLE_MAINTAINED that was introduced in Oracle 12c release.

https://docs.oracle.com/database/121/REFRN/GUID-309FCCB2-2E8D-4371-9FC5-7F3B10E2A8C0.htm#REFRN23302

 

SQL> select username,last_login,oracle_maintained from dba_users where oracle_maintained=’Y’;

Picture1

Finding Oracle built-in roles:

 

SQL> select role,oracle_maintained from dba_roles where oracle_maintained=’Y’;

Picture22

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Secure Oracle database binaries by updating JDK

One of the things that gets unnoticed or (overlooked) when securing Oracle Database Infrastructure is securing oracle database binaries by updating JDK build or updating SQL Developer.

When installing Oracle database binaries the version in a database install is always going to be behind the latest JDK so you should update to the latest version since latest version will include security fixes.

to check your current JDK build:

cd $ORACLE_HOME/jdk/bin

java –version

Procedure to replace JDK:

shutdown your oracle database and listener.

cd $ORACLE_HOME

mv jdk jdk.orig

//  you can download the Latest Java SE Patches/Update Releases on MOS (Doc ID 1414485.1) OR All Java SE Downloads on MOS (Doc ID 1439822.1)

//  copy the downloaded JDK to $ORACLE_HOME

scp jdk-6u181-linux-x64.bin $ORACLE_HOME

cd $ORACLE_HOME

./jdk-6u181-linux-x64.bin

cd $ORACLE_HOME

mv jdk1.6.0_181 jdk

rm -rf jdk-6u181-linux-x64.bin

To verify: 

cd $ORACLE_HOME/jdk/bin

java –version

Startup the database and listener.

Run utlrp.sql script and check that all database components are valid:

SQL> @?/rdbms/admin/utlrp.sql

SQL> select * from dba_registry;

Also, check database alert log file …just in case.

If the database has “JAVA” component you can follow the steps described in this procedure for “verification”

How To Determine The JDK Version Used by the Oracle JVM in the Database (Doc ID 131872.1)

Pluggable database Service Level Access Control “Firewall”

If you are using VNC (Valid Node Checking) to implement a TNS firewall in non-CDB Oracle database architecture and wondering if there is a way to perform the same thing in Multitenant Architecture (CDB)…..Yes…through  a package DBMS_SFW_ACL_ADMIN  and its under the account: DBSFWUSER

The account has three tables: ACL$_OBJ , EXADIRECT_ACL , IP_ACL

1

First, you need to add your listener.ora file the following:

LOCAL_REGISTRATION_ADDRESS_LISTENER=ON

2

The firewall On clause means only connection coming validated against ACL will be accepted, others will be rejected. This is documented in the package specification comments as follows:

3

Running the following SQL Query in the CDB$root will provide the information of the services available:

SELECT service_id,name,network_name,pdb FROM   cdb_services;

4

To configure PDB Level Access, execute the following in CDB$root:

BEGIN

  dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_add_pdb_ace(‘pdb_test2‘,’192.142.56.136‘);

  dbsfwuser.DBMS_SFW_ACL_ADMIN.commit_acl;

END;

/

In the package specification you can see the list of procedures included within the package to provide a guide what input parameters are required :

5

Checking ACL has been added:

6

To remove access control entry:

BEGIN

  dbsfwuser.DBMS_SFW_ACL_ADMIN.IP_REMOVE_PDB_ACE(‘pdb_test2′,’192.142.56.136’);

  dbsfwuser.DBMS_SFW_ACL_ADMIN.commit_acl;

END;

/

 

 

 

 

 

 

Oracle RMAN Errors RMAN-03002 RMAN-03014 RMAN-06004

The below error was received after restoring the database to a previous point in time:

RMAN-03002: failure of backup command at 08/10/2017 14:00:15

RMAN-03014: implicit resync of recovery catalog failed

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20035: invalid high RECID

To resolve this either implement OPTION 1 OR OPTION 2 :

OPTION 1. unregister/register the database with recovery catalog:

— make sure you have a backup of your recovery catalog, because

it retains old  backup information of all of  your  databases.

rman target /

connect catalog_usr/catalog_usr_pass@CATALOG

RMAN> unregister database;

RMAN> register database;

OPTION 2. Open Database with Resetlogs:

sqlplus / as sysdba

SQL> ALTER DATABASE MOUNT;

SQL> ALTER DATABASE RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;

SQL> ALTER DATABASE RECOVER CANCEL;

SQL> ALTER DATABASE OPEN RESETLOGS;

rman target /

connect catalog_usr/catalog_usr_pass@CATALOG

RMAN> reset database;

RMAN> backup database;

New Features for Oracle Database 12cR2 In-Memory

Oracle 12cR2 release introduced many new features for the in-memory database technology, the following is some of the new features introduced:

Dynamically increase In-Memory area on the fly

Starting in 12.2, it is possible to increase the size of the In-Memory area on the fly, by increasing the INMEMORY_SIZE parameter using ALTER SYSTEM command. Also, worht mentioning is that The INMEMORY_SIZE parameter must be increased by 128MB or more in order for this change to take effect. However, It is not possible to shrink the size of the In-Memory area on the fly.

In-Memory FastStart

Enabling In-Memory FastStart (IM FastStart) the system will checkpoints the IMCUs from the IM column store to the FastStart area on disk. Consequnetly, when database restarts takes place, data is populated via the FastStart area rather than from the base tables.

Note that FAST_START_TBS is a dedicated tablespace configured for FastStart,is allowed for each PDB or non-CDB.the FastStart tablespace should be at least twice the size of the INMEMORY_SIZE setting.

CREATE TABLESPACE FAST_START_TBS DATAFILE
‘D:\APP\EMAD\VIRTUAL\ORADATA\ORACLE\FASTSTARTD01.DBF’ SIZE 400M
LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

SQL> BEGIN dbms_inmemory_admin.faststart_enable(‘FAST_START_TBS’); END;

So, basically The FastStart area defines what data is loaded when the database reopens. Population of data is still controlled by the “priority” settings, it won’t affect it.

SQL> SELECT * FROM V$INMEMORY_FASTSTART_AREA;

inmem1

In-Memory Expressions

In-Memory Expressions provide the ability to materialize commonly used expressions in the In-Memory column store. Materializing these expressions will improve the query performance by preventing the re-computation of the expression for every row. Also, it enables us to take advantage of all of the In-Memory query performance optimization when we access them.

* Analytic queries often contain complex expressions in the select list or where clause predicates that need to be evaluated for every row processed by the query

* The evaluation of these complex expressions can be very resource intensive and time consuming

* In-Memory Expressions greatly improve the performance of analytic queries that use computationally intensive expressions and access large data sets

* In-Memory expressions speed queries of large data sets by pre-computing computationally intensive expressions

In-Memory Virtual Columns

user-defined virtual columns can now be populated in the In-Memory column store. Virtual columns will be materialized as they are populated and since the expression is evaluated at population time it can be retrieved repeatedly without re-evaluation.

To illustrate:

SQL> alter system set inmemory_virtual_columns=ENABLE scope=spfile;

inmem2

CREATE TABLE HR.EMPLOYEE_SALES (
employee_id NUMBER(6) PRIMARY KEY ,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
salary NUMBER(8,2)
);

The target query against the table will be:

SQL> select employee_id,last_name,salary,(salary/12) as monthly_sal, 3*(salary+2000) as bonus from HR.EMPLOYEE_SALES order by bonus;

SQL> ALTER TABLE HR.EMPLOYEE_SALES ADD monthly_sal AS (salary/12);
SQL>ALTER TABLE HR.EMPLOYEE_SALES ADD bonus AS (3*(salary+2000));

SQL> ALTER TABLE HR.EMPLOYEE_SALES INMEMORY PRIORITY HIGH;

SQL>DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS(‘CURRENT’);
SQL> EXEC DBMS_INMEMORY_ADMIN.IME_POPULATE_EXPRESSIONS();

SQL>SELECT OWNER, TABLE_NAME, COLUMN_NAME, SQL_EXPRESSION
FROM DBA_IM_EXPRESSIONS;

 

 

 

 

 

 

 

 

 

Oracle Virtual Private Database

The idea of Virtual Private Database is to enable users having access to a table to see ONLY subset of
data within the table. This technique is based on a function that will enable SQL execution to append a WHRE clause predicate. The subset of data can be from row-level or column-level perspective.

For demonstration, I will be using a table called EMP under SCOTT schema.

1.PNG

I will also create 2 accounts:
SQL> create user manager identified by mono670;
SQL>grant create session to to manager;
SQL>grant select on SCOTT.EMP to manager;
SQL>create user clerk identified by clerk332;

SQL>grant create session to clerk;
SQL>grant select on SCOTT.EMP to clerk;
connecting as accounts MANAGER & CLERK I can see the full table data !!!

2

3.PNG

Now I will create security administrator account:
SQL> alter session set container=CDB$root;
SQL> create user security_admin identified by zorro3_g;
SQL> grant create session to security_admin;
SQL> grant create procedure to security_admin;
Now I am going to create the function:
SQL> CREATE OR REPLACE FUNCTION VPD_FUNC
(schema_v IN VARCHAR2, tbl_v IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN ‘upper(ename) = SYS_CONTEXT(”USERENV”, ”CURRENT_USER”) OR upper(job) =
SYS_CONTEXT(”USERENV”, ”CURRENT_USER”)’;
END;
/

 

4

Then as a sys user in the pluggable database PDB_ORIGIN configure the policy:
SQL> begin
sys.dbms_rls.add_policy(
object_schema=>’SCOTT’,
object_name=>’EMP’,
policy_name=>’VPD1_POLICY’,
function_schema=>’security_admin’,
policy_function=>’VPD_FUNC’,
statement_types => ‘SELECT’);
end;
/
Connecting as account “manager” I can see data related to “manager” records:

5

Same to “CLERK

6

This is a very nice security feature that many organizations can use to restrict  access to data within the table itself.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ORA-06512: at “CTXSYS.TEXTINDEXMETHODS”, line 366

while creating a domain index as a SYS user for a table…..the following error was thrown:

ERROR at line 1:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-20000: Oracle Text error:

DRG-50857: oracle error in drvxtab.create_index_tables

ORA-01031: insufficient privileges

ORA-06512: at “CTXSYS.DRUE”, line 160

ORA-06512: at “CTXSYS.TEXTINDEXMETHODS”, line 366

 

Solution:

Grant create table to the schema account user of the table.