DBSAT in 12c .

Oracle has provided a new tool to assess the security configuration and advise on it. It is a lightweight utility and the most important thing is that it is FREE ( surprise!).  As long as , we have the active support from Oracle , we should be able to download the utility .

For the first release , it is very promising  , albeit the findings for Windows Operating Systems is limited. For Windows , it does not look the OS configurations. 

a) First , a user needs to be created with minimalist privileges . The documentation has the sample script that could be provisioned . 

create user dbsat_user identified by xxxxx;

// If Database Vault is enabled, connect as DV_ACCTMGR to run this command

grant create session to dbsat_user;

grant select_catalog_role to dbsat_user;

grant select on sys.registry$history to dbsat_user;

grant select on sys.dba_users_with_defpwd to dbsat_user; // 11g and 12c

grant select on audsys.aud$unified to dbsat_user; // 12c only

grant audit_viewer to dbsat_user; // 12c

grant capture_admin to dbsat_user;// 12c covers sys.dba_priv_captures, sys.priv_capture$, sys.capture_run_log$

// if Database Vault is enabled, connect as DV_OWNER to run this command

grant DV_SECANALYST to dbsat_user;

 
b) We need to "collect" the configuration  by running the  commands below. As usual , this will run " as the administrator". At the event of  the process , it will ask for the password . Make sure that it is secured as the file has very important information.  

D:\dbsat_output>set path=%path%;d:\dbsat

D:\dbsat_output>dbsat collect dbsat_user/xxxxxxxxxx dbsat_output20171024

This tool is intended to assist in you in identifying potential

vulnerabilities in your system, but you are solely responsible for

your system and the effect and results of the execution of this tool

(including, without limitation, any damage or data loss). Further,

the output generated by this tool may include potentially sensitive

system configuration data and information that could be used by a

skilled attacker to penetrate your system. You are solely responsible

for ensuring that the output of this tool, including any generated

reports, is handled in accordance with your company's policies.

Connecting to the target Oracle database...

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 24 13:01:05 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

Database Security Assessment Tool version 1.0.2 (October 2016)

Setup complete.

SQL queries complete.

OS Commands Skipped.

BEGIN

*

ERROR at line 1:

ORA-20002: Complete without OS Commands.

ORA-06512: at line 4

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

DBSAT Collector completed successfully.

Calling D:\app\oracle\product\12.1.0\dbhome_1\bin\zip.exe to encryptdbsat_output20171024.json...

Enter password:

Verify password:

  adding: dbsat_output20171024.json (164 bytes security) (deflated 88%)

zip completed successfully.

D:\dbsat_output>dir

Volume in drive D is New Volume

Volume Serial Number is 32D6-96CB

Directory of D:\dbsat_output

10/24/2017  01:01 PM              .

10/24/2017  01:01 PM              ..

10/24/2017  01:01 PM            29,227 dbsat_output20171024.zip

               1 File(s)         29,227 bytes

               2 Dir(s)  223,632,429,056 bytes free

c) We need to generate a report based on the file , generated from the last step. At the completion of this step , we will have three files ( text file , excel , html ) , that lists the recommendations. Be sure to safeguard this file , as the exposure to the outside world  would cause security risks that our database has now. 

D:\dbsat_output>dbsat report -a -n dbsat_output20171024

This tool is intended to assist in you in identifying potential

vulnerabilities in your system, but you are solely responsible for

your system and the effect and results of the execution of this tool

(including, without limitation, any damage or data loss). Further,

the output generated by this tool may include potentially sensitive

system configuration data and information that could be used by a

skilled attacker to penetrate your system. You are solely responsible

for ensuring that the output of this tool, including any generated

reports, is handled in accordance with your company's policies.

Archive:  dbsat_output20171024.zip

[dbsat_output20171024.zip] dbsat_output20171024.json password:

password incorrect--reenter:

password incorrect--reenter:

  inflating: dbsat_output20171024.json

Database Security Assessment Tool version 1.0.2 (October 2016)

DBSAT Reporter ran successfully.

Also , to generate the report , you would need python installed in your machine. 

My preference is to use the  html as it has links to the individual sections . 

It lists the vulnerability , opportunity ( to use RAS , OLS , ... ) 

This is one of the "Hands on Lab" sessions that I attended at OpenWorld 2017. It is worth looking into it , as it is a light weight and does the job in few minutes. 

For additional information , please visit  https://docs.oracle.com/cd/E76178_01/SATUG/toc.htm#SATUG-GUID-C7E917BB-EDAC-4123-900A-D4F2E561BFE9

Comments welcome. 

IDENTITY column in Oracle

Prior to Oracle 12c , there was no direct equivalent to Sybase / SQL Server / PostgreSQL  identity column . This enhancement is available in 12c. 

Under the hood , Oracle creates sequence and associate it to the base table.  The  sequence name is tagged as "ISEQ$$_XXXXX"  , where XXXX is the object id of the base table.   The record is available in xxx_sequences ( all / user / pdb) views , till the recycle bin is purged . 

To create a table with the identity column , the user needs to "CREATE TABLE" and "CREATE SEQUENCE" privilege . 

Datapump has been enhanced to accommodate this enhancement as well. Here is an example. 




SQL> show user
USER is "ZAHIR"
SQL>

SQL> Create table t_identity
  2  (  empid int GENERATED BY DEFAULT ON NULL AS IDENTITY ,
  3     empname varchar2(20)
  4     ) ;
Create table t_identity
*
ERROR at line 1:
ORA-01031: insufficient privileges

C:\Users\mohideen>sqlplus system@pdb_test

SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 20 13:04:13 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Wed Apr 20 2016 12:42:47 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SQL> grant create sequence to zahir ;

Grant succeeded.

SQL> conn zahir/password@pdb_test
Connected.
SQL>
SQL> Create table t_identity
  2  (  empid int GENERATED BY DEFAULT ON NULL AS IDENTITY ,
  3     empname varchar2(20)
  4     ) ;

Table created.

SQL> col object_name format a20
SQL> Select  object_name , object_id , object_type  from user_objects where object_name like 'T_IDENT%';

OBJECT_NAME           OBJECT_ID OBJECT_TYPE
-------------------- ---------- -----------------------
T_IDENTITY                92733 TABLE

SQL> col sequence_name format a20
SQL> Select  sequence_name , min_value  from user_sequences ;

SEQUENCE_NAME         MIN_VALUE
-------------------- ----------
ISEQ$$_92733                  1

SQL> drop table t_identity;

Table dropped.

SQL> Select  sequence_name , min_value  from user_sequences ;

SEQUENCE_NAME         MIN_VALUE
-------------------- ----------
ISEQ$$_92733                  1

SQL> purge recyclebin ;

Recyclebin purged.

SQL> Select  sequence_name , min_value  from user_sequences ;

no rows selected

Creating Common User in Oracle 12c

Oracle has introduced multi-tenant architecture in 12c. With this , we will have an instance ( Single Instance) , multiple instances ( RAC) will be hosting multiple pluggable databases ( PDBs) with in a  container database  ( CDB). 

Please note that the  CDB/PDB is extra licensing option . With the regular license , we can have a CDB with only one PDB. 

Accordingly , there are changes to how users are maintained in 12c.  There are two type of users. 

a) Common User  

b) Local User 

In short , common user is for all PDBs and Local user is local to a PDB. 


There are few rules / restrictions in creating a common user. 
One of them being , the username has to be prefixed with ( c##) . 
However  , this can be circumvented . But I would recommend against against this practice, as this is not ( likely ) supported by oracle . 

Here is an example . 


SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> create user c##zahir identified by ZahirPassword container=all;

User created.


As you can see, common user ( c##zahir) was created sucessfully . 

Let us try , by removing c##. 

SQL> create user zahirm identified by ZahirPassword container=all;
create user zahirm identified by ZahirPassword container=all
            *
ERROR at line 1:
ORA-65096: invalid common user or role name


Let us set the UNDOCUMENTED parameter to true . 
Being this is undocumented parameter ; as like many Oracle professionals , 
I would not recommend with meddling  with these  parameters unless otherwise directed by Oracle support. 

Note : This parameter also needed , when you need to create a CDB from sqlplus. 

SQL> alter session set "_oracle_script"=true;

Session altered.

SQL> create user zahirm identified by ZahirPassword container=all;

User created.

Voila . The user was created . 


Comments Welcome. 

External table Enhancements in Oracle 12c.

One of the External tables' enhancements in Oracle 12c is the ability to use wildcard characters in defining the data sources.  We can have either ? ( to denote single character substitution ) or * ( multiple character substitution ) in denoting the location of the data files . 

Here is an simple example . 

Let us create  few CSV files. 

C:\extdir>dir

 Volume in drive C has no label.

 Volume Serial Number is 8280-8864

 Directory of C:\extdir

07/06/2015  01:45 PM              .

07/06/2015  01:45 PM              ..

07/06/2015  01:37 PM                27 emp1.txt

07/06/2015  01:38 PM                26 emp2.txt

07/06/2015  01:44 PM                52 emp500.txt

               3 File(s)            105 bytes

               2 Dir(s)  168,809,664,512 bytes free

C:\extdir>type emp1.txt

101,zahir,F

102,mohamed,R

C:\extdir>type emp2.txt

201,Hameed,A

252,Abdul,R

C:\extdir>type emp500.txt

511,Balasubramaniam,A

577,Yesudas,A

501,Janaki,A

Let us create an external table with Single character in the wild card .

CREATE TABLE EMP_EXT

  (

    "EMP_ID"   VARCHAR2(1000 BYTE),

    "EMP_NAME" VARCHAR2(1000 BYTE),

    "EMP_TYPE" VARCHAR2(1000 BYTE)

  )

  ORGANIZATION EXTERNAL

  (

    TYPE ORACLE_LOADER DEFAULT DIRECTORY "EX_TAB_DIR"

    ACCESS PARAMETERS

    ( debug = 3

     RECORDS DELIMITED BY NEWLINE

     CHARACTERSET we8mswin1252

     BADFILE EX_TAB_DIR:'emp_ext.bad_xt'

     LOGFILE 'emp_ext.log_xt'

     FIELDS TERMINATED BY ","

     LDRTRIM MISSING FIELD VALUES ARE NULL

     REJECT ROWS WITH ALL NULL FIELDS

    ) LOCATION ( 'emp?.txt')

  )

REJECT LIMIT UNLIMITED ;

SQL> Select * from emp_ext ;

EMP_I EMP_NAME                  EMP_T

----- ------------------------- -----

101   zahir                     F

102   mohamed                   R

201   Hameed                    A

252   Abdul                     R

As we have given '?' in the wildcard , the external table created above did not take in the records from emp500.txt. 

Let us create another table with '*' in the wildcard as below. 

CREATE TABLE EMP_EXT_MULTI

  (

    "EMP_ID"   VARCHAR2(1000 BYTE),

    "EMP_NAME" VARCHAR2(1000 BYTE),

    "EMP_TYPE" VARCHAR2(1000 BYTE)

  )

  ORGANIZATION EXTERNAL

  (

    TYPE ORACLE_LOADER DEFAULT DIRECTORY "EX_TAB_DIR" 

    ACCESS PARAMETERS 

    ( debug = 3 

     RECORDS DELIMITED BY NEWLINE 

     CHARACTERSET we8mswin1252 

     BADFILE EX_TAB_DIR:'emp_m_ext.bad_xt' 

     LOGFILE 'emp_m_ext.log_xt' 

     FIELDS TERMINATED BY "," 

     LDRTRIM MISSING FIELD VALUES ARE NULL 

     REJECT ROWS WITH ALL NULL FIELDS 

    ) LOCATION ( 'emp*.txt')

  ) 

REJECT LIMIT UNLIMITED ;

SQL> Select * from EMP_EXT_MULTI ;

EMP_I EMP_NAME                  EMP_T

----- ------------------------- -----

101   zahir                     F

102   mohamed                   R

201   Hameed                    A

252   Abdul                     R

511   Balasubramaniam           A

577   Yesudas                   A

501   Janaki                    A

As can be seen from the above example , the external table did bring in the contents of all files starting with the filenames 'emp.....txt'.

- Zahir 

ORA-01720 ... Error while recreating a view in 12c

We were testing some of my scripts for 12c upgrade . There were few changes to few views . So , we changed the script and deploy it . 

The script was similar to the following 

Create or replace view v_emp_access .... 

When we execute the view , we got the following error .

Error at Command Line : 24 Column : 8

Error report -

SQL Error: ORA-01720: grant option does not exist for 'SCOTT.EMP'

01720. 00000 -  "grant option does not exist for '%s.%s'"

*Cause:    A grant was being performed on a view and the grant option was

           not present for an underlying object.

*Action:   Obtain the grant option on all underlying objects of the view.

All the grants have been provisioned and everything works in lower versions. 

 

After few minutes of R&D , found that the behavior was changed by Oracle from 11.2.0.4 onwards. The resolution is to either drop all the grants and recreate the view or drop the view ( thus dropping the grants )  and recreate it again.  

Per Oracle , this is not a bug ; works as designed. 

You can refer to MOS article (Doc ID 1628033.1) for additional information .

Renaming PDB

Oracle 12c has introduced CDB/ PDB architecture . Prior to 12c , Only one database can  be serviced by an instance ( in a Single Instance ) / by instances ( in RAC) . In other words , no more than one databases can be serviced by an instance(s). 

In Oracle 12c, we can have ( pluggable) databases that are  contained with in a  (container) database. Container database will not have user data , where as the pluggable database will have the user data. This is almost synonymous with Sybase / MS SQL Server or DB2.  Loosely speaking , we can equate Container database as to Combination of System (master , msdb , model and resource ) databases in Sybase / MS-SQL Server. 

Note : With the standard license , we can one CDB and one PDB . We would need additional license , if we are planning to have more than one PDB. 

Let us say , we created a database with DBCA and asked for 2 PDBs with the pdb_docs, then we will have a CDB ( whatever we have given ) , PDB seed  and two pdbs with pdb_docs1 and pdb_docs2. 

As you can see , OXYZ12DB is a the containter database .

C:\>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 22 13:37:02 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select name , CDB  from v$database;

NAME      CDB
--------- ---
OXYZ12DB  YES

SQL> select name , open_mode , restricted from v$pdbs;

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB$SEED                       READ ONLY  NO
PDB_DOCS1                      READ WRITE YES

PDB_DOCS2                      READ WRITE NO

Now , let us see how we can change the name of pdb_docs2 to pdb_noncredit .
We need to be connected the correct PDB and it needs to be in restricted mode. 

C:\>sqlplus sys@pdb_docs2 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 22 13:50:16 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

SQL> select name , open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------

PDB_DOCS2                      READ WRITE


We need to open the PDB in restricted mode , so we can its change its name. 

SQL>  alter pluggable database pdb_docs2 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb_docs2 open restricted;

Pluggable database altered.

SQL> select name , open_mode , restricted from v$pdbs;

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB_DOCS2                      READ WRITE YES



SQL> alter pluggable database pdb_docs2  rename global_name to pdb_noncredit;

Pluggable database altered.

SQL> select name , open_mode , restricted from v$pdbs;

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB_NONCREDIT                  READ WRITE YES

Here we go. Now the PDB's name is changed to PDB_NONCREDIT.

Make sure that you change your tnsnames.ora to reflect the new name . 

Comments welcome.

Partitioning made easy - Part 2

In one of my previous post (http://mfzahirdba.blogspot.com/2014/12/partitioning-made-easy.html)  , I have mentioned about few of the partioning enhancements in Oracle 12c. 


In Oracle 12c , creation of partial indexes have made easy to maintain and administer.  Partial indexes helps us in saving space , index maintenance operations such as rebuilding / gathering statistics , so on. 

One way is to do that is , to define the partitioned table as indexing OFF / ON at the table level and then define indivdual partions to set indexing OFF  / ON. The second step is to define the partioned index as PARTIAL. 

Here is the example . 

For my business case , only the courses that were offered for the last three years are actively queried on ... In this case ,  I am setting these three partitions ( course_part_2013 , course_part_2014 , course_part_2015) to have index on the dataset. 


SQL> Create table course
  2  ( course_year int ,
  3    course_term varchar2(1) ,
  4    course_index varchar2(6) ,
  5    course_code varchar2(12)
  6    )
  7    indexing off
  8    PARTITION by range ( course_year)
  9    (
 10    partition course_part_2010 values less than ( 2011) indexing off,
 11    partition course_part_2011 values less than ( 2012) indexing off,
 12    partition course_part_2012 values less than ( 2013) indexing off,
 13    partition course_part_2013 values less than ( 2014) indexing on,
 14    partition course_part_2014 values less than ( 2015) indexing on,
 15    partition course_part_2015 values less than ( 2016) indexing on
 16    );

Table created.

Now , I am creating global PARTIAL index as below .

SQL>    Create index idx_course on course(course_index)  global indexing partial ;

Index created.


SQL>   Select
  2    table_name , partition_name ,  indexing
  3    from user_tab_partitions
  4    where table_name ='COURSE'  ;

TABLE_NAME           PARTITION_NAME                 INDE
-------------------- ------------------------------ ----
COURSE               COURSE_PART_2010               OFF
COURSE               COURSE_PART_2011               OFF
COURSE               COURSE_PART_2012               OFF
COURSE               COURSE_PART_2013               ON
COURSE               COURSE_PART_2014               ON
COURSE               COURSE_PART_2015               ON

6 rows selected.


SQL>  Select
  2    table_name , index_name , indexing
  3    from user_indexes
  4    where table_name ='COURSE' ;

TABLE_NAME           INDEX_NAME           INDEXIN
-------------------- -------------------- -------
COURSE               IDX_COURSE           PARTIAL



This is one of the good ones in Oracle 12c.