Atualmente, estou usando XML em meu banco de dados para representar metadados para produtos individuais. Estou usando o esquema abaixo para inserir os dados. Em cada subgrupo de campos, você notará o atributo cms_name que eu uso para corresponder a uma linha da tabela relacional.
Esta tabela relacional, também listada, permite vincular grupos de conteúdo (de usuários) a cada cms_name, associar direitos aos grupos. Observação lateral, amigável, renderização e restrição são apenas para o front-end.
Quero continuar usando um pouco do que tenho porque embutido nos dados XML está a renderização e as restrições de dados a serem ecoadas via XSL; no entanto, promover (por meio de colunas persistentes) e atualizar o XML parece ser um matador de recursos.
Não tenho nenhum entendimento real de segurança de banco de dados. Eu entendo a segurança do NT/2008/(qualquer tipo do mês), incluindo árvore, domínio e segurança local (grupos e usuários), mas não tenho ideia de como traduzir isso para a segurança do banco de dados.
Prevejo uma exibição somente leitura e uma exibição atualizável para cada nó protegido e cada um dos nós analisados em colunas na tabela de contêiner atual que contém o XML, uma chave primária, SKU e datas (criar, modificar etc.) . O que falta na equação é como dar direitos aos usuários sobre o conteúdo.
- Eu vejo papéis e esquemas, como eles se relacionam com grupos?
- O que estou tentando fazer é possível usando segurança integrada, exibições e exibições atualizáveis?
- O potencial para direitos distintos em um "documento" de produto pode aumentar à medida que mais usuários precisam de mais direitos. Vale a pena tentar abandonar o XML?
Meu resultado final é que eu gostaria que algo pudesse escalar. Não milhões de usuários, mas pelo menos uma centena, e o XML parece ser um devorador de recursos. Devo ser capaz de criar programaticamente os usuários e grupos para usar no aplicativo.
Obrigado
Esquema XML
<?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>
Tabelas:
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
)
O que você está tentando fazer é a filtragem básica no nível da linha. Normalmente, isso não seria feito usando SQL Users and Roles, mas sim com usuários de nível de aplicativo em seu aplicativo. Basicamente você tem sua tabela de usuários (e/ou uma tabela de grupos) que são trabalhadas dentro do aplicativo.
Em seguida, você cria uma tabela entre os usuários e as linhas que precisa para fornecer as permissões e faz junções entre elas, para que os usuários possam visualizar apenas as linhas que deveriam.
Existem vários livros e artigos sobre o assunto.