Loading Access Control Policy using SQL

Here is a technique which is an alternate to WCS acpload script. The technique uses a set of SQL's to load Access control policy for new Controller commands and Views.


This would be useful for newbies who might think defining xml / acpload is the only option to define access policy for new views/commands, this is much more simpler and faster approach.
and for the purpose of showing you the difference I will document both infocenter approach and direct SQL approach.

Loading a new View Access control Policy


Here is an example from infocenter to create a new custom View 

http://publib.boulder.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.developer.tutorial.doc/tutorial/ttd12.htm


Custom View Policy

View Policy XML to be loaded using acpload:
 
<?xml version="1.0" encoding="ISO-8859-1" standalone="no" ?>
<!DOCTYPE Policies SYSTEM "../dtd/accesscontrolpolicies.dtd">
<Policies>
<Action Name="MyNewView"
   CommandName="MyNewView">
</Action>
<ActionGroup Name="AllSiteUsersViews"
            OwnerID="RootOrganization">
            <ActionGroupAction Name="MyNewView"/>
</ActionGroup>
</Policies>
You will then copy this xml in /xml/policies and run "acpload" as follows


SQL approach to load VIEW policy:

insert into acaction (acaction_id, action) values ((select counter from keys where tablename='acaction'), 'MyNewView');

insert into acactactgp (ACACTGRP_ID,ACACTION_ID) values
((SELECT ACACTGRP_ID FROM ACACTGRP WHERE GROUPNAME = 'AllSiteUsersViews'
and member_id in (select orgentity_id from orgentity where orgentityname='Root Organization')
),
(select acaction_id from acaction where action='MyNewView'));

UPDATE KEYS SET COUNTER = COUNTER+1 WHERE TABLENAME = 'acaction';

Rollback the Access policy:

delete from acactactgp

  where ACACTION_ID in (select acaction_id from acaction where action='MyNewView')

delete from acaction where action ='MyNewView';

Loading a new Command Access Control Policy
 

Here is an example from infocenter to create a new custom controller command


http://publib.boulder.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.developer.tutorial.doc/tutorial/ttd13.htm


Access Policy XML:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE import SYSTEM "../../../schema/xml/wcs.dtd">
<import>
<acaction ACACTION_ID="@Execute" ACTION="Execute"/>
<acrescgry ACRESCGRY_ID="@com.ibm.commerce.sample.commands.MyNewControllerCmd" RESCLASSNAME="com.ibm.commerce.sample.commands.MyNewControllerCmd"/>
<acresact ACRESCGRY_ID="@com.ibm.commerce.sample.commands.MyNewControllerCmd" ACACTION_ID="@Execute"/>
<acresgrp ACRESGRP_ID="@AllSiteUserCmdResourceGroup" MEMBER_ID="-2001" GRPNAME="AllSiteUserCmdResourceGroup"/>
<acresgpres ACRESGRP_ID="@AllSiteUserCmdResourceGroup" ACRESCGRY_ID="@com.ibm.commerce.sample.commands.MyNewControllerCmd"/>

</import>
 

SQL approach to load custom command policy

insert into acrescgry
(ACRESCGRY_ID,RESCLASSNAME)
values
((select counter from keys where tablename='acrescgry'),'com.ibm.commerce.sample.commands.MyNewControllerCmd');

insert into acresact
(ACRESCGRY_ID, ACACTION_ID)
values
((select counter from keys where tablename='acrescgry'),(select ACACTION_ID from acaction where action='Execute'));

insert into acresgpres
(ACRESGRP_ID, ACRESCGRY_ID)
values
((select ACRESGRP_ID from acresgrp where MEMBER_ID in
(select orgentity_id from orgentity where orgentityname='Root Organization') and GRPNAME='AllSiteUserCmdResourceGroup'),
(select counter from keys where tablename='acrescgry'));

UPDATE KEYS SET COUNTER = COUNTER+1 WHERE TABLENAME = 'acrescgry';

Rollback the Access policy:

delete from acresgpres where ACRESCGRY_ID in (select ACRESCGRY_ID from acrescgry where  RESCLASSNAME='com.ibm.commerce.sample.commands.MyNewControllerCmd') 

delete from acresact where ACRESCGRY_ID in (select ACRESCGRY_ID from acrescgry where  RESCLASSNAME='com.ibm.commerce.sample.commands.MyNewControllerCmd';

delete from acrescgry where RESCLASSNAME='com.ibm.commerce.sample.commands.MyNewControllerCmd