PL/SQL Convert Rows to Delimited String

SELECT SUBSTR (SYS_CONNECT_BY_PATH (name , ‘,’), 2) OBJECT_NAME INTO l_tenant_objects
FROM (
SELECT OBJ.name , ROW_NUMBER () OVER (ORDER BY OBJ.name ) rn,
COUNT (*) OVER () cnt
FROM OBJECTS OBJ
INNER JOIN (SELECT MAX(OBJ.OBJECT_ID) DIST_OBJ_ID,OBJ.NAME FROM OBJECTS OBJ
INNER JOIN AUTHORIZATION_OBJECTS AO
ON OBJ.AUTH_OBJECT_ID = AO.AUTH_OBJECT_ID
INNER JOIN AUTH_OBJECT_TYPES AOT
ON AOT.AUTH_OBJ_TYPE_ID = AO.AUTH_OBJ_TYPE_ID
AND AOT.CODE = ‘AUTH_OBJ_TYPE_TENANT’
GROUP BY NAME
) DISTINCTED_OBJECTS
ON OBJ.OBJECT_ID = DISTINCTED_OBJECTS.DIST_OBJ_ID
)
WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

Advertisements

Oracle Virtual Private Database ( VPD ) – Monitoring Policy Predicate

When started on new fresh database Oracle 11g Release 2, there might occur following messages:

Note
—–
cpu costing is off (consider enabling it)
– Warning: basic plan statistics not available. These are only collected when:
* hint ‘gather_plan_statistics’ is used for the statement or
* parameter ‘statistics_level’ is set to ‘ALL’, at session or system

—-

To get rid of those messages on the development or testing environments, we can alter 2 system parameters:

1. exec DBMS_STATS.GATHER_SYSTEM_STATS;

2. ALTER SYSTEM set STATISTICS_LEVEL = ‘ALL’;

Microsoft Windows Operatin System Version Value

Knowing these values is good to know when software you want to install does not support my actual version of Windows. Usually there are available in the installers some configuration files (xml, ini, …etc.) which I can extract, change the values and repack the installer, to reflect your M$ OS version.

Of course ūüôā I can end up with not working software even installer finished without error because of some lower dll compatibility issues (functions missing, etc. but even that can be solved, but in different story). So for such basic overriding purposes here I have the microsoft windows operating system version table:

System VersionNT WindowsBuild ServicePackLevel
Windows 2000 500 2195 Not applicable
Windows 2000 + Service Pack 1 500 2195 1
Windows 2000 + Service Pack 2 500 2195 2
Windows 2000 + Service Pack 3 500 2195 3
Windows 2000 + Service Pack 4 500 2195 4
Windows XP 501 2600 Not applicable
Windows XP with Service Pack 1 (SP1) 501 2600 1
Windows XP with Service Pack 2 (SP2) 501 2600 2
Windows XP with Service Pack 3 (SP3) 501 2600 3
Windows Server 2003 502 3790 Not applicable
Windows Server 2003 with Service Pack 1 (SP1) 502 3790 1
Windows Server 2003 with Service Pack 2 (SP2) 502 3790 2
Windows Vista 600 6000 Not applicable
Windows Vista with Service Pack 1 (SP1) 600 6001 1
Windows Vista with Service Pack 2 (SP2) 600 6002 2
Windows Server 2008 600 6001 Not applicable
Windows Server 2008 with Service Pack 2 (SP2) 600 6002 2
Windows Server 2008 R2 601 greater than 7100 Not applicable
Windows 7 601 greater than 7100 Not applicable
Windows 7 with Service Pack 1 (SP1) 601 greater than 7100 1
Windows 8 602 greater than or equal to 9200 Not applicable
Windows Server 2012 602 greater than or equal to 9200 Not applicable

Linux corrupted file system under vmware

Back due to some unexpected reboots probably the vmware file was somehow corrupted. Running fsck, or even trying to recover the superblock from backup looked like working but wasn’t. Tried also under host system to repair the disk with vmware-vdiskmanager utility, and it reported fix, but after some few starts it again reported issues.

Under guest mostly there was a problem with buffer IO device cannot be accessed, after trying to connect the disk in host as IDE, SATA or SCSI, I end up with creating completely new drive under vmware workstation, booted with systemrescueCD linux distribution cloned old /dev/sda to new /dev/sdb with utility ddrescue by issuing commands:

#ddrescue -n /dev/sda /dev/sdb ddreslog.txt

and

#ddrescue -r1 /dev/sda /dev/sdb ddreslog-retry.txt

After this, I removed from the guest first corrupted disk in vmware and let the second as replacement. After boot, the filesystems in LVM group were again corrupted, but I was already able to fix the filesystem ext4 with standard tools. So the resolution was that even vmware-vdiskmanager repaired the vmware disk, it was still somehow corrupted anyway and prevent me to fix in standard way.

Some other sources: https://sites.google.com/a/kossboss.com/main/linux—how-to-clone-a-disk-with-ddrescue—dnu-ddrescue-also-known-as-gddrescue—the-better-ddrescue-tool?pli=1

Deal with DBMS_RLS the devil

These last weeks I am trying to get the VPD option (Virtual Private Database) enabled for my application. This time as a developer with no DBA or SYS privileges. I am used to being a DBA for the databases I work on and am used to being able to get the fullest out of the Oracle database. For one, because I think Oracle provides us with a great deal of built-in features that do things more efficiently than we can do ourselves (more efficient, built-in kernel code) and also especially because it seems useless and time-consuming to write code that is already supplied by built-in packages. Now these provided features may not all be as intuitive as we want to or lack some important features, but that is where we can spend some time extending them or making them more user-friendly by creating ‚Äúwrapper‚ÄĚ packages: packages you write that hide the complexity or unfriendliness of a built-in package or limit the features you can use. Because there are quite some packages out there that contain functions or procedures or even parameters that you do not want to give to your developers or end users.

An example of such a package that is too powerful to grant to your end-users as a whole is DBMS_RLS. This package enables you to put Row Level Security (and even Column Level Security) on your schema objects. When you take a look at most tutorials available about the VPD option, all of them simply state that you should grant EXECUTE on DBMS_RLS to the schema owner so that it can add, drop, enable, disable policies on its objects to limit the data an end-user of the object can see or manipulate.

First, I love the feature. It is a way of declaratively filtering data based on business criteria dependent on the logged in user/location/terminal/anything that can be set or queried as a context variable. It is so much cleaner than having the same kind of predicates sitting around in all parts of the application, almost knowing for sure that:

  • something will have been missed somewhere and people see data they should not
  • or will change in the future and turns in to a maintenance nightmare
  • or can be circumvented

Why is DBMS_RLS too powerful in ‚Äúmy‚ÄĚ opinion? At least I made it my opinion also after consulting with¬†a DBA at the¬†company where¬†I need¬†the VPD feature,¬†who knows what it is like to manage several hundreds of databases and knows the challenge of maintaining some level of standardization without compromising security but on the other hand providing application developers with a toolset they can solve their problems with. I did not think of it at first when asking for EXECUTE privileges on DBMS_RLS for my application, but he mentioned that DBMS_RLS is not preventing me from hurting someone else‚Äôs policies:

If a database contains schema owners SCOTT and TOINE and both need VPD for their schema objects‚Ķthe tutorials state that both should get execute privileges on DBMS_RLS. But that does not prevent SCOTT from dropping policies of user TOINE. Me as TOINE would hate the idea that someone else can touch my ‚Äėrules‚Äô. As it turns out there is more about securing DBMS_RLS that goes beyond this simple article (SQL Injection and XMLDB or some other built-in packages can dig a hole into your beautiful VPD security, see for such an example http://www.oracle-dox.net/Wiley-The.Oracle.Hacker.s.Hand/final/BBL0043.html or Pete Finnigan‚Äôs presentation http://www.petefinnigan.com/Oracle_Security_VPD_2009_6slides.pdf). Also there is a lot of useful information to be found for a hacker in a number of data dictionary views.

So how would a DBA that needs to standardize hundreds of databases enable a developer to use DBMS_RLS? Several options, one better than the other:

  • He or she could execute any policy manipulation (enable/disable/add/drop) on behalf of the developer. This requires tickets to be raised and will frustrate the developer who has to wait for the DBA to find time to execute that task out of his growing queue of such tasks
  • I like the wrapper package idea: create a wrapper package on top of DBMS_RLS, it can be as simple as providing all subprograms, but then WITHOUT the object_schema parameter. Instead of having the developer provide the object_schema, YOU take control and allow that developer to only manipulate policies on objects of its own. Instead of a grant EXECUTE on DBMS_RLS, a grant EXECUTE on the wrapper package is provided.

So DBMS_RLS looks like (In Oracle 10R2):

CREATE OR REPLACE PACKAGE SYS.dbms_rls AS
  STATIC                     CONSTANT   BINARY_INTEGER := 1;
  SHARED_STATIC              CONSTANT   BINARY_INTEGER := 2;
  CONTEXT_SENSITIVE          CONSTANT   BINARY_INTEGER := 3;
  SHARED_CONTEXT_SENSITIVE   CONSTANT   BINARY_INTEGER := 4;
  DYNAMIC                    CONSTANT   BINARY_INTEGER := 5;
  DV_INTERNAL                CONSTANT   BINARY_INTEGER := 10;
  ALL_ROWS                   CONSTANT   BINARY_INTEGER := 1;

  PROCEDURE add_policy(object_schema   IN VARCHAR2 := NULL,
                       object_name     IN VARCHAR2,
                       policy_name     IN VARCHAR2,
                       function_schema IN VARCHAR2 := NULL,
                       policy_function IN VARCHAR2,
                       statement_types IN VARCHAR2 := NULL,
                       update_check    IN BOOLEAN  := FALSE,
                       enable          IN BOOLEAN  := TRUE,
                       static_policy   IN BOOLEAN  := FALSE,
                       policy_type     IN BINARY_INTEGER := NULL,
                       long_predicate BOOLEAN  := FALSE,
                       sec_relevant_cols IN VARCHAR2  := NULL,
                       sec_relevant_cols_opt IN BINARY_INTEGER := NULL); 

  PROCEDURE drop_policy(object_schema IN VARCHAR2 := NULL,
                        object_name   IN VARCHAR2,
                        policy_name   IN VARCHAR2);

[intentionally left out the rest of subprograms)
END dbms_rls;
/

A wrapper package, which can be created in any DBA managed schema (it might be a dedicated VPD admin schema) could look like:

CREATE OR REPLACE PACKAGE VPDADMIN.secure_dbms_rls AS
  STATIC                     CONSTANT   BINARY_INTEGER := 1;
  SHARED_STATIC              CONSTANT   BINARY_INTEGER := 2;
  CONTEXT_SENSITIVE          CONSTANT   BINARY_INTEGER := 3;
  SHARED_CONTEXT_SENSITIVE   CONSTANT   BINARY_INTEGER := 4;
  DYNAMIC                    CONSTANT   BINARY_INTEGER := 5;
  DV_INTERNAL                CONSTANT   BINARY_INTEGER := 10; 
  ALL_ROWS                   CONSTANT   BINARY_INTEGER := 1;

  PROCEDURE add_policy(object_name   IN VARCHAR2,
                       policy_name     IN VARCHAR2,
                       function_schema IN VARCHAR2 := NULL,
                       policy_function IN VARCHAR2,
                       statement_types IN VARCHAR2 := NULL,
                       update_check    IN BOOLEAN  := FALSE,
                       enable          IN BOOLEAN  := TRUE,
                       static_policy   IN BOOLEAN  := FALSE,
                       policy_type     IN BINARY_INTEGER := NULL,
                       long_predicate BOOLEAN  := FALSE,
                       sec_relevant_cols IN VARCHAR2  := NULL,
                       sec_relevant_cols_opt IN BINARY_INTEGER := NULL);

  PROCEDURE drop_policy(object_name   IN VARCHAR2,
                        policy_name   IN VARCHAR2);

  [intentionally left out the rest of subprograms)
END dbms_rls;
/

In the body of this package the subprograms can simply call DBMS_RLS. Add_policy and drop_policy like this:

CREATE OR REPLACE PACKAGE BODY VPDADMIN.secure_dbms_rls AS
     PROCEDURE add_policy(object_name   IN VARCHAR2,
                          policy_name     IN VARCHAR2,
                          function_schema IN VARCHAR2 := NULL,
                          policy_function IN VARCHAR2,
                          statement_types IN VARCHAR2 := NULL,
                          update_check    IN BOOLEAN  := FALSE,
                          enable          IN BOOLEAN  := TRUE,
                          static_policy   IN BOOLEAN  := FALSE,
                          policy_type     IN BINARY_INTEGER := NULL,
                          long_predicate BOOLEAN  := FALSE,
                          sec_relevant_cols IN VARCHAR2  := NULL,
                          sec_relevant_cols_opt IN BINARY_INTEGER := NULL)
  IS
   BEGIN
     sys.dbms_rls.add_policy(USER,
                             object_name,
                             policy_name,
                             function_schema,
                             policy_function,
                             statement_types,
                             update_check,
                             enable,
                             static_policy,
                             policy_type,
                             long_predicate,
                             sec_relevant_cols,
                             sec_relevant_cols_opt);
   END; 
  [intentionally left out the rest of subprograms)
END dbms_rls;
/

The only disadvantage I can think of is portability of the code: if the target database is not following the same principle, you would have to alter your package name. But if you call your add_policy program using named parameters instead of positional…then the package name may be the only thing you would have to change…since DBMS_RLS takes the current user as the default if none is provided:

BEGIN
   VPDADMIN.secure_dbms_rls.add_policy
                             (object_name          => 'mytable',
                              policy_name          => 'mypolicy',
                              function_schema      => 'myschema',
                              policy_function      => 'mypackage.myfunction',
                              statement_types      => 'select, update, delete'
                             );
END;

I am currently in the middle of discussing this wrapper option…I hope to be able to use VPD as wanted soon. Anyone feeling differently on grants on DBMS_RLS?

Dehydration in BPEL – Oracle SOA Suite 11g

Dehydration in BPEL – Oracle SOA Suite 11g

Dehydration – Offers Reliability, fail-over protection

Over the life cycle of a BPEL instance, the instance with its current state of execution may be saved in a database. When a BPEL instance is saved to a database, the instance is known as being dehydrated. The database where the BPEL instance is saved is called a dehydration store.

Once a BPEL instance is dehydrated, Oracle BPEL Server can off load it from the memory of Oracle BPEL Server. When a certain event occurs, such as the arrival of a message or the expiration of a timer, Oracle BPEL Server locates and loads the persistent BPEL instance from the dehydration store back into the memory of Oracle BPEL Server and resumes the execution of the process instance. Dehydrating BPEL instances offers reliability. If Oracle BPEL Server crashes in the middle of executing a process, the instance can be recovered automatically, programmatically, or manually from the dehydrated states. When Oracle BPEL Server resumes the execution of the process instance, it resumes from the last dehydration point, which is the last state of the instance that Oracle BPEL Server saves to the dehydration store.

               When and how the dehydration occurs differs based on the process types:

‚Ė† Transient process ‚ÄĒ Oracle BPEL Server dehydrates the process instance only once at the end of the process. When a host crashes in the middle of running the process instance, the instances are not visible from Oracle BPEL Control.

‚Ė† Durable process/idempotent ‚ÄĒ Oracle BPEL Server dehydrates the process instance in-flight at all midprocess breakpoint and non-idempotent activities, plus the end of the process. When the server crashes, this process instance appears in Oracle BPEL Control up to the last dehydration point (breakpoint activity) once the server restarts. If the server crashes before the process instance reaches the first midprocess breakpoint activity, the instance is not visible in Oracle BPEL Control after the server restarts.

       There are three cases in which dehydration occurs:

  1. When the BPEL instance encounters a mid-process breakpoint activity (not including the initial receive)

Activities like wait, receive, onMessage, onAlarm, call to an async WSDL

That is where an existing BPEL instance must wait for an event, which can be either a timer expiration or message arrival. When the event occurs (the alarm expires or the message arrives), the instance is loaded from the dehydration store and execution is resumed. This type of dehydration occurs only in durable processes, which have mid-process breakpoint activities. A transient process does not have any midprocess breakpoint activities.

  1. When the BPEL instance encounters a non-idempotent activity

When Oracle BPEL Server recovers after a crash, it retries the activities in the process instance. However, it should only retry the idempotent activities. Therefore, when Oracle BPEL Server encounters a nonidempotent activity, it dehydrates it. This enables Oracle BPEL Server to memorize that this activity was performed once and is not performed again when Oracle BPEL Server recovers from a crash.

Idempotent activities are those activities where the result is the same irrespective of no. of times you execute the process.

Repeated invocations have the same affect as one invocation.

Ex : Read-Only services

  1. When the BPEL instance finishes

At the end of the BPEL process, Oracle BPEL Server saves the process instance to the dehydration store, unless you explicitly configure it not to do so. This happens to both durable and transient processes. For transient processes, the end of the process is the only point where the process instance is saved. This is because a transient process does not have any mid-process breakpoint activities and nonidempotent activities where the in-flight dehydration can occur.

——————

Dehydration triggered by:

(a)Breakpoint activities: <receive>, <onMessage> (including <pick>), <onAlarm>, <wait>, and <reply>

(b)When using checkPoint()within a <bpelx:exec>activity

——————

A BPEL invoke activity is by default an idempotent activity, meaning that the BPEL process does not dehydrate instances immediately after invoke activities. Therefore, if idempotent is set to true and Oracle BPEL Server fails right after an invoke activity executes, Oracle BPEL Server performs the invoke again after restarting. This is because no record exists that the invoke activity has executed. This property is applicable to both durable and transient processes.

If idempotent is set to false, the invoke activity is dehydrated immediately after execution and recorded in the dehydration store. If Oracle BPEL Server then fails and is restarted, the invoke activity is not repeated, because Oracle BPEL Process Manager sees that the invoke already executed.

When idempotent is set to false, it provides better failover protection, but at the cost of some performance, since the BPEL process accesses the dehydration store much more frequently. This setting can be configured for each partner link in the bpel.xml file.

Some examples of where this property can be set to true are read-only services (for example, CreditRatingService) or local EJB/WSIF invocations that share the instance’s transaction.

——————–

BPEL Dehydration Stores

As already explained, when a BPEL process instance is saved in the database, it uses the schema that is configured using Repository Creation Utility(RCU) that you would use during SOA environment setup.

Here are some of the tables that a BPEL engine uses to store its current instance state.

cube_instance – stores instance metadata, eg. instance creation date, current state, title, process identifier

cube_scope – stores the scope data for an instance

work_item – stores activities created by an instance

document – stores large XML variables, etc.