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
)
Best Answer
What you are trying to do is basic row level filtering. Normally this wouldn't be done using SQL Users and Roles but instead with application level users within your application. Basically you have your users table (and/or a groups table) which are worked with within the application.
Then you create a table between the users and the rows which you need to provide the permissions and do joins between then so that users can only view rows which they should.
There are several books and articles on the subject.