ALTER RESOURCE LABEL¶

说明¶

修改资源标签

语法¶

alter_resource_label::=
    ALTER RESOURCE LABEL label_name { ADD | REMOVE } { COLUMN ( column_path [ , ... ] ) } [ , ... ]

参数¶

label_name

资源标签名称，创建时要求不能与已有标签重名。

column_path

列名的路径（例如：sysdba.tab.col1）。

示例¶

示例1: 向资源标签中添加列

--  清理环境
DROP TABLE tab_mask1 CASCADE;

CREATE TABLE tab_mask1(col1 TEXT, col2 TEXT);

CONNECT syssecure/szoscar55;

--  清理环境
DROP RESOURCE LABEL label_mask1;

-- 创建资源标签
CREATE RESOURCE LABEL label_mask1 ADD COLUMN(sysdba.tab_mask1.col1);


SELECT MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME FROM SYS_MASK_LABEL, SYS_CLASS WHERE MASKLABELNAME = 'LABEL_MASK1' AND OID = MASKRELATIONID ORDER BY MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME;
MASKLABELNAME(name)      |MASKNAMESPACEID(OID)      |RELNAME(name)      |MASKCOLUMNNAME(name)      |
------------------------------------------------------------------------------------------------
LABEL_MASK1              |1                         |TAB_MASK1          |COL1                      |
总数目：1

-- 向资源标签中添加列
ALTER RESOURCE LABEL label_mask1 ADD COLUMN(sysdba.tab_mask1.col2);


SELECT MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME FROM SYS_MASK_LABEL, SYS_CLASS WHERE MASKLABELNAME = 'LABEL_MASK1' AND OID = MASKRELATIONID ORDER BY MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME;
MASKLABELNAME(name)      |MASKNAMESPACEID(OID)      |RELNAME(name)      |MASKCOLUMNNAME(name)      |
------------------------------------------------------------------------------------------------
LABEL_MASK1              |1                         |TAB_MASK1          |COL1                      |
------------------------------------------------------------------------------------------------
LABEL_MASK1              |1                         |TAB_MASK1          |COL2                      |
总数目：2

-- 删除资源标签
DROP RESOURCE LABEL label_mask1;

CONNECT sysdba/szoscar55;

DROP TABLE tab_mask1 CASCADE;

示例2: 从资源标签中删除列

--  清理环境
DROP TABLE tab_mask2 CASCADE;

CREATE TABLE tab_mask2(col3 TEXT, col4 TEXT);

CONNECT syssecure/szoscar55;

--  清理环境
DROP RESOURCE LABEL label_mask2;

-- 创建资源标签
CREATE RESOURCE LABEL label_mask2 ADD COLUMN(sysdba.tab_mask2.col3, sysdba.tab_mask2.col4);


SELECT MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME FROM SYS_MASK_LABEL, SYS_CLASS WHERE MASKLABELNAME = 'LABEL_MASK2' AND OID = MASKRELATIONID ORDER BY MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME;
MASKLABELNAME(name)      |MASKNAMESPACEID(OID)      |RELNAME(name)      |MASKCOLUMNNAME(name)      |
------------------------------------------------------------------------------------------------
LABEL_MASK2              |1                         |TAB_MASK2          |COL3                      |
------------------------------------------------------------------------------------------------
LABEL_MASK2              |1                         |TAB_MASK2          |COL4                      |
总数目：2

-- 从资源标签中删除列
ALTER RESOURCE LABEL label_mask2 REMOVE COLUMN(sysdba.tab_mask2.col4);


SELECT MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME FROM SYS_MASK_LABEL, SYS_CLASS WHERE MASKLABELNAME = 'LABEL_MASK2' AND OID = MASKRELATIONID ORDER BY MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME;
MASKLABELNAME(name)      |MASKNAMESPACEID(OID)      |RELNAME(name)      |MASKCOLUMNNAME(name)      |
------------------------------------------------------------------------------------------------
LABEL_MASK2              |1                         |TAB_MASK2          |COL3                      |
总数目：1

-- 删除资源标签
DROP RESOURCE LABEL label_mask2;

CONNECT sysdba/szoscar55;

DROP TABLE tab_mask2 CASCADE;

示例3: 向资源标签中添加多列

--  清理环境
DROP TABLE tab_mask3 CASCADE;
DROP TABLE tab_mask4 CASCADE;
DROP TABLE tab_mask5 CASCADE;

CREATE TABLE tab_mask3(col5 TEXT, col6 TEXT);
CREATE TABLE tab_mask4(col7 TEXT, col8 TEXT);
CREATE TABLE tab_mask5(col9 TEXT, col10 TEXT);

CONNECT syssecure/szoscar55;

--  清理环境
DROP RESOURCE LABEL label_mask3;

-- 创建资源标签
CREATE RESOURCE LABEL label_mask3 ADD COLUMN(sysdba.tab_mask3.col5);


SELECT MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME FROM SYS_MASK_LABEL, SYS_CLASS WHERE MASKLABELNAME = 'LABEL_MASK3' AND OID = MASKRELATIONID ORDER BY MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME;
MASKLABELNAME(name)      |MASKNAMESPACEID(OID)      |RELNAME(name)      |MASKCOLUMNNAME(name)      |
------------------------------------------------------------------------------------------------
LABEL_MASK3              |1                         |TAB_MASK3          |COL5                      |
总数目：1

-- 向资源标签中添加多列
ALTER RESOURCE LABEL label_mask3 ADD COLUMN(sysdba.tab_mask4.col7, sysdba.tab_mask4.col8);


SELECT MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME FROM SYS_MASK_LABEL, SYS_CLASS WHERE MASKLABELNAME = 'LABEL_MASK3' AND OID = MASKRELATIONID ORDER BY MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME;
MASKLABELNAME(name)      |MASKNAMESPACEID(OID)      |RELNAME(name)      |MASKCOLUMNNAME(name)      |
------------------------------------------------------------------------------------------------
LABEL_MASK3              |1                         |TAB_MASK3          |COL5                      |
------------------------------------------------------------------------------------------------
LABEL_MASK3              |1                         |TAB_MASK4          |COL7                      |
------------------------------------------------------------------------------------------------
LABEL_MASK3              |1                         |TAB_MASK4          |COL8                      |
总数目：3

-- 向资源标签中添加多列
ALTER RESOURCE LABEL label_mask3 ADD COLUMN(sysdba.tab_mask5.col9), COLUMN(sysdba.tab_mask5.col10);


SELECT MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME FROM SYS_MASK_LABEL, SYS_CLASS WHERE MASKLABELNAME = 'LABEL_MASK3' AND OID = MASKRELATIONID ORDER BY MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME;
MASKLABELNAME(name)      |MASKNAMESPACEID(OID)      |RELNAME(name)      |MASKCOLUMNNAME(name)      |
------------------------------------------------------------------------------------------------
LABEL_MASK3              |1                         |TAB_MASK3          |COL5                      |
------------------------------------------------------------------------------------------------
LABEL_MASK3              |1                         |TAB_MASK4          |COL7                      |
------------------------------------------------------------------------------------------------
LABEL_MASK3              |1                         |TAB_MASK4          |COL8                      |
------------------------------------------------------------------------------------------------
LABEL_MASK3              |1                         |TAB_MASK5          |COL10                     |
------------------------------------------------------------------------------------------------
LABEL_MASK3              |1                         |TAB_MASK5          |COL9                      |
总数目：5

-- 删除资源标签
DROP RESOURCE LABEL label_mask3;

CONNECT sysdba/szoscar55;

DROP TABLE tab_mask3 CASCADE;
DROP TABLE tab_mask4 CASCADE;
DROP TABLE tab_mask5 CASCADE;

示例4: 从资源标签中删除多列

--  清理环境
DROP TABLE tab_mask6 CASCADE;
DROP TABLE tab_mask7 CASCADE;
DROP TABLE tab_mask8 CASCADE;

CREATE TABLE tab_mask6(col11 TEXT, col12 TEXT);
CREATE TABLE tab_mask7(col13 TEXT, col14 TEXT);
CREATE TABLE tab_mask8(col15 TEXT, col16 TEXT);

CONNECT syssecure/szoscar55;

--  清理环境
DROP RESOURCE LABEL label_mask4;

-- 创建资源标签
CREATE RESOURCE LABEL label_mask4 ADD COLUMN(sysdba.tab_mask6.col11, sysdba.tab_mask7.col13, sysdba.tab_mask7.col14, sysdba.tab_mask8.col15, sysdba.tab_mask8.col16);


SELECT MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME FROM SYS_MASK_LABEL, SYS_CLASS WHERE MASKLABELNAME = 'LABEL_MASK4' AND OID = MASKRELATIONID ORDER BY MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME;
MASKLABELNAME(name)      |MASKNAMESPACEID(OID)      |RELNAME(name)      |MASKCOLUMNNAME(name)      |
------------------------------------------------------------------------------------------------
LABEL_MASK4              |1                         |TAB_MASK6          |COL11                     |
------------------------------------------------------------------------------------------------
LABEL_MASK4              |1                         |TAB_MASK7          |COL13                     |
------------------------------------------------------------------------------------------------
LABEL_MASK4              |1                         |TAB_MASK7          |COL14                     |
------------------------------------------------------------------------------------------------
LABEL_MASK4              |1                         |TAB_MASK8          |COL15                     |
------------------------------------------------------------------------------------------------
LABEL_MASK4              |1                         |TAB_MASK8          |COL16                     |
总数目：5

-- 从资源标签中删除多列
ALTER RESOURCE LABEL label_mask4 REMOVE COLUMN(sysdba.tab_mask7.col13, sysdba.tab_mask7.col14);


SELECT MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME FROM SYS_MASK_LABEL, SYS_CLASS WHERE MASKLABELNAME = 'LABEL_MASK4' AND OID = MASKRELATIONID ORDER BY MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME;
MASKLABELNAME(name)      |MASKNAMESPACEID(OID)      |RELNAME(name)      |MASKCOLUMNNAME(name)      |
------------------------------------------------------------------------------------------------
LABEL_MASK4              |1                         |TAB_MASK6          |COL11                     |
------------------------------------------------------------------------------------------------
LABEL_MASK4              |1                         |TAB_MASK8          |COL15                     |
------------------------------------------------------------------------------------------------
LABEL_MASK4              |1                         |TAB_MASK8          |COL16                     |
总数目：3

-- 从资源标签中删除多列
ALTER RESOURCE LABEL label_mask4 REMOVE COLUMN(sysdba.tab_mask8.col15), COLUMN(sysdba.tab_mask8.col16);


SELECT MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME FROM SYS_MASK_LABEL, SYS_CLASS WHERE MASKLABELNAME = 'LABEL_MASK4' AND OID = MASKRELATIONID ORDER BY MASKLABELNAME, MASKNAMESPACEID, RELNAME, MASKCOLUMNNAME;
MASKLABELNAME(name)      |MASKNAMESPACEID(OID)      |RELNAME(name)      |MASKCOLUMNNAME(name)      |
------------------------------------------------------------------------------------------------
LABEL_MASK4              |1                         |TAB_MASK6          |COL11                     |
总数目：1

-- 删除资源标签
DROP RESOURCE LABEL label_mask4;

CONNECT sysdba/szoscar55;

DROP TABLE tab_mask6 CASCADE;
DROP TABLE tab_mask7 CASCADE;
DROP TABLE tab_mask8 CASCADE;