Privilege Usage Feature

Or theory of “minimum permission”. Basically it is about giving the minimum level of privileges to a user to develop their work, giving more than what they actually use does not contribute anything in their day to day and from the point of view of security is a high risk. From a simple error by the user (to delete a table) until the user is compromised by an attack and used for other purposes than the original.

In version 12 Oracle has introduced a new feature to audit the permissions that are being used “Privilege Usage”. This trace can be in the following types of level;

 
At the database level
At the role level
At the user level

Let’s see the procedures that this package has (DBMS_PRIVILEGE_CAPTURE)

 
SQL> desc DBMS_PRIVILEGE_CAPTURE
PROCEDURE CREATE_CAPTURE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 TYPE                           NUMBER                  IN     DEFAULT
 ROLES                          ROLE_NAME_LIST          IN     DEFAULT
 CONDITION                      VARCHAR2                IN     DEFAULT
PROCEDURE DISABLE_CAPTURE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
PROCEDURE DROP_CAPTURE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
PROCEDURE ENABLE_CAPTURE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
PROCEDURE GENERATE_RESULT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN

The basic steps are the following;

  • Create a trace
  • Activate trace
  • user work as usual
  • Stop trace
  • Generate report

Before to start create a user for this sample;

 
SQL>
SQL> -- Create a sample user and grant dba role
SQL> create user test identified by test1234567890;

User created.

SQL> grant dba to test;

Grant succeeded.

SQL>

Create the trace. Capture for user TEST;

 
SQL>
SQL> -- Create capture for user test  --------------------------
SQL> BEGIN
  2  DBMS_PRIVILEGE_CAPTURE.create_capture(
  3  name        => 'capture_for_user_test',
  4  type        => DBMS_PRIVILEGE_CAPTURE.g_context,
  5  condition   => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''TEST'''
  6  );
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>

Now activate the trace

 
SQL>
SQL> -- Activate the trace ------
SQL> BEGIN
  2  DBMS_PRIVILEGE_CAPTURE.enable_capture(
  3  name        => 'capture_for_user_test'
  4  );
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>

For other session log on like test user and execute a pair of commands;

 
$ 
$ sqlplus test/test1234567890

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 27 14:31:59 2018

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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> show user
USER is "TEST"
SQL> create table trial as select * from dba_objects;

Table created.

SQL> select count (*) from dba_users;

  COUNT(*)
----------
      1329

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
$ 

Stop the trace;

 
SQL> -- Stop the trace -----
SQL> BEGIN
  2  DBMS_PRIVILEGE_CAPTURE.disable_capture(
  3  name        => 'capture_for_user_test'
  4  );
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>

Generate a new report;

 
SQL> -- Generate the report by insert data into the DBA views associated -----
SQL> BEGIN
  2  DBMS_PRIVILEGE_CAPTURE.generate_result(
  3  name        => 'capture_for_user_test'
  4  );
  5  END;
  6  /

PL/SQL procedure successfully completed.

Select the data from DBA views

 
SQL> COLUMN name FORMAT a25
SQL> COLUMN roles FORMAT a20
SQL> COLUMN context FORMAT a30
SQL> select name,type,roles,context FROM dba_priv_captures;

NAME                      TYPE             ROLES                CONTEXT
------------------------- ---------------- -------------------- ------------------------------
capture_for_user_test     CONTEXT                               SYS_CONTEXT('USERENV', 'SESSIO
                                                                N_USER') = 'TEST'

SQL> select username, sys_priv from dba_used_sysprivs where capture = 'capture_for_user_test';

USERNAME   SYS_PRIV
---------- ----------------------------------------
TEST       CREATE TABLE
TEST       UNLIMITED TABLESPACE
TEST       CREATE SESSION

SQL>

HTH – Antonio NAVARRO

Advertisements

Deleting Thousands Of Files

Today I landed in a system that had a full filesystem, in this case because there was no maintenance of audit files or directly this holding the process that does this work. The purpose of this is to show a simple way to delete the files. Of course, there are many, as can be found and tell you to delete the files that meet the search criteria.

First of all count the files to delete and create a file with the name of all thems

 
grid12@moti1./app/oracle/grid/rdbms/audit $ ls -rlt | wc -l
  174958
grid12@moti1./app/oracle/grid/rdbms/audit $ time ls > report.txt

real    0m6.05s
user    0m5.72s
sys     0m0.32s

 

Below I show a little shell script (bash shell like shebang) that read line by line and perform a rm in each file

 
#!/bin/bash
while IFS='' read -r line || [[ -n "$line" ]]; do
   rm $line
done < "$1"

 

Now, set execute permissions and run the script;

 
grid12@moti1./app/oracle/grid/rdbms/audit $ chmod 700 delete.sh
grid12@moti1./app/oracle/grid/rdbms/audit $ time delete.sh report.txt

real    12m20.47s
user    0m35.63s
sys     2m4.13s

In this case, delete about 170.000 files takes 12 minutes, maybe a little slow.

 

If we analyze the times, the total was twelve minutes from the start until the end. The user and sys fields are CPU time. The user part corresponds to our process and the system part represents the kernel time.

total cpu = 0m35.63s + 2m4.13s = 2m39.76s

if we subtract this total time from cpu of total time of execution of the process

12m20.47s – 2m39.76s = 9m40.71s

These 9m40.71s, although we would have to trace it or see the consumptions (using for example IOCTL) and since we are working with files, that time goes on disk.

 
Remark that in this setup the temporary file contains the file list to delete is dropped. However delete.sh file is not dropped. I usally drop it like housekeeping policy.

 
rm delete.sh

HTH – Antonio NAVARRO

Relocate MGMTDB Database To Another Node

Hello, today I would like to show how to move the MGMTDB database within the nodes of a cluster. This database (optional until version 12.1 if I remember correctly) is used mainly to store the telemetry that Oracle collects on the operation of the cluster on which it is executed.

In my case, I’m going to move it because I need to launch the cluster monitor (oclumon) from node 1. The main benefit of running the oclumon and that the database MGMTDB are in the same node is to reduce network traffic in the Interconnect (or private network).

 
First, check where the database is running;

griduser@node-1 $ srvctl status mgmtdb
Database is enabled
Instance -MGMTDB is running on node node-2

Now execute the relocate to node one;

griduser@node-1 $ srvctl relocate mgmtdb -node node-1
griduser@node-1 $

Check again;

griduser@node-1 $ srvctl status mgmtdb
Database is enabled
Instance -MGMTDB is running on node node-1
griduser@node-1 $

You can also check the presence of the pmon with the world famous command “ps -ef | grep pmon” as shown below;

griduser@node-1 $ ps -ef | grep pmon
    grid 10311     1   0   Nov 14 ?           6:12 asm_pmon_+ASM1
    grid  7723     1   0   Nov 14 ?           5:52 apx_pmon_+APX1
  oracle 10582     1   0   Nov 14 ?           9:59 ora_pmon_CLOUD1
    grid 29146     1   0 15:40:09 ?           0:00 mdb_pmon_-MGMTDB

 

HTH – Antonio NAVARRO

 

ORA-15056, ORA-15221 Errors

Today creating a password file into a ASM I get the next error;

 
oracle@lince-1:...e/prod/server/product/12.1/dbs$
oracle@lince-1:...e/prod/server/product/12.1/dbs$ orapwd file='+data' password=secret dbuniquename=oradb entries=10

OPW-00010: Could not create the password file.
ORA-15056: additional error message
ORA-15221: ASM operation requires compatible.asm of 12.1.0.0.0 or higher
ORA-06512: at line 4

The issue is enough clear, compatible asm requieres a higer value. If check the compatibility like show below;

 
SQL> select name, COMPATIBILITY, DATABASE_COMPATIBILITY from v$asm_diskgroup

NAME                           COMPATIBILITY                                                DATABASE_COMPATIBILITY
------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
REDCONT_1                      10.1.0.0.0                                                   10.1.0.0.0
REDCONT_2                      10.1.0.0.0                                                   10.1.0.0.0
ARCHIVE_1                      10.1.0.0.0                                                   10.1.0.0.0
OCRVTD_DG                      12.1.0.0.0                                                   10.1.0.0.0
ACFS_LOADS                     12.1.0.0.0                                                   10.1.0.0.0
ACFS_UTILITIES                 12.1.0.0.0                                                   10.1.0.0.0
DATA                           10.1.0.0.0                                                   10.1.0.0.0
FRA                            10.1.0.0.0                                                   10.1.0.0.0
ACFS_DIAG                      12.1.0.0.0                                                   10.1.0.0.0

With grid infrastructure user we can change the compatible with the next command;

 
SQL> alter diskgroup DATA set attribute 'compatible.asm'='12.1.0.0.0';

Diskgroup altered.

If check the compatibility again

 

 
SQL> select name, COMPATIBILITY, DATABASE_COMPATIBILITY from v$asm_diskgroup

NAME                           COMPATIBILITY                                                DATABASE_COMPATIBILITY
------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
REDCONT_1                      10.1.0.0.0                                                   10.1.0.0.0
REDCONT_2                      10.1.0.0.0                                                   10.1.0.0.0
ARCHIVE_1                      10.1.0.0.0                                                   10.1.0.0.0
OCRVTD_DG                      12.1.0.0.0                                                   10.1.0.0.0
ACFS_LOADS                     12.1.0.0.0                                                   10.1.0.0.0
ACFS_UTILITIES                 12.1.0.0.0                                                   10.1.0.0.0
DATA                           12.1.0.0.0                                                   10.1.0.0.0
FRA                            10.1.0.0.0                                                   10.1.0.0.0
ACFS_DIAG                      12.1.0.0.0                                                   10.1.0.0.0

Try the create passwd file again;

 
oracle@lince-1:...e/prod/server/product/12.1/dbs$ orapwd file='+data' password=secret dbuniquename=oradb entries=10
oracle@lince-1:...e/prod/server/product/12.1/dbs$

HTH – Antonio NAVARRO

 

It Is Necessary To Relink Grid Infrastructure After Applying OS Patch

The question of the title of this post I have heard many times, but today I will answer for the part of Clusterware. The database engine I leave for another post ;). According to the Oracle documentation: “You must relink the Oracle Clusterware and Oracle ASM binaries every time you apply an operating system patch or after an operating system upgrade.”

Of course, and this is the procedure;

As root:

 
# cd Grid_home/crs/install
# rootcrs.sh -unlock

As theGrid Infrastructure owner:

 
$ export ORACLE_HOME=Grid_home
$ Grid_home/bin/relink

As root:

 
# cd Grid_home/rdbms/install/
# ./rootadd_rdbms.sh
# cd Grid_home/crs/install
# rootcrs.sh -patch

HTH – Antonio NAVARRO.

Update The Project Is Not Reflected In The Installer

I’m installing a grid infrastructure, the issue is that give an error by the limit of open files, as shown in the screenshot below.

foto_installer_fail_reading_projects_v2

The error itself is quite simple and easy to fix. The problem is that by saying to the installer that it returns to perform the check, it will fail with the same error. Basically when we login with the our user to the operating system, the project is read and these values ​​are fixed (as hardcode) to the session.

I have been researching (Google, MOS / ML,  Stackoverflow,) to see if there is any way to force the user to read the permissions again, but I have not found anything. If someone knows how their help is always welcome.

In my case I have solved it by closing the installer, logout the session and reconnecting to the machine, logically returned to execute the installer from the beginning.

HTH – Antonio NAVARRO

 

Using SSH with Jumped Host (With MobaXterm)

It may be strange to want to open an SSH connection using an SSH server to make a jump. The logical thing would be to connect directly to the destination server with SSH.

Everything has a because, in the case that I expose is because a provider has to connect from outside the local network to a server, but this provider only have access to a single machine from outside, can not go directly to the destination ( limitations of the communications department through firewalls and ACLs). So far what it does is connect to the input computer from the internet with ssh and from the open session run an ssh to the target machine.

Open MobaXterm

moba_ssh1

Choose the SSH conection

 

moba_ssh2

Set up the target host

 

moba_ssh3

Now;

Select Network settings tab.

Check Connect through SSH gateway (jump host), in my case is the  entry server to the local network.

moba_ssh4GIF

Push OK button. That’s all, so easy

 

HTH – Antonio NAVARRO