I am currently using XML in my database to represent metadata for individual products. I am using the below schema to enter the data. In each sub grouping of fields you will notice the cms_name attribute which I use to match to a relational table row.
This relational table, also listed, allows me to link content groups (of users) to each cms_name, associate rights to the groups. Side note, friendly, render, and restrict are solely for the front-end.
I want to continue using some of what I have because embedded in the XML data is the rendering and data restrictions to be echoed out via XSL; however, promoting (via persisted columns) and updating the XML seems to be a resource killer.
I have no real understanding of database security. I understand NT/2008/(whatever flavor of the month) security including tree, domain, and local security (groups and users) but I have no idea how to translate that to database security.
I envision a read-only view and an updatable view for each protected node, and each of the nodes parsed out into columns in the current container table which contains the XML, a primary key, SKU, and dates (create, modify, etc). Missing from the equation is how to give rights to users over the content.
- I see roles and schemas, how do they relate to groups?
- Is what I am trying to do even possible using the built-in security, views, and updatable views?
- The potential for distinct rights on a product "document" could get large as more users need more rights, is this a worthwhile endeavor to move away from XML?
My end result is that I would like something could scale. Not millions of users, but at least a hundred, and XML seems to be a resource hog. I must be able to programmatically create the users and groups to use within the application.
Thanks
XML Schema
<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:simpleType name="search_minage">
<xsd:restriction base="xsd:positiveInteger">
<xsd:minInclusive value="1" />
<xsd:maxInclusive value="99" />
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="search_maxage">
<xsd:restriction base="xsd:positiveInteger">
<xsd:maxInclusive value="99" />
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="decMONEY">
<xsd:restriction base="xsd:decimal">
<xsd:fractionDigits value="2"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="intBOOL">
<xsd:restriction base="xsd:nonNegativeInteger">
<xsd:minInclusive value="0" />
<xsd:maxInclusive value="1" />
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="option_time">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="mon"/>
<xsd:enumeration value="yr"/>
<xsd:enumeration value="months"/>
<xsd:enumeration value="years"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="option_dimension">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="in"/>
<xsd:enumeration value="ft"/>
<xsd:enumeration value="inches"/>
<xsd:enumeration value="feet"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="option_weight">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="oz"/>
<xsd:enumeration value="lb"/>
<xsd:enumeration value="ounces"/>
<xsd:enumeration value="pounds"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:complexType name="price_cost">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Cost"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="decMONEY"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="price_sale">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Sale Price"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="decMONEY"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="price_openbox">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Open Box Price"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="decMONEY"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="price_distressed">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Distressed Price"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="decMONEY"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="openbox">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Open Box Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="distressed">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Distressed Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="consigned">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Marketplace Consigned Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="listed">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Marketplace Listed Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="external">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Amazon Warehoused Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="dropship">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Available Drop Ship Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="preorder">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Customer Pre-Order Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="sold">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Sold Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="sub_grp_normal_counts">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Onhand Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_normal_counts"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="sub_grp_extend_counts">
<xsd:sequence>
<xsd:element name="openbox" type="openbox"/>
<xsd:element name="distressed" type="distressed"/>
<xsd:element name="consigned" type="consigned"/>
<xsd:element name="listed" type="listed"/>
<xsd:element name="amazonhosted" type="external"/>
<xsd:element name="dropship" type="dropship"/>
<xsd:element name="preorder" type="preorder"/>
<xsd:element name="sold" type="sold"/>
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_extend_counts"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="sub_grp_normal_pricing">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Price"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="decMONEY"/>
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_normal_price"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="sub_grp_extend_pricing">
<xsd:sequence>
<xsd:element name="cost" type="price_cost" />
<xsd:element name="sale" type="price_sale" />
<xsd:element name="openbox" type="price_openbox" />
<xsd:element name="distressed" type="price_distressed" />
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_extend_price"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="sub_grp_sale_date">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="On Sale Exp. Date"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="date"/>
<xsd:element name="value" type="xsd:date" default="1999-01-01"/>
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_sale_date"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="grp_quantities">
<xsd:sequence>
<xsd:element name="onhand" type="sub_grp_normal_counts"/>
<xsd:element name="extend" type="sub_grp_extend_counts"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="grp_pricing">
<xsd:sequence>
<xsd:element name="normal" type="sub_grp_normal_pricing"/>
<xsd:element name="extend" type="sub_grp_extend_pricing"/>
<xsd:element name="saledateexp" type="sub_grp_sale_date"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="search_shops">
<xsd:sequence>
<xsd:element name="shop" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:positiveInteger"/>
<xsd:attribute name="short" type="xsd:string"/>
<xsd:attribute name="display" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="search_genres">
<xsd:sequence>
<xsd:element name="genre" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:positiveInteger"/>
<xsd:attribute name="short" type="xsd:string"/>
<xsd:attribute name="display" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="sub_grp_shops">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Shops"/>
<xsd:element name="render" type="xsd:string" fixed="select"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="values" type="search_shops" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="sub_grp_genres">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Genres"/>
<xsd:element name="render" type="xsd:string" fixed="select"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="values" type="search_genres" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="search_description">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Description"/>
<xsd:element name="render" type="xsd:string" fixed="large_text"/>
<xsd:element name="restrict" type="xsd:string" fixed="string"/>
<xsd:element name="value" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="dim_weight">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Weight"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:positiveInteger" default="6"/>
<xsd:element name="units" type="option_weight" default="oz"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="dim_length">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Length"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_float"/>
<xsd:element name="value" type="decMONEY" default="1"/>
<xsd:element name="units" type="option_dimension" default="in"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="dim_width">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Width"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_float"/>
<xsd:element name="value" type="decMONEY" default="1"/>
<xsd:element name="units" type="option_dimension" default="in"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="dim_height">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Height"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_float"/>
<xsd:element name="value" type="decMONEY" default="1"/>
<xsd:element name="units" type="option_dimension" default="in"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="safe_minage">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Minimum Age"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="search_minage" default="10" />
<xsd:element name="units" type="option_time" default="years"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="safe_maxage">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Maximum Age"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="search_maxage" default="99" />
<xsd:element name="units" type="option_time" default="years"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="safe_choking">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Choking Hazard"/>
<xsd:element name="render" type="xsd:string" fixed="bool"/>
<xsd:element name="restrict" type="xsd:string" fixed="bool"/>
<xsd:element name="value" type="intBOOL" default="0" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="safe_smallparts">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Small Parts"/>
<xsd:element name="render" type="xsd:string" fixed="bool"/>
<xsd:element name="restrict" type="xsd:string" fixed="bool"/>
<xsd:element name="value" type="intBOOL" default="0" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="attr_fragile">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Fragile Item"/>
<xsd:element name="render" type="xsd:string" fixed="bool"/>
<xsd:element name="restrict" type="xsd:string" fixed="bool"/>
<xsd:element name="value" type="intBOOL" default="0" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="attr_adult">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Adult Themed"/>
<xsd:element name="render" type="xsd:string" fixed="bool"/>
<xsd:element name="restrict" type="xsd:string" fixed="bool"/>
<xsd:element name="value" type="intBOOL" default="0" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="attr_shipsbyself">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Ships In Own Box"/>
<xsd:element name="render" type="xsd:string" fixed="bool"/>
<xsd:element name="restrict" type="xsd:string" fixed="bool"/>
<xsd:element name="value" type="intBOOL" default="0" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="attr_available">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Available For Sale?"/>
<xsd:element name="render" type="xsd:string" fixed="bool"/>
<xsd:element name="restrict" type="xsd:string" fixed="bool"/>
<xsd:element name="value" type="intBOOL" default="0"/>
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_available"/>
</xsd:complexType>
<xsd:complexType name="name_brand">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Brand Name"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="string"/>
<xsd:element name="value" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="name_subbrand">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Sub-Brand Name"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="string"/>
<xsd:element name="value" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="name_product">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Product Name"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="string"/>
<xsd:element name="value" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="name_model">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Model/Part Number"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="string"/>
<xsd:element name="value" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="grp_search">
<xsd:sequence>
<xsd:element name="shops" type="sub_grp_shops"/>
<xsd:element name="genres" type="sub_grp_genres" />
<xsd:element name="description" type="search_description" />
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_search"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="grp_safety">
<xsd:sequence>
<xsd:element name="minage" type="safe_minage" />
<xsd:element name="maxage" type="safe_maxage" />
<xsd:element name="choking" type="safe_choking" />
<xsd:element name="smallparts" type="safe_smallparts" />
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_safety"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="grp_attributes">
<xsd:sequence>
<xsd:element name="fragile" type="attr_fragile" />
<xsd:element name="adultproduct" type="attr_adult" />
<xsd:element name="shipbyself" type="attr_shipsbyself" />
<xsd:element name="available" type="attr_available" />
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_attributes"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="grp_dimensions">
<xsd:sequence>
<xsd:element name="weight" type="dim_weight"/>
<xsd:element name="length" type="dim_length" />
<xsd:element name="width" type="dim_width" />
<xsd:element name="height" type="dim_height" />
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_dimensions"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="grp_names">
<xsd:sequence>
<xsd:element name="brand" type="name_brand" />
<xsd:element name="sub_brand" type="name_subbrand" />
<xsd:element name="product" type="name_product" />
<xsd:element name="model" type="name_model" />
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_names"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="mDetail">
<xsd:sequence>
<xsd:element name="name" type="grp_names"/>
<xsd:element name="safety" type="grp_safety"/>
<xsd:element name="attributes" type="grp_attributes"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="lDetail">
<xsd:sequence>
<xsd:element name="dimensions" type="grp_dimensions"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="sDetail">
<xsd:sequence>
<xsd:element name="search" type="grp_search"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="fDetail">
<xsd:sequence>
<xsd:element name="quantities" type="grp_quantities"/>
<xsd:element name="pricing" type="grp_pricing"/>
</xsd:sequence>
</xsd:complexType>
<xsd:element name="metadata">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="information" type="mDetail" />
<xsd:element name="measurements" type="lDetail" />
<xsd:element name="categorization" type="sDetail" />
<xsd:element name="financial" type="fDetail" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Tables:
CREATE TABLE [dbo].[tbl_cms_securables]
(
[refid] [int] NOT NULL IDENTITY(1,1),
[refname] [varchar](100) NOT NULL,
[refdescription] [varchar](MAX) NULL,
CONSTRAINT pk_cms_secdocs_refid PRIMARY KEY (refid)
)
CREATE TABLE [dbo].[tbl_cms_secureusers]
(
[refid] [bigint] NOT NULL IDENTITY(1,1),
[secid] [int] NOT NULL,
[grpid] [int] NOT NULL,
[read] [bit] NOT NULL DEFAULT 0,
[modify] [bit] NOT NULL DEFAULT 0,
[delete] [bit] NOT NULL DEFAULT 0,
CONSTRAINT pk_cms_secuser_refid PRIMARY KEY (refid),
CONSTRAINT fk_cms_secdocs_secid FOREIGN KEY (secid) REFERENCES dbo.tbl_cms_securables(refid),
CONSTRAINT fk_cms_secgrps_grpid FOREIGN KEY (grpid) REFERENCES dbo.tbl_cms_groups(refid) ON DELETE CASCADE
)
