Access Control Policies overview !!

There are 2 levels of access controls provided for WebSphere Commerce.
1. WAS protects EJB's and Servlets.
2. WebSphere Commerce provides low level fine grained access control  framework based on access control policies to various types of users (registered/guest/customer service rep/sales managers).
and
3. It is always recommended to guard a WebSphere Commerce Server is always by a Firewall that will help internet clients from not being able to directly access resource in WebSphere Commerce.

WebSphere Application Server layer security and access control: 

1. Servlets and EJB's are configured to be invoked only by a chosen identity through declarative security and hence during EJB creation, we select use identity of EJB server and using identity of EJB server field ensures that all EJB beans run under the same identity for security.
2. WAS provides multiple security features such as 1. Global Security. 2. security domains can be configured with different scope 3. WAS utilizes SSL for secure connection between client and server and hence any third party integration's requires certificates to be installed. 4. In cases where standard authentication is not sufficient, WAS supports JAAS for higher level of security.

WebSphere Commerce Access Control Framework: Authorization model

Info center provides a good read on users/actions/resources//relationships but i am going to start with the practical case. There are always cases when we need to implement a custom access policy but most of the times, when adding access control in *ACP.xml. There are 2 levels of access control

•    Command Level access control
•    Resource Level access control

"Access control policies are enforced by the access control Policy Manager. 

In general, when a user attempts to access a protected resource, the access control policy manager first determines what access control policies are applicable for that user 

and then, based upon the applicable access control policies, it determines if the user is allowed to perform the requested operation on the given resource".

Most developers need to get the basic view and command level entries right, which is explained below and only on few times, would get an opportunity to implement custom access policy.

1. We create actions for views and commands
<Action Name="ABCItemExtView" CommandName="ABCItemExtView" />
<Action Name="ABCSyncPersonControllerCmd" CommandName="ABCSyncPersonControllerCmd" />
<Action Name="com.custom.soi.member.commands.ABCSyncPersonControllerCmd" CommandName="com.custom.soi.member.commands.ABCSyncPersonControllerCmd"></Action>
2. Assign actions to action groups as ActionGroupAction for views 
E.g. action groups
<ActionGroup Name="ProductManagersViews" OwnerID="RootOrganization">

<ActionGroup Name="AllSiteUsersViews" OwnerID="RootOrganization">

<ActionGroup Name="RegisteredUserViews" OwnerID="RootOrganization">
<ActionGroup Name="CustomerServiceRepresentativeViews" OwnerID="RootOrganization">
Assignment example:
<ActionGroup Name="ProductManagersViews" OwnerID="RootOrganization">
      <ActionGroupAction Name="ABCItemExtView"/>

     </ActionGroup>

<ActionGroup Name="RegisteredUserViews" OwnerID="RootOrganization">
       <ActionGroupAction Name="ABCSyncPersonControllerCmd" />
</ActionGroup&gt

3. Creating resource categories for commands
ResourceBeanClass="com.custom.soi.member.commands.ABCSyncPersonControllerCmd">

4. Assigning resource category to resource groups for commands as ResourceGroupResource
e.g. Resource groups
<ResourceGroup Name="AllSiteUserCmdResourceGroup" OwnerID="RootOrganization">
<ResourceGroup Name="CustomerServiceRepCmdResourceGroup" OwnerID="RootOrganization">
<ResourceGroup Name="RegisteredUserCmdResourceGroup" OwnerID="RootOrganization">

<ResourceGroup Name="BecomeUserCmdsResourceGroup" OwnerID="RootOrganization">
Assignment example:
<ResourceGroup Name="AllSiteUserCmdResourceGroup" OwnerID="RootOrganization">
<ResourceGroupResource Name="com.custom.soi.member.commands.ABCSyncPersonControllerCmdResourceCategory" />
</ResourceGroup>

Run acpload or in V7, dataloader will take care of it as it would internally run the ACPLoad
CMD:
select * from acresgrp where acresgrp_id in
(
select acresgrp_id from acresgpres where acrescgry_id =
(select acrescgry_id from acrescgry where resclassname like '%ABCSyncPersonControllerCmd%')
)

VIEWS:
select * from acpolicy where acactgrp_id in (select acactgrp_id
from acactactgp where acaction_id
in (select ACACTION_ID from ACACTION where ACTION = 'ABCItemExtView'))

select groupname from ACACTACTGP a, ACACTGRP b where acaction_id = (select acaction_id from ACACTION where action like '%ABCItemExtView%')
and a.acactgrp_id = b.acactgrp_id


Reference:
http://pic.dhe.ibm.com/infocenter/wchelp/v7r0m0/index.jsp?topic=/com.ibm.commerce.data.doc/refs/rdmattrdict.htm

Acronyms:
WAS: WebSphere Application Server
SSL : Secure Socket Layer
JAAS: Java Authenticating and Authorization services

Struts actions AjaxComponentServiceAction !!

   
This is not your usual forward mapping to a JSP or an action mapping to a command
 This is an example of calling a component service from an ajax style request and this must be define a struts action in struts-config-ext.xml. Plese find below a snippet.

   <action parameter="member.updateAddressForPerson" path="/AjaxPersonChangeServiceAddressUpdate" type="com.ibm.commerce.struts.AjaxComponentServiceAction">
            <set-property property="authenticate" value="0:0"/>
            <set-property property="https" value="0:0"/>
            <set-property property="csrfProtected" value="0:0"/>
        </action>

e.g. Service actions above can be used in refresh areas example below. After completion of the command, the struts action forwards the JSP file  that generates a JSON , with response property containing success or failure details.

    wc.service.declare({
        id: "AjaxUpdateAddressForPerson",
        actionId: "AjaxUpdateAddressForPerson",
        url: getAbsoluteURL() + "AjaxPersonChangeServiceAddressUpdate",
        formId: ""

    /**
     * hides all the messages and the progress bar
     * @param (object) serviceResponse The service response object, which is the
     * JSON object returned by the service invocation
     */
        ,successHandler: function(serviceResponse) {
            AddressHelper.updateOrderAfterAddressUpdate();
            MessageHelper.hideAndClearMessage();
            cursor_clear();
        }
     /**
     * display an error message
     * @param (object) serviceResponse The service response object, which is the
     * JSON object returned by the service invocation
     */
        ,failureHandler: function(serviceResponse) {

            if (serviceResponse.errorMessage) {
                MessageHelper.displayErrorMessage(serviceResponse.errorMessage);
            }
            else {
                 if (serviceResponse.errorMessageKey) {
                    MessageHelper.displayErrorMessage(serviceResponse.errorMessageKey);
                 }
            }
            cursor_clear();
        }

    }),

Reference: For more information on checkout controls, please review IBM infocenter.

IP address change | could cause staleConnectionException wiith remote DB

If you guys have worked with DB2 from VM. This could happen from periodically. Just be vary to uncatalog and catalog with the new IP address.

[12:44:55:179 EDT] 0000000c SystemOut     O WC.TOOLKIT: Enterprise 7.0.0.6 /
[12:44:55:170 EDT] 0000000c SystemErr     R com.ibm.commerce.wc.appmanagement.exception.WCAppManagementException: com.ibm.websphere.ce.cm.StaleConnectionException: [jcc][t4][2055][11259][4.13.80] The database manager is not able to accept new requests, has terminated all requests in progress,
or has terminated this particular request due to unexpected error conditions detected at the target system. ERRORCODE=-4499, SQLSTATE=58009DSRA0010E: SQL State = 58009, Error Code = -4,499
[12:44:55:171 EDT] 0000000c SystemErr     R     at com.ibm.commerce.wc.version.ECDatabaseVersionInfo.readSiteTable(ECDatabaseVersionInfo.java:140)
[12:44:55:171 EDT] 0000000c SystemErr     R     at com.ibm.commerce.wc.version.ECDatabaseVersionInfo.(ECDatabaseVersionInfo.java:84)
[12:44:55:171 EDT] 0000000c SystemErr     R     at com.ibm.commerce.server.ProductInfo.getECDatabaseVersionInfo(ProductInfo.java:58)
[12:44:55:171 EDT] 0000000c SystemErr     R     at com.ibm.commerce.server.ProductInfo.getECDBVersionInfo(ProductInfo.java:96)
[12:44:55:171 EDT] 0000000c SystemErr     R     at com.ibm.commerce.server.ProductInfo.getProductVersion(ProductInfo.java:75)
[12:44:55:171 EDT] 0000000c SystemErr     R     at com.ibm.commerce.server.WcsApp.configInit(WcsApp.java:718)
[12:44:55:172 EDT] 0000000c SystemErr     R     at com.ibm.commerce.server.WcsApp.initialize(WcsApp.java:454)

Commands to uncatalog and catalog in DB2:

db2 UNCATALOG DATABASE
db2 UNCATALOG NODE
db2 catalog tcpip node remote 144.149.XX.X server 50000
db2 catalog database as at node
db2 terminate

EJB adding a resource reference in toolkit | JDE Integration

Adding a resource reference is required for multiple purposes and one example would for JDE Integration.
When you need to connect to a third party or external resource. Click the deployment descriptor the EJB project and goto the references tab and click add reference.

A resource is created ejb-jar.xml block and a resource is created ibm-ejb-jar-bnd.xmi:

Bing Integration with Jquery

Bing provides an interesting to alternative to Google maps for integration and if you want to integrate using Jquery. Please copy paste the example in a .html file and test it with a zip-code or city.

The key that I have in the example below will expire in 90 days so please use the below link to create a new key:
http://www.microsoft.com/maps/

Copy the below section after this line in a .html  file:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Use Bing Maps REST Services with jQuery to build an autocomplete box and find a location dynamically</title>
    <script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.5.1.js" type="text/javascript"></script>
    <script src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.8.10/jquery-ui.js" type="text/javascript"></script>
    <link href="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.8.10/themes/redmond/jquery-ui.css" rel="stylesheet" type="text/css" />
    <style type="text/css">
        .ui-autocomplete-loading
        {
            background: white url('images/ui-anim_basic_16x16.gif') right center no-repeat;
        }
        #searchBox
        {
            width: 25em;
        }
    </style>

    <script type="text/javascript">
        $(document).ready(function () {
            $("#searchBox").autocomplete({
                source: function (request, response) {
                    $.ajax({
                        url: "http://dev.virtualearth.net/REST/v1/Locations",
                        dataType: "jsonp",
                        data: {
                            key: "AlJKmxkiJg2u0CIDEyaTM6CWC9jQ_q1pf4_xzxPdEJoaT_KsgKRy73ksHyl24oe5",
                            q: request.term
                        },
                        jsonp: "jsonp",
                        success: function (data) {
                            var result = data.resourceSets[0];
                            if (result) {
                                if (result.estimatedTotal > 0) {
                                    response($.map(result.resources, function (item) {
                                        return {
                                            data: item,
                                            label: item.name + ' (' + item.address.countryRegion + ')',
                                            value: item.name
                                        }
                                    }));
                                }
                            }
                        }
                    });
                },
                minLength: 1,
                change: function (event, ui) {
                    if (!ui.item)
                        $("#searchBox").val('');
                },
                select: function (event, ui) {
                    displaySelectedItem(ui.item.data);
                }
            });
        });

        function displaySelectedItem(item) {
            $("#searchResult").empty().append('Result: ' + item.name).append(' (Latitude: ' + item.point.coordinates[0] + ' Longitude: ' + item.point.coordinates[1] + ')');
        }
    </script>
</head>
<body>
    <div>
        <div class="ui-widget">
            <label for="searchBox">
                Search:
            </label>
            <input id="searchBox" />
        </div>
        <div id="searchResult" class="ui-widget" style="margin-top: 1em;">
        </div>
    </div>
</body>
</html>

SQL | Joins Explained | Using Commerce tables.

This example below outlines lot of SQL Join functionality that we use in a lot of queries in our codes. The below example use commerce tables to demonstrate the joins functionality.

--EXAMPLE 2 OUTER JOIN Table XORDERITEMS:
CREATE TABLE XCATENTRY (catentry_id NUMBER,partnumber varchar2(255), lastupdate timestamp);

--2. Data insertion into table
insert into  XCATENTRY values (11,'DSC_1',sysdate-800);
insert into  XCATENTRY values (33,'DSC_3',sysdate-700);
insert into  XCATENTRY values (1010,'DSC_10',sysdate-500);
insert into  XCATENTRY values (1111,'DSC_11',sysdate-300);

output::
Tables Orders:
ORDERS_ID    PARTNUMBER    STATUS
-----------------------------------------
1        DSC_1    P
2        DSC_2    Y
3        DSC_3    c
4        DSC_4    c
5        DSC_5    c
6        DSC_4    P
7        DSC_3    y
8        DSC_7    c
9        DSC_8    P
10        DSC_9    P

Tables Catentry:
CATENTRY_ID   PARTNUMBER  LASTUPDATE
--------------------------------------------
11        DSC_1    29-08-2011 13:53:21.000000
33        DSC_3    07-12-2011 13:53:21.000000
1010        DSC_10    24-06-2012 13:53:21.000000
1111        DSC_11    10-01-2013 13:53:21.000000

Query: For fetching all partnumber from left table along with matching partnumber */

--Query 1
select o.partnumber from XORDERITEMS o left outer join xcatentry c on o.partnumber = c.partnumber;

--Query 2
select o.partnumber from XORDERITEMS o , xcatentry c where o.partnumber = c.partnumber(+);

output:
PARTNUMBER
DSC_1
DSC_3
DSC_3
DSC_4
DSC_4
DSC_8
DSC_9
DSC_7
DSC_5
DSC_2

--Query: For fetching all partnumber from right table along with matching partnumber

--QUERY
select distinct c.* from XORDERITEMS o right outer join xcatentry c on o.partnumber = c.partnumber;

--ALTERNATE QUERY:
select distinct c.* from XORDERITEMS o , xcatentry c where o.partnumber(+) = c.partnumber;

Result:
CATENTRY_ID    PARTNUMBER    LASTUPDATE
11    DSC_1    29-08-2011 13:53:21.000000
33    DSC_3    07-12-2011 13:53:21.000000
1010    DSC_10    24-06-2012 13:53:21.000000
1111    DSC_11    10-01-2013 13:53:21.000000

-- Query: For fetching all partnumber from left and right table along with matching partnumber

select o.*,c.* from XORDERITEMS o full outer  join xcatentry c on o.partnumber = c.partnumber;


ORDERS_ID    PARTNUMBER    STATUS    CATENTRY_ID    PARTNUMBER_1    LASTUPDATE
1    DSC_1    P    11    DSC_1    29-08-2011 13:53:21.000000
7    DSC_3    y    33    DSC_3    07-12-2011 13:53:21.000000
3    DSC_3    c    33    DSC_3    07-12-2011 13:53:21.000000
6    DSC_4    P
4    DSC_4    c
9    DSC_8    P
10    DSC_9    P
8    DSC_7    c
5    DSC_5    c
2    DSC_2    Y
            1111    DSC_11    10-01-2013 13:53:21.000000
            1010    DSC_10    24-06-2012 13:53:21.000000


--EXAMPLE 3 Query with date range
--Query 1: select partnumber of year 2011
select partnumber from xcatentry where to_date(to_char ( lastupdate,'DD/MM/YYYY'),'DD/MM/YYYY') BETWEEN TO_DATE('01/01/2011','DD/MM/YYYY') AND TO_DATE('31/12/2011','DD/MM/YYYY')

output:
PARTNUMBER
DSC_1
DSC_3

--Query 2: select partnumber of year 2011 and 2012 but not in orders tables
select partnumber from xcatentry c where to_date(to_char ( c.lastupdate,'DD/MM/YYYY'),'DD/MM/YYYY') BETWEEN TO_DATE('01/01/2011','DD/MM/YYYY') AND TO_DATE('31/12/2012','DD/MM/YYYY')
and not exists  (select 1 from XORDERITEMS o where o.partnumber = c.partnumber)

RESULTS:
PARTNUMBER
DSC_10

--EXAMPLE 4  Query : Count the status
select status,count(*) from orders group by status order by status;

output:
STATUS    COUNT(*)
P          4
Y          1
c          4
y          1

Tables XORDERITEMS:
ORDERS_ID         PARTNUMBER   STATUS
-----------------------------------------
1                              DSC_1   P
2                              DSC_2   Y
3                              DSC_3   c
4                              DSC_4   c
5                              DSC_5   c
6                              DSC_4   P
7                              DSC_3   y
8                              DSC_7   c
9                              DSC_8   P
10                           DSC_9   P

Tables XCatentry:
CATENTRY_ID   PARTNUMBER  LASTUPDATE
--------------------------------------------
11                           DSC_1   29-08-2011 13:53:21.000000
33                           DSC_3   07-12-2011 13:53:21.000000
1010                       DSC_10 24-06-2012 13:53:21.000000
1111                       DSC_11 10-01-2013 13:53:21.000000

Tables Xcatentdesc:
CATENTRY_ID    LANGUAGE_ID SHORTDESCRIPTION
11           -1            DSC_1_DESCRIPTION
33           -1            DSC_3_DESCRIPTION
1010       -1            DSC_10_DESCRIPTION
1111       -1            DSC_11_DESCRIPTION

Query 1: Example: inner join - two table
select o.orders_id, o.partnumber,c.lastupdate from Xorders o inner join Xcatentry c on o.partnumber= c.partnumber;
o/p:
ORDERS_ID         PARTNUMBER   LASTUPDATE
1              DSC_1   8/29/2011 1:53:21.000000 PM
3              DSC_3   12/7/2011 1:53:21.000000 PM
7              DSC_3   12/7/2011 1:53:21.000000 PM

Query 2: Example: inner join - three table
select o.orders_id, o.partnumber,cd.shortdescription from Xorders o inner join Xcatentry c on o.partnumber= c.partnumber inner join Xcatentdesc cd on c.catentry_id
= cd.catentry_id;

output:
ORDERS_ID         PARTNUMBER   SHORTDESCRIPTION
1              DSC_1   DSC_1_DESCRIPTION
3              DSC_3   DSC_3_DESCRIPTION
7              DSC_3   DSC_3_DESCRIPTION

SQL | Analysis and Query to find items that are not part of input list

I will have a few SQL series blogs and as a WCS developer, I feel it is very important to have good SQL skills.

--Exampple . Find all part numbers that are not found in orders table from the list (DSC_1,DSC_3, DSC_10, DSC_11)  in the XORDERITEMS table below.
Result: DSC_10, DSC_11

--1. Table creation:

CREATE TABLE XORDERITEMS (orders_id NUMBER,partnumber varchar2(255), status varchar2(1)) NOLOGGING;

--2. Data insertion into table
insert into /*+ APPEND */  XORDERITEMS values (1,'DSC_1','P');
insert into /*+ APPEND */  XORDERITEMS values (2,'DSC_2','Y');
insert into /*+ APPEND */  XORDERITEMS values (3,'DSC_3','c');
insert into /*+ APPEND */  XORDERITEMS values (4,'DSC_4','c');
insert into /*+ APPEND */  XORDERITEMS values (5,'DSC_5','c');
insert into /*+ APPEND */  XORDERITEMS values (6,'DSC_4','P');
insert into /*+ APPEND */  XORDERITEMS values (7,'DSC_3','y');
insert into /*+ APPEND */  XORDERITEMS values (8,'DSC_7','c');
insert into /*+ APPEND */  XORDERITEMS values (9,'DSC_8','P');
insert into /*+ APPEND */  XORDERITEMS values (10,'DSC_9','P');

--two alternates to fetch the above result.
--Query-1
select pno partnumber from
(SELECT TRIM(SUBSTR ( partnumber , INSTR (partnumber, ',', 1, level ) + 1 , INSTR (partnumber, ',', 1, level+1 ) - INSTR (partnumber, ',', 1, level) -1)) pno
FROM ( SELECT ','||'DSC_1,DSC_3,DSC_10,DSC_11'||',' AS partnumber FROM dual )
CONNECT BY level <= LENGTH(partnumber)-LENGTH(REPLACE(partnumber,',',''))-1 )
where pno not in (select partnumber from XORDERITEMS);

--Alternate Query
select pno partnumber from
(select 'DSC_1' pno from dual
union
select 'DSC_3' pno from dual
union
select 'DSC_10' pno from dual
union
select 'DSC_11' pno from dual)
where pno not in (select partnumber from XORDERITEMS);