我目前在我的数据库中使用 XML 来表示单个产品的元数据。我正在使用以下模式输入数据。在字段的每个子分组中，您会注意到我用来匹配关系表行的 cms_name 属性。
此关系表也已列出，允许我将（用户的）内容组链接到每个 cms_name，将权限关联到组。旁注、friendly、render 和 restrict 仅适用于前端。
我想继续使用我拥有的一些东西，因为嵌入在 XML 数据中的是要通过 XSL 回显的呈现和数据限制；然而，提升（通过持久化列）和更新 XML 似乎是一种资源杀手。
我对数据库安全性没有真正的了解。我了解 NT/2008/（无论哪个月份）安全性，包括树、域和本地安全性（组和用户），但我不知道如何将其转化为数据库安全性。
我为每个受保护节点设想了一个只读视图和一个可更新视图，并且每个节点都解析为当前容器表中的列，其中包含 XML、主键、SKU 和日期（创建、修改等） . 等式中缺少的是如何授予用户对内容的权利。
- 我看到了角色和模式，它们与组有什么关系？
- 使用内置的安全性、视图和可更新视图，我正在尝试做的事情是否有可能实现？
- 随着更多的用户需要更多的权利，产品“文档”上的不同权利的潜力可能会变大，这是摆脱 XML 的值得努力吗？
我的最终结果是我希望有些东西可以扩展。不是数百万用户，但至少有一百个，而且 XML 似乎是一个资源消耗者。我必须能够以编程方式创建要在应用程序中使用的用户和组。
谢谢
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>
表：
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
)
您要做的是基本的行级过滤。通常这不会使用 SQL 用户和角色来完成，而是使用应用程序中的应用程序级用户来完成。基本上，您拥有在应用程序中使用的用户表（和/或组表）。
然后在用户和需要提供权限的行之间创建一个表，然后在它们之间进行连接，以便用户只能查看他们应该查看的行。
有几本关于这个主题的书籍和文章。