Loading Access Control Policy using SQL

Posted by Unknown on

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


6 comments:

  1. Hi, Could you please tell me the sql approach to load ACPolicy for an entity bean.

    ReplyDelete
  2. I am getting an issue while opening acpload.bat file . When I am trying to open it , it is showing me 'parameter is incorrect' . And because of this issue I am not able to execute my acpload.
    Can anyone suggest what should I do in this scenario?

    ReplyDelete
  3. THANKS FOR SHARING SUCH A AMAZING WORK
    GREAT PIECE OF WORK!!!
    access point supplier in dubai

    ReplyDelete
  4. I'm very much inspired when I've visited your blog. Your blog is informative. Hope you will continue with the new article.
    SQL Azure Online Training
    Azure SQL Training
    SQL Azure Training

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete