三权分立在GBase 8s中的实践
- 概述
- 业务场景说明
- 安全等级
- 商品分类
- 区域
- 岗位
- 岗位权限规划
- 业务主管（manager）
- 采购人员（purchase）
- 销售人员（sale）
- 库管人员（store）
- 财务人员（finance）
- 三权分立中的用户规划
- 环境部署
- 创建用户
- 安装GBase 8s(不创建数据库实例)
- 修改配置参数
- 创建数据库实例
- 配置用户环境变量（gbasedbt）
- 三权分立功能验证
- 配置审计掩码（dbsso）
- 配置审计参数（dbaao）
- 查看审计日志（dbaao）
- 创建数据库（gbasedbt）
- 用户授权（gbasedbt/自主访问控制）
- 创建安全标签（dbsso/强制访问控制）
- 为用户授予安全标签（dbsso/强制访问控制）
- 创建表（dbsso）
- 插入数据（manager）
- 插入数据（purchase_nc）
- 插入数据（purchase_ec）
- 插入数据（purchase_sc）
- 查询数据（manager）
- 查询数据（finance）
- 查询数据（store）
- 查询数据（purchase_nc）
- 查询数据（purchase_ec）
- 查询数据（purchase_sc）
- 查询数据（sale_nc）
- 查询数据（sale_ec）
- 查询数据（sale_sc）
- 查询数据（gbasedbt）
- 查询数据（dbsso）
- 查询数据（dbaao）
- 更新数据（sale_nc）
- 更新数据（sale_ec）
- 更新数据（sale_sc）
- 小结
- 附录
- 常用审计事件
- 操作系统用户管理
概述
本文档介绍一个在全国范围内，经营软件产品的贸易公司，通过该公司的整体业务，演示三权分立在GBase 8s中的应用。本演示用例中的权限分配，只是用于演示三权分立的控制效果，与真实场景的权限分配没有关联。
贸易公司主要采购和销售的软件产品分为三类：硬件厂商OEM类，集团采购类，个人终端零售类。
贸易公司的业务遍布全国，在管理上，划分为华北区，华东区，华南区共三个区域。
贸易公司在组织上分为：总部业务主管，总部库管人员，总部财务人员，各区域采购人员，各区域销售人员。
业务场景说明
安全等级
贸易公司将数据分为三个等级：
- 秘密
- 敏感
- 公开
商品分类
贸易公司的商品分为OEM软件，集团采购类软件和零售类软件共三类：
- OEM
- 集团采购（Wholesale）
- 零售（Retail）
区域
贸易公司有一个总部和三个分公司：
- 总部（HEAD）
- 华北（NC）
- 华东（EC）
- 华南（SC）
岗位
贸易公司共设置5个岗位：
- 主管（manager）。可以操作所有区域，全部安全等级，全部商品分类的业务数据。
- 采购（purchase）。可以操作本区域，全部安全等级，属于集团采购和零售类软件的业务数据，不可以操作OEM类软件的业务数据。
- 销售（sale）。可以操作本区域，集团采购和零售类软件的敏感数据或公开数据，不可以操作OEM类业务数据，不可以操作秘密等级的业务数据。可以修改、查看除
采购价格以外的字段。 - 财务（finance）。可以查看全部区域的，除OEM商品分类以外的敏感业务数据和公开业务数据，不能修改业务数据。
- 库管（store）。可以查看全部区域，除OEM商品分类以外的公开数据（不包含价格数据），不能修改任何业务数据。
岗位权限规划
业务主管（manager）
- 可以读、写全部行。
- 可以读、写全部列。
采购人员（purchase）
- 只能读、写本区域或未标记区域的商品记录。
- 可以读、写全部安全等级的商品记录。
- 只能读、写集团采购、零售的商品记录，不可以读写OEM商品记录。
销售人员（sale）
- 只能读、写本区域或未标记区域的商品记录。
- 只能读、写集团采购、零售商品记录，不可以读写OEM商品记录。
- 只能读、写敏感类和未标记安全等级的商品记录，不可以读写秘密类商品记录。
- 可以读、写商品售价，不可以读写商品进价
库管人员（store）
- 可以读取全部区域的商品记录。
- 可以读取零售的商品记录，不可以读取OEM，集团采购类商品记录。
- 只能读、写未标记安全等级的商品记录，不可以读写敏感类和秘密类商品记录。
- 不能读商品进价和商品售价。
- 不能修改任何数据。
财务人员（finance）
- 可以读取全部区域的商品记录。
- 可以读取全部安全等级的商品记录。
- 可以读取集团采购和零售的数据，不可以读取OEM类数据。
- 不能修改任何数据。
三权分立中的用户规划
人员 | 数据库用户 | 说明 |
业务主管(总部) | manager | 对全部商品数据可进行增删改查 |
财务人员(总部) | finance | 对非OEM类商品数据可进行查询 |
库管人员(总部) | store | 对未安全标记类、零售类商品除价格信息的查询 |
采购人员(华北) | purchase_nc | 对华北区域非OEM类商品的增删改查 |
采购人员(华东) | purchase_ec | 对华东区域非OEM类商品的增删改查 |
采购人员(华南) | purchase_sc | 对华南区域非OEM类商品的增删改查 |
销售人员(华北) | sale_nc | 对华北区域非OEM类，非秘密类商品除进价信息的增删改查 |
销售人员(华东) | sale_ec | 对华东区域非OEM类，非秘密类商品除进价信息的增删改查 |
销售人员(华南) | sale_sc | 对华南区域非OEM类，非秘密类商品除进价信息的增删改查 |
数据库管理员 | gbasedbt | 可进行数据库运维，授权等操作，不可以访问商品数据 |
数据库安全员 | dbsso | 可进行安全审计掩码设置，不可访问商品数据，不可查看审计日志 |
数据库审计员 | dbaao | 可查看审计日志，不可进行安全审计掩码设置，不可访问商品数据 |
环境部署
创建用户
在root用户下，创建演示用的多个用户。
useradd gbasedbt
useradd dbsso
useradd dbaao
useradd manager
useradd finance
useradd store
useradd purchase_nc
useradd purchase_ec
useradd purchase_sc
useradd sale_nc
useradd sale_ec
useradd sale_sc创建用户后，使用passwd为每个用户设置密码。
安装GBase 8s(不创建数据库实例)
- 在安装时，需要选择自定义安装
2- Custom installation。 - 在安装时，需要启动角色分离
1- Enable role separation。 - 安全相关任务组Group for security-related tasks: (DEFAULT: gbasedbt):
dbsso。 - 审计管理员组Group for audit-administration tasks: (DEFAULT: gbasedbt):
dbaao。 - 数据用户组Group for database users (leave blank to allow all users): (DEFAULT: ):不需要设置，直接回车即可。
进入安装目录，输入./ids_install，启动安装程序。
[root@vmsvr2 ~]# cd install/
[root@vmsvr2 install]# ls
doc ids_install ids.properties onsecurity
[root@vmsvr2 install]# ./ids_install
Preparing to install...
Extracting the JRE from the installer archive...
Unpacking the JRE...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...
Launching installer...
===============================================================================
GBase Software Bundle (created with InstallAnywhere)
-------------------------------------------------------------------------------
Preparing CONSOLE Mode Installation...
===============================================================================
Getting Started
---------------
This application will guide you through the installation of GBase Software
Bundle.
Copyright General Data Corporation 2014, 2021. All rights reserved.
1. Release Notes
The Release Notes can be found in
/root/install/doc/ids_unix_relnotes_12.10.html.
2. Launch Information Center
Access the GBase Information Center at http://www.gbase.cn.
To Begin Installation, respond to each prompt to proceed to the next step in
the installation.
If you want to change something on a previous step, type 'back'.
You may cancel this installation at any time by typing 'quit'.
PRESS <ENTER> TO CONTINUE:输入回车。
===============================================================================
License Agreement
-----------------
Installation and Use of GBase Software Bundle Requires Acceptance of the
Following License Agreement:
......
which the corresponding fee has been paid.
2. RESTRICTIONS. Software is confidential and copyrighted. Title to Software
PRESS <ENTER> TO CONTINUE:输入回车。
and all associated intellectual property rights is retained by GeneralData
and/or its licensors. Except as specifically authorized in any Supplemental
......
however caused regardless of the theory of liability, arising out of or
related to the use of or inability to use software, even if GeneralData has
PRESS <ENTER> TO CONTINUE:输入回车。
been advised of the possibility of such damages. In no event will
GeneralData's libility to you, whether incontract, tort(including negligence),
......
7. CHINESE GOVERNMENT RESTRICTED. If Software is being acquired by or on behalf
PRESS <ENTER> TO CONTINUE:输入回车。
of the Chinese Government, then the Government's rights in Software and
......
depresentative of each party. When the translation document has the different
PRESS <ENTER> TO CONTINUE:输入回车。
meaning or has the conflicting views with Chinese original text conflict,
should take the laws and regulations promulgation unit as well as the
GeneralData issue Chinese original text as the standard.
All trademarks and registered trademarks mentioned herein are the property of
their respective owners.
DO YOU ACCEPT THE TERMS OF THIS LICENSE AGREEMENT? (Y/N): y输入y后回车。
===============================================================================
Installation Location
---------------------
Choose location for software installation.
Default Install Folder: /opt/GBASE/gbase
ENTER AN ABSOLUTE PATH, OR PRESS <ENTER> TO ACCEPT THE DEFAULT
: /opt/gbase输入/opt/gbase后回车。
INSTALL FOLDER IS: /opt/gbase
IS THIS CORRECT? (Y/N): y输入y后回车。
===============================================================================
Installation or Distribution
----------------------------
Select the installation type.
Typical: Install the database server with all features and a database server
that
is configured with default values. Includes:
** Client Software Development Kit (CSDK)
** Java Database Connectivity (JDBC)
Minimum disk space required: 700-800MB
Custom: Install the database server with specific features and software that
you need.
Optionally install a configured database server instance.
Minimum disk space required: 75 MB (without a server instance)
->1- Typical installation
2- Custom installation
3- Extract the product files (-DLEGACY option)
ENTER THE NUMBER FOR YOUR CHOICE, OR PRESS <ENTER> TO ACCEPT THE DEFAULT:: 2输入2后回车。
===============================================================================
"Choose Install Features" Help
------------------------------
On the next screen, type the number of a feature to change whether it is
selected or deselected. You can provide a list of numbers, separated by commas,
with no leading or trailing spaces.
For example, assume that the screen contains this list:
1- [X] ABC
2- [ ] XYZ
Please choose the Features to be installed by this installer.: ___
If you want to install both ABC and XYZ, type: 2
If you want to install only XYZ, type: 1,2
PRESS <ENTER> TO CONTINUE:回车。
===============================================================================
Choose Install Set
------------------
===============================================================================
Choose Product Features
-----------------------
ENTER A COMMA_SEPARATED LIST OF NUMBERS REPRESENTING THE FEATURES YOU WOULD
LIKE TO SELECT, OR DESELECT. TO VIEW A FEATURE'S DESCRIPTION, ENTER
'?<NUMBER>'. PRESS <RETURN> WHEN YOU ARE DONE:
1- [X] GBase database server
2- |-[X] Base Server
3- |-[X] Extensions and tools
4- |-[X] J/Foundation
5- |-[X] Database extensions
6- |-[X] Conversion and reversion support
7- |-[X] XML publishing
8- |-[X] Demonstration database scripts
9- |-[X] Enterprise Replication
10- |-[X] Data loading utilities
11- |-[X] onunload and onload utilities
12- |-[X] dbload utility
13- |-[X] Backup and Restore
14- |-[X] archecker utility
15- |-[X] ON-Bar utility
16- |-[X] Interface to Tivoli Storage Manager
17- |-[X] Administrative utilities
18- |-[X] Performance monitoring utilities
19- |-[X] Miscellaneous monitoring utilities
20- |-[X] Auditing utilities
21- |-[X] Database import and export utilities
22- |-[X] JSON Client Support
23- [X] Global Language Support (GLS)
24- |-[X] Chinese
Please choose the Features to be installed by this installer.:
===============================================================================
Feature review
--------------
Would you like to review the features that you have selected, or change which
features you have selected?
->1- Continue
2- Return to feature tree
ENTER THE NUMBER OF THE DESIRED CHOICE, OR PRESS <ENTER> TO ACCEPT THE
DEFAULT: 1输入1后回车。
===============================================================================
Get Role Separation choice
--------------------------
Enable role separation for auditing procedures.
If you enable role separation, you can assign existing groups of users to
specific roles.
If you do not enable role separation, the database server administrator
performs all administration tasks.
1- Enable role separation
->2- Do not enable role separation
ENTER THE NUMBER FOR YOUR CHOICE, OR PRESS <ENTER> TO ACCEPT THE DEFAULT:: 1输入1后回车。
===============================================================================
Role Separation groups selection
--------------------------------
Assign a group of users to each of the following roles by specifying group
identifiers (group IDs). The group IDs specified must already exist on your
system.
Group for security-related tasks: (DEFAULT: gbasedbt): dbsso输入dbsso后回车。
Group for audit-administration tasks: (DEFAULT: gbasedbt): dbaao输入dbaao后回车。
Group for database users (leave blank to allow all users): (DEFAULT: ):回车。
===============================================================================
Server Instance
---------------
Type 'back' to go to the previous step or 'quit' to cancel the installation.
Create a database server instance?
1- Yes - create a server instance
->2- No - do not create a server instance
ENTER THE NUMBER FOR YOUR CHOICE, OR PRESS <ENTER> TO ACCEPT THE DEFAULT:: 2输入2后回车。
===============================================================================
Installation Summary
--------------------
Please review the following before continuing:
Product Name:
GBase Software Bundle
Install Folder:
/opt/gbase
Product Features:
GBase database server,
Base Server,
Extensions and tools,
......
Global Language Support (GLS),
Chinese
Disk Space Information (for Installation Target):
Required: 496,188,997 Bytes
Available: 12,938,809,344 Bytes
PRESS <ENTER> TO CONTINUE:回车。
===============================================================================
Ready To Install
----------------
InstallAnywhere is now ready to install GBase Software Bundle onto your system
at the following location:
/opt/gbase
PRESS <ENTER> TO INSTALL:回车。
===============================================================================
Installing...
-------------
[==================|==================|==================|==================]
[------------------|------------------|------------------|------------------]
===============================================================================
Installation Complete
---------------------
Congratulations! GBase Software Bundle installation is complete.
Product install status:
GBase: Successful
GBase Connect: Successful
GBase 8s V8.8
For more information about using GBase products, see the GBase Information
Center at http://www.gbase.cn.
PRESS <ENTER> TO EXIT THE INSTALLER:回车。
[root@vmsvr2 install]#退出程序，安装完成。
修改配置参数
在安装目录的dbssodir目录中，有一个seccfg文件，该文件只有一个参数IXUSERS，设置该参数值IXUSERS=*。
[root@vmsvr2 ~]# su - gbasedbt
Last login: Tue Dec 7 16:54:06 CST 2021 on pts/2
[gbasedbt@vmsvr2 ~]$ cd /opt/gbase/dbssodir/
[gbasedbt@vmsvr2 dbssodir]$ ls
adtmasks.std seccfg
[gbasedbt@vmsvr2 dbssodir]$ cat seccfg
IXUSERS=
[gbasedbt@vmsvr2 dbssodir]$ vi seccfg
[gbasedbt@vmsvr2 dbssodir]$ cat seccfg
IXUSERS=*
[gbasedbt@vmsvr2 dbssodir]$创建数据库实例
在安装目录的etc中（本例中为/opt/gbase/etc），有一个脚本GBaseInit_gbasedbt.sh，可以使用该脚本创建一个数据库实例。
使用该脚本初始化数据库实例，需要在gbasedbt用户下操作。
[gbasedbt@vmsvr2 etc]$ sh GBaseInit_gbasedbt.sh
Initializing Program...OK
ENTER THE INSTANCE INFORMATION or PRESS <ENTER> TO ACCEPT THE DEFAULT.
ENTER GBASE INSTANCE NAME (GBASEDBTSERVER) [Default:gbaseserver]:选择默认值，回车继续。
GBASE INSTALL FOLDER LIST:
1) /opt/gbase
CHOOSE GBASE INSTALL FOLDER(GBASEDBTDIR) [Default:/opt/gbase]:选择默认值，回车继续。
CHOOSE SERVICE IP ADDRESS FROM THE LIST:
1) 192.168.3.12
2) 127.0.0.1
3) 192.168.122.1
ENTER THE NUMBER FOR YOUR CHOICE [Default:192.168.3.12]:选择默认值，回车继续。
SPECIFY THE PORT NUMBER FOR GBASE [Default:9088]:选择默认值，回车继续。
INITIALIZE TYPE:
1) TYPICAL -- Initialize the instance with all features configured with default values.
2) CUSTOM -- Initialize the instance with specific features that you need.
ENTER THE NUMBER FOR YOUR CHOICE [Default:1]: 2输入2，回车继续。
GBASE CHARACTER SET LIST:
1) en_US.8859-1
2) zh_CN.GB18030-2000
3) zh_CN.utf8
ENTER THE NUMBER FOR YOUR CHOICE [Default:1]: 3输入3，回车继续。
ENTER THE DATA SPACE PATH [Default:/opt/gbase/gbaseserver_dbs]:
ENTER THE LOGICAL LOG DBSPACE SIZE(MB) [Default:1240]: 200根据实际情况输入文件大小，本例输入200，回车继续。
ENTER THE PHYSICAL LOG DBSPACE SIZE(MB) [Default:1240]: 200根据实际情况输入文件大小，本例输入200，回车继续。
ENTER THE SMART LOB DBSPACE SIZE(MB) [Default:278]: 100根据实际情况输入文件大小，本例输入100，回车继续。
ENTER THE TEMPORARY DBSPACE SIZE(MB) [Default:278]: 100根据实际情况输入文件大小，本例输入100，回车继续。
ENTER "Y" TO START DATABASE ADVANCED SETTINGS, OR ENTER "N" TO ACCEPT DEFAULT VALUES: [Default:N]: y输入y，回车继续。
ENTER THE NUMBER OF DATA DBSPACES [Default:1]: 3输入3，回车继续。
ENTER THE NUMBER OF TEMP DBSPACES [Default:1]: 3输入3，回车继续。
Enter "Y" TO ENABLE ENVIRONMENT GL_USEGLU, OR ENTER "N" TO DISABLE: [Default:N]: y输入y，回车继续。
INSTANCE SUMMARY:
Instance name: gbaseserver
GBase install directory: /opt/gbase
IP address & port: 192.168.3.12:9088
Character set: zh_CN.utf8
Logical log space size: 200 MB
Physical log space size: 200 MB
Smart LOB space size: 100 MB
Temp DBSpace size: 100 MB
Data path: /opt/gbase/gbaseserver_dbs
Data path free size: 12280 MB
The number of data DBSpace: 3
The number of temp DBSpace: 3
Environment GL_USEGLU: 1
Enter "Y" to Start database initializing, or Enter "N" to Edit Again: y
Touching Chunks...OK
Create sqlhosts File: /opt/gbase/etc/sqlhosts.gbaseserver ...OK
Setting Parameters in /opt/gbase/etc/onconfig.gbaseserver :
IME.BUFFERPOOL2K.BUFFERPOOL16K...AUTO_CKPTS.OK
Initializing Root DBSpace & Share Memory...OK
Creating system database..........OK
Creating logical log Dbspace...OK
Creating physical log Dbspace...OK
Creating smart LOB Dbspace...OK
Creating temp Dbspace.1.2.3...OK
Creating data Dbspace.1.2.3...OK
Setting dbscheduler...OK
Moving physical log...OK
Adding 20 logical logs: 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20...OK
Setting data chunks extendable...OK
Cleaning logical logs in rootdbs...Your evaluation license will expire on 2022-12-03 00:00:00
.Your evaluation license will expire on 2022-12-03 00:00:00
.Your evaluation license will expire on 2022-12-03 00:00:00
.Your evaluation license will expire on 2022-12-03 00:00:00
...OK
Database restarting.......Your evaluation license will expire on 2022-12-03 00:00:00
OK
Creating database: gbasedb...OK
Now you can use this URL to connect to gbasedb:
2;GL_USEGLU=1
Version:
Your evaluation license will expire on 2022-12-03 00:00:00
On-Line -- Up 00:00:15 -- 674664 Kbytes
Build Number: 3.3.0_N303
GBase Initializing Finished!
Initialize log file : ./InitGBaseDB_202112030140.log
Press <ENTER> to Exit and Logout...
Killed数据库实例初始化成功后，脚本去自动退出gbasedbt用户。
配置用户环境变量（gbasedbt）
GBaseInit_gbasedbt.sh脚本会自动在gbasedbt用户下，创建一个数据库实例环境变量文件，默认的文件名为profile.<instance_name>，本例中的文件名为profile.gbaseserver。
[root@vmsvr2 ~]# su - gbasedbt
Last login: Tue Dec 7 15:09:12 CST 2021 on pts/0
[gbasedbt@vmsvr2 ~]$ cat profile.gbaseserver
export GBASEDBTSERVER=gbaseserver
export GBASEDBTDIR=/opt/gbase
export GBASEDBTSQLHOSTS=/opt/gbase/etc/sqlhosts.gbaseserver
export ONCONFIG=onconfig.gbaseserver
export PATH=/opt/gbase/bin:$PATH
export DB_LOCALE=zh_CN.utf8
export CLIENT_LOCALE=zh_CN.utf8
unset GL_USEGLU将该环境变量文件，复制到dbsso, dbaao, manager, finance, store, purchase_nc, purchase_ec, purchase_sc, sale_nc, sale_ec, sale_sc各用户的Home目录下。可以在每个用户的.bash_profile中添加source ~/profile.gbaseserver，保障在ssh到每个用户后，该环境变量均可生效。
参考的文件复制命令。
scp profile.gbaseserver dbsso@192.168.3.12:/home/dbsso
scp profile.gbaseserver dbaao@192.168.3.12:/home/dbaao
scp profile.gbaseserver manager@192.168.3.12:/home/manager
scp profile.gbaseserver finance@192.168.3.12:/home/finance
scp profile.gbaseserver store@192.168.3.12:/home/store
scp profile.gbaseserver purchase_nc@192.168.3.12:/home/purchase_nc
scp profile.gbaseserver purchase_ec@192.168.3.12:/home/purchase_ec
scp profile.gbaseserver purchase_sc@192.168.3.12:/home/purchase_sc
scp profile.gbaseserver sale_nc@192.168.3.12:/home/sale_nc
scp profile.gbaseserver sale_ec@192.168.3.12:/home/sale_ec
scp profile.gbaseserver sale_sc@192.168.3.12:/home/sale_sc参考的.bash_profile配置。
[gbasedbt@vmsvr2 ~]$ tail -n 1 .bash_profile
source ~/profile.gbaseserver三权分立功能验证
- 只有安全管理员可以控制要审计哪些内容。
- 只有审计管理员可以查看审计结果。
- 只有数据库管理员可以授权用户访问数据库。
- 数据库管理员和安全管理员，共同控制用户访问数据的权限。在未经授权的情况下，数据库管理员，安全管理员，审计管理员都不能访问表中的数据。
- 不同用户根据自身的权限等级，访问表中部分或全部数据。
配置审计掩码（dbsso）
GBase 8s目前共支持161类事件的审计。
当审计内容过多时，不但会严重影响数据库的性能，更为审计内容带来困难。安全管理员需要根据自身的安全需求及数据库性能需求，确定出符合自身实际情况的审计事件。常用的审计事件见附录。
- GBase 8s使用
onaudit命令，实现审计策略的配置和审计日志的查看。 - 只有dbsso用户，才可以配置审计掩码。
- 只有dbaao用户，才可以查看审计日志。
- gbasedbt用户，不能执行审计类工作。
当使用gbasedbt用户执行onaudit命令时：
[root@vmsvr2 ~]# su - gbasedbt
Last login: Tue Dec 7 17:14:16 CST 2021 on pts/2
[gbasedbt@vmsvr2 ~]$ onaudit
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
Must be an AAO or DBSSO to run this program.
[gbasedbt@vmsvr2 ~]$程序显示，只有审计管理员（AAO）和安全管理员（DBSSO）才能运行onaudit。
结论：数据库管理员，无法执行审计类工作，只有审计管理员和安全管理员，才可以执行审计类工作。
使用dbaao用户登录。
[root@vmsvr2 ~]# su - dbaao
[dbaao@vmsvr2 ~]$ source profile.gbaseserver
[dbaao@vmsvr2 ~]$ onaudit
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
Usage: onaudit <action> [-f file] [-u name] [-r bmsk] [-e eset] [-y]
onaudit [-c] [-n] [-l lev] [-e err] [-p path] [-s size]
action: one of
-a -- add a mask
-d -- delete a mask
-m -- modify a mask
-o -- output a mask
-r bmsk -- name of basemask
-c -- print audit configuration
-n -- start new log file
-l lev -- set ADTMODE
-e err -- set ADTERR
-p path -- set ADTPATH
-s size -- set ADTSIZE
-f file -- include instruction file
-u mask -- name of target/mask
-e eset -- event set added to (+) or removed from (-) mask
-R fga -- set ADTROWS for Fine-Grained Auditing
-y -- respond yes to all prompts使用dbaaoo用户，可以执行onaudit命令，执行审计类工作。
使用onaudit -o命令，查看审计掩码。
[dbaao@vmsvr2 ~]$ onaudit -o
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
Must be a DBSSO to execute this action.
[dbaao@vmsvr2 ~]$程序显示，只有安全管理员（DBSSO）才能运行onaudit -o命令。
使用dbsso用户登录。
[root@vmsvr2 ~]# su - dbsso
Last login: Tue Dec 7 17:37:54 CST 2021 on pts/2
[dbsso@vmsvr2 ~]$ source profile.gbaseserver
[dbsso@vmsvr2 ~]$ onaudit
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
Usage: onaudit <action> [-f file] [-u name] [-r bmsk] [-e eset] [-y]
onaudit [-c] [-n] [-l lev] [-e err] [-p path] [-s size]
action: one of
-a -- add a mask
-d -- delete a mask
-m -- modify a mask
-o -- output a mask
-r bmsk -- name of basemask
-c -- print audit configuration
-n -- start new log file
-l lev -- set ADTMODE
-e err -- set ADTERR
-p path -- set ADTPATH
-s size -- set ADTSIZE
-f file -- include instruction file
-u mask -- name of target/mask
-e eset -- event set added to (+) or removed from (-) mask
-R fga -- set ADTROWS for Fine-Grained Auditing
-y -- respond yes to all prompts使用dbsso用户，可以执行onaudit命令，执行审计类工作。
使用onaudit -o命令，查看审计掩码。
[dbsso@vmsvr2 ~]$ onaudit -o
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
This will list every row in the audit mask table
Do you wish to continue? [y/N]: y
[dbsso@vmsvr2 ~]$ onaudit -a -u _default -e +CRDB,DRDB,CRTB,DRTB,ALTB
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
[dbsso@vmsvr2 ~]$ onaudit -o
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
This will list every row in the audit mask table
Do you wish to continue? [y/N]: y
_default - ALTB,CRDB,CRTB,DRDB,DRTB
[dbsso@vmsvr2 ~]$使用dbsso用户，可以执行onaudit -a命令，添加审计掩码，可以执行onaudit -o命令，查看审计掩码的配置信息。
结论：数据库管理员，无法执行onaudit命令，执行审计类工作；审计管理员无法执行onaudit -o命令，查看审计掩码。只有安全管理员，才可以执行审计掩码的配置和查看。
配置审计参数（dbaao）
- GBase 8s使用
onaudit命令，实现审计策略的配置和审计日志的查看。 - 只有dbsso用户，才可以配置审计掩码。
- 只有dbaao用户，才可以查看审计日志。
- gbasedbt用户，不能执行审计类工作。
当使用gbasedbt用户执行onaudit命令时：
[root@vmsvr2 ~]# su - gbasedbt
Last login: Tue Dec 7 17:37:02 CST 2021 on pts/2
[gbasedbt@vmsvr2 ~]$ onaudit
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
Must be an AAO or DBSSO to run this program.
[gbasedbt@vmsvr2 ~]$程序显示，只有审计管理员（AAO）和安全管理员（DBSSO）才能运行onaudit。
结论：数据库管理员，无法执行审计类工作，只有审计管理员和安全管理员，才可以执行审计类工作。
使用dbsso用户登录。
[root@vmsvr2 ~]# su - dbsso
Last login: Tue Dec 7 17:54:50 CST 2021 on pts/2
[dbsso@vmsvr2 ~]$ source profile.gbaseserver
[dbsso@vmsvr2 ~]$ onaudit
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
Usage: onaudit <action> [-f file] [-u name] [-r bmsk] [-e eset] [-y]
onaudit [-c] [-n] [-l lev] [-e err] [-p path] [-s size]
action: one of
-a -- add a mask
-d -- delete a mask
-m -- modify a mask
-o -- output a mask
-r bmsk -- name of basemask
-c -- print audit configuration
-n -- start new log file
-l lev -- set ADTMODE
-e err -- set ADTERR
-p path -- set ADTPATH
-s size -- set ADTSIZE
-f file -- include instruction file
-u mask -- name of target/mask
-e eset -- event set added to (+) or removed from (-) mask
-R fga -- set ADTROWS for Fine-Grained Auditing
-y -- respond yes to all prompts使用dbsso用户，可以执行onaudit命令，执行审计类工作。
使用onaudit -c命令，查看审计配置信息。
[dbsso@vmsvr2 ~]$ onaudit -c
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
Must be an AAO to execute this action.
[dbsso@vmsvr2 ~]$程序显示，只有审计管理员（AAO）才能运行onaudit -c命令。
使用dbaao登录。
[root@vmsvr2 ~]# su - dbaao
Last login: Tue Dec 7 17:47:54 CST 2021 on pts/2
[dbaao@vmsvr2 ~]$ source profile.gbaseserver
[dbaao@vmsvr2 ~]$ onaudit
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
Usage: onaudit <action> [-f file] [-u name] [-r bmsk] [-e eset] [-y]
onaudit [-c] [-n] [-l lev] [-e err] [-p path] [-s size]
action: one of
-a -- add a mask
-d -- delete a mask
-m -- modify a mask
-o -- output a mask
-r bmsk -- name of basemask
-c -- print audit configuration
-n -- start new log file
-l lev -- set ADTMODE
-e err -- set ADTERR
-p path -- set ADTPATH
-s size -- set ADTSIZE
-f file -- include instruction file
-u mask -- name of target/mask
-e eset -- event set added to (+) or removed from (-) mask
-R fga -- set ADTROWS for Fine-Grained Auditing
-y -- respond yes to all prompts使用dbaao用户，可以执行onaudit命令，执行审计类工作。
[dbaao@vmsvr2 ~]$ onaudit -c
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
Current audit system configuration:
ADTMODE = 0
ADTERR = 0
ADTPATH = /usr/gbasedbt/aaodir
ADTSIZE = 50000
Audit file = 0
ADTROWS = 0
[dbaao@vmsvr2 ~]$ mkdir aaodir
[dbaao@vmsvr2 ~]$ onaudit -p /home/dbaao/aaodir/
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
[dbaao@vmsvr2 ~]$ onaudit -c
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
Current audit system configuration:
ADTMODE = 0
ADTERR = 0
ADTPATH = /home/dbaao/aaodir/
ADTSIZE = 50000
Audit file = 0
ADTROWS = 0
[dbaao@vmsvr2 ~]$使用dbaao用户，可以执行onaudit -p命令，修改审计配置信息，可以执行onaudit -c命令，查看审计配置信息。
结论：数据库管理员，无法执行onaudit命令，执行审计类工作；安全管理员无法执行onaudit -c命令，查看审计配置信息。只有审计管理员，才可以执行审计配置信息的修改和查看。
使用onaudit -l命令启动审计。
[dbaao@vmsvr2 ~]$ onaudit -l 1
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
[dbaao@vmsvr2 ~]$ onaudit -c
Your evaluation license will expire on 2022-12-07 00:00:00
Onaudit -- Audit Subsystem Configuration Utility
Current audit system configuration:
ADTMODE = 1
ADTERR = 0
ADTPATH = /home/dbaao/aaodir/
ADTSIZE = 50000
Audit file = 0
ADTROWS = 0
[dbaao@vmsvr2 ~]$查看审计日志（dbaao）
使用gbasedbt用户，执行建库，建表，删表，删库操作。
[root@vmsvr2 ~]# su - gbasedbt
Last login: Wed Dec 8 13:25:43 CST 2021 on pts/0
[gbasedbt@vmsvr2 ~]$ dbaccess - -
Your evaluation license will expire on 2022-12-07 00:00:00
> create database mydb in datadbs1 with log;
Database created.
> create table t_user(f_userid int, f_username varchar(20));
Table created.
> drop table t_user;
Table dropped.
> drop database mydb;
359: Cannot drop or rename the current database or any open database.
Error in line 1
Near character position 17
> database sysmaster;
Database closed.
Database selected.
> drop database mydb;
Database dropped.
>
Database closed.使用dbaao用户，查看审计日志。
[gbasedbt@vmsvr2 ~]$ su - dbaao
Password:
Last login: Wed Dec 8 13:24:00 CST 2021 on pts/1
[dbaao@vmsvr2 ~]$ cd aaodir/
[dbaao@vmsvr2 aaodir]$ ls
gbaseserver.2
[dbaao@vmsvr2 aaodir]$ cat gbaseserver.2
ONLN|2021-12-08 13:40:36.342|vmsvr2|66787|gbaseserver|gbasedbt|0:CRDB:mydb:datadbs1
ONLN|2021-12-08 13:40:53.232|vmsvr2|66787|gbaseserver|gbasedbt|0:CRTB:mydb:100:t_user:gbasedbt:0:-
ONLN|2021-12-08 13:41:05.775|vmsvr2|66787|gbaseserver|gbasedbt|0:DRTB:mydb:100:t_user:gbasedbt:0:8388748
ONLN|2021-12-08 13:41:13.023|vmsvr2|66787|gbaseserver|gbasedbt|-359:DRDB:mydb:-
ONLN|2021-12-08 13:41:31.874|vmsvr2|66787|gbaseserver|gbasedbt|0:DRDB:mydb:-
[dbaao@vmsvr2 aaodir]$ pwd
/home/dbaao/aaodir从审计日志看，成功记录了创建mydb数据库，成功创建了t_user表，成功删除了t_user表，第一次删除mydb数据库失败，第二次删除mydb数据库成功。
尝试使用dbsso用户和gbasedbt用户访问审计日志，均因权限不足被拒绝。
[dbaao@vmsvr2 aaodir]$ su - dbsso
Password:
Last login: Wed Dec 8 13:27:17 CST 2021 on pts/1
[dbsso@vmsvr2 ~]$ cat /home/dbaao/aaodir/gbaseserver.2
cat: /home/dbaao/aaodir/gbaseserver.2: Permission denied
[dbsso@vmsvr2 ~]$ exit
logout
[dbaao@vmsvr2 aaodir]$ exit
logout
[gbasedbt@vmsvr2 ~]$ cat /home/dbaao/aaodir/gbaseserver.2
cat: /home/dbaao/aaodir/gbaseserver.2: Permission denied
[gbasedbt@vmsvr2 ~]$结论：只有dbaao用户可以访问审计日志，dbsso和gbasedbt均无权访问。
创建数据库（gbasedbt）
create database mydb in datadbs1 with log;演示代码
[root@vmsvr2 ~]# su - gbasedbt
Last login: Wed Dec 8 13:40:20 CST 2021 on pts/0
[gbasedbt@vmsvr2 ~]$ dbaccess - -
Your evaluation license will expire on 2022-12-07 00:00:00
> create database mydb in datadbs1 with log;
Database created.
>用户授权（gbasedbt/自主访问控制）
database mydb;
grant resource to manager;
grant connect to finance;
grant connect to store;
grant connect to purchase_nc;
grant connect to purchase_ec;
grant connect to purchase_sc;
grant connect to sale_nc;
grant connect to sale_ec;
grant connect to sale_sc;演示代码
[root@vmsvr2 ~]# su - gbasedbt
Last login: Wed Dec 8 13:40:20 CST 2021 on pts/0
[gbasedbt@vmsvr2 ~]$ dbaccess - -
Your evaluation license will expire on 2022-12-07 00:00:00
> database mydb;
Database closed.
Database selected.
> grant resource to manager;
Permission granted.
> grant connect to finance;
Permission granted.
> grant connect to store;
Permission granted.
> grant connect to purchase_nc;
Permission granted.
> grant connect to purchase_ec;
Permission granted.
> grant connect to purchase_sc;
Permission granted.
> grant connect to sale_nc;
Permission granted.
> grant connect to sale_ec;
Permission granted.
> grant connect to sale_sc;
Permission granted.
>创建安全标签（dbsso/强制访问控制）
create security label component security_level array['Secret', 'Confidential', 'Unclassified'];
create security label component goods_source array['OEM', 'Wholesale', 'Retail'];
create security label component organization_code set{'head', 'nc', 'ec', 'sc'};
create security policy goods components security_level, goods_source, organization_code;
create security label goods.manager
component security_level 'Secret',
component goods_source 'OEM',
component organization_code 'head', 'nc', 'ec', 'sc';
create security label goods.finance
component security_level 'Secret',
component goods_source 'Wholesale',
component organization_code 'head', 'nc', 'ec', 'sc';
create security label goods.store
component security_level 'Unclassified',
component goods_source 'Retail'
component organization_code 'head', 'nc', 'ec', 'sc';
create security label goods.purchase_nc
component security_level 'Secret',
component goods_source 'Wholesale',
component organization_code 'nc';
create security label goods.purchase_ec
component security_level 'Secret',
component goods_source 'Wholesale',
component organization_code 'ec';
create security label goods.purchase_sc
component security_level 'Secret',
component goods_source 'Wholesale',
component organization_code 'sc';
create security label goods.sale_nc
component security_level 'Confidential',
component goods_source 'Wholesale',
component organization_code 'nc';
create security label goods.sale_ec
component security_level 'Confidential',
component goods_source 'Wholesale',
component organization_code 'ec';
create security label goods.sale_sc
component security_level 'Confidential',
component goods_source 'Wholesale',
component organization_code 'sc';
create security label goods.secret
component security_level 'Secret';
create security label goods.confidential
component security_level 'Confidential';
create security label goods.unclassified
component security_level 'Unclassified';
create security label goods.confidential_nc
component security_level 'Confidential',
component organization_code 'nc';
create security label goods.confidential_ec
component security_level 'Confidential',
component organization_code 'ec';
create security label goods.confidential_sc
component security_level 'Confidential',
component organization_code 'sc';
create security label goods.unclassified_nc
component security_level 'Unclassified',
component organization_code 'nc';
create security label goods.unclassified_ec
component security_level 'Unclassified',
component organization_code 'ec';
create security label goods.unclassified_sc
component security_level 'Unclassified',
component organization_code 'sc';使用gbasedbt用户，创建安全标签组件。
[root@vmsvr2 ~]# su - gbasedbt
Last login: Wed Dec 8 14:45:41 CST 2021 on pts/0
[gbasedbt@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> create security label component security_level array['Secret', 'Confidential', 'Unclassified'];
8200: User (gbasedbt) does not have DBSECADM authority.
Error in line 1
Near character position 93
>
Database closed.
[gbasedbt@vmsvr2 ~]$ exit
logout由于gbasedbt不是DBSECADM，所以无法创建安全标签组件。
使用dbaao用户，创建安全标签组件。
[root@vmsvr2 ~]# su - dbaao
Last login: Wed Dec 8 13:41:41 CST 2021 on pts/0
[dbaao@vmsvr2 ~]$ source profile.gbaseserver
[dbaao@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
387: No connect permission.
111: ISAM error: no record found.
[dbaao@vmsvr2 ~]$dbaao用户无法连接mydb数据库，无法创建安全标签组件。
使用dbsso用户，创建安全标签组件。
[root@vmsvr2 ~]# su - dbsso
Last login: Wed Dec 8 13:54:52 CST 2021 on pts/0
[dbsso@vmsvr2 ~]$ source profile.gbaseserver
[dbsso@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
387: No connect permission.
111: ISAM error: no record found.
[dbsso@vmsvr2 ~]$目前dbsso无权连接mydb数据库，需要数据库管理员对安全管理员授权后，才能连接。
[gbasedbt@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> grant connect to dbsso;
Permission granted.
>再次使用dbsso创建安全标签组件。
[root@vmsvr2 ~]# su - dbsso
Last login: Wed Dec 8 15:11:23 CST 2021 on pts/0
[dbsso@vmsvr2 ~]$ source profile.gbaseserver
[dbsso@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> create security label component security_level array['Secret', 'Confidential', 'Unclassified'];
8200: User (dbsso) does not have DBSECADM authority.
Error in line 1
Near character position 93
>由于dbsso目前还没有获取DBSECADM权限，仍然无法创建安全标签组件。
使用gbasedbt用户为dbsso授予DBSECADM权限。
[root@vmsvr2 ~]# su - gbasedbt
Last login: Wed Dec 8 15:14:15 CST 2021 on pts/0
[gbasedbt@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> grant DBSECADM to dbsso;
DBSECADM granted.
>使用dbsso用户创建安全标签组件，安全策略，安全标签。
[root@vmsvr2 ~]# su - dbsso
Last login: Wed Dec 8 15:15:56 CST 2021 on pts/0
[dbsso@vmsvr2 ~]$ source profile.gbaseserver
[dbsso@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> create security label component security_level array['Secret', 'Confidential', 'Unclassified'];
Security label component created.
> create security label component goods_source array['OEM', 'Wholesale', 'Retail'];
Security label component created.
> create security label component organization_code set{'head', 'nc', 'ec', 'sc'};
Security label component created.
> create security policy goods components security_level, goods_source, organization_code;
Security policy created.
> create security label goods.manager
component security_level 'Secret',
component goods_source 'OEM',
component organization_code 'head', 'nc', 'ec', 'sc';> > >
Security label created.
> create security label goods.finance
component security_level 'Secret',
component goods_source 'Wholesale',
component organization_code 'head', 'nc', 'ec', 'sc';> > >
Security label created.
> create security label goods.store
component security_level 'Unclassified',
component goods_source 'Retail'
component organization_code 'head', 'nc', 'ec', 'sc';> > >
Security label created.
> create security label goods.purchase_nc
component security_level 'Secret',
component goods_source 'Wholesale',
component organization_code 'nc';> > >
Security label created.
> create security label goods.purchase_ec
component security_level 'Secret',
component goods_source 'Wholesale',
component organization_code 'ec';> > >
Security label created.
> create security label goods.purchase_sc
component security_level 'Secret',
component goods_source 'Wholesale',
component organization_code 'sc';> > >
Security label created.
> create security label goods.sale_nc
component security_level 'Confidential',
component goods_source 'Wholesale',
component organization_code 'nc';> > >
Security label created.
> create security label goods.sale_ec
component security_level 'Confidential',
component goods_source 'Wholesale',
component organization_code 'ec';> > >
Security label created.
> create security label goods.sale_sc
component security_level 'Confidential',
component goods_source 'Wholesale',
component organization_code 'sc';> > >
Security label created.
> create security label goods.secret
component security_level 'Secret';>
Security label created.
> create security label goods.confidential
component security_level 'Confidential';>
Security label created.
> create security label goods.unclassified
component security_level 'Unclassified';>
Security label created.
> create security label goods.confidential_nc
component security_level 'Confidential',
component organization_code 'nc';> >
Security label created.
> create security label goods.confidential_ec
component security_level 'Confidential',
component organization_code 'ec';> >
Security label created.
> create security label goods.confidential_sc
component security_level 'Confidential',
component organization_code 'sc';> >
Security label created.
> create security label goods.unclassified_nc
component security_level 'Unclassified',
component organization_code 'nc';> >
Security label created.
> create security label goods.unclassified_ec
component security_level 'Unclassified',
component organization_code 'ec';> >
Security label created.
> create security label goods.unclassified_sc
component security_level 'Unclassified',
component organization_code 'sc';> >
Security label created.
>结论：安全管理员dbsso只有在数据库管理员gbasedbt授权后，才能在数据库中创建安全标签。
为用户授予安全标签（dbsso/强制访问控制）
grant security label goods.manager to user manager for all access;
grant security label goods.finance to user finance for read access;
grant security label goods.store to user store for read access;
grant security label goods.purchase_nc to user purchase_nc for all access;
grant security label goods.purchase_ec to user purchase_ec for all access;
grant security label goods.purchase_sc to user purchase_sc for all access;
grant security label goods.sale_nc to user sale_nc for all access;
grant security label goods.sale_ec to user sale_ec for all access;
grant security label goods.sale_sc to user sale_sc for all access;
grant exemption on rule idslbacwritearray writedown for goods to user manager;
grant exemption on rule idslbacwritearray writedown for goods to user purchase_nc;
grant exemption on rule idslbacwritearray writedown for goods to user purchase_ec;
grant exemption on rule idslbacwritearray writedown for goods to user purchase_sc;
grant exemption on rule idslbacwritearray writedown for goods to user sale_nc;
grant exemption on rule idslbacwritearray writedown for goods to user sale_ec;
grant exemption on rule idslbacwritearray writedown for goods to user sale_sc;操作演示
使用dbsso为用户授予标签。
[root@vmsvr2 ~]# su - dbsso
Last login: Wed Dec 8 15:31:59 CST 2021 on pts/0
[dbsso@vmsvr2 ~]$ source profile.gbaseserver
[dbsso@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> grant security label goods.manager to user manager for all access;
Security label granted.
> grant security label goods.finance to user finance for read access;
Security label granted.
> grant security label goods.store to user store for read access;
Security label granted.
> grant security label goods.purchase_nc to user purchase_nc for all access;
Security label granted.
> grant security label goods.purchase_ec to user purchase_ec for all access;
Security label granted.
> grant security label goods.purchase_sc to user purchase_sc for all access;
Security label granted.
> grant security label goods.sale_nc to user sale_nc for all access;
Security label granted.
> grant security label goods.sale_ec to user sale_ec for all access;
Security label granted.
> grant security label goods.sale_sc to user sale_sc for all access;
Security label granted.默认情况下，用户只能操作与用户标签完全相同安全等级的数据，我们可以通过豁免权，让用户可以操作与安全等级相同，或操作低于用户安全等级的数据。
> grant exemption on rule idslbacwritearray writedown for goods to user manager;
Security exemption granted.
> grant exemption on rule idslbacwritearray writedown for goods to user purchase_nc;
Security exemption granted.
> grant exemption on rule idslbacwritearray writedown for goods to user purchase_ec;
Security exemption granted.
> grant exemption on rule idslbacwritearray writedown for goods to user purchase_sc;
Security exemption granted.
> grant exemption on rule idslbacwritearray writedown for goods to user sale_nc;
Security exemption granted.
> grant exemption on rule idslbacwritearray writedown for goods to user sale_ec;
Security exemption granted.
> grant exemption on rule idslbacwritearray writedown for goods to user sale_sc;
Security exemption granted.
>创建表（dbsso）
create table t_goods(f_goodsid serial column secured with unclassified,
f_goodsname varchar(50) column secured with unclassified,
f_price_in decimal(18, 2) column secured with secret,
f_price_out decimal(18, 2) column secured with confidential,
f_qty decimal(18, 2) column secured with unclassified,
f_operator varchar(20) column secured with unclassified,
f_flag idssecuritylabel)
security policy goods;操作演示
使用gbasedbt用户创建表。
[root@vmsvr2 ~]# su - gbasedbt
Last login: Wed Dec 8 15:19:43 CST 2021 on pts/0
[gbasedbt@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> create table t_goods(f_goodsid serial column secured with unclassified,
f_goodsname varchar(50) column secured with unclassified,
f_price_in decimal(18, 2) column secured with secret,
f_price_out decimal(18, 2) column secured with confidential,
f_qty decim> > > > al(18, 2) column secured with unclassified,
f_operator varchar(20) column secured with unclassified,
f_flag idssecuritylabel)
security policy goods;> > >
8200: User (gbasedbt) does not have DBSECADM authority.
Error in line 8
Near character position 19
>由于gbasedbt不是安全管理员，因此无法创建具有安全功能的表。
使用dbsso用户创建表。
[root@vmsvr2 ~]# su - dbsso
Last login: Wed Dec 8 15:55:18 CST 2021 on pts/0
[dbsso@vmsvr2 ~]$ source profile.gbaseserver
[dbsso@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> create table t_goods(f_goodsid serial column secured with unclassified,
f_goodsname varchar(50) column secured with unclassified,
f_price_in decimal(18, 2) column secured with secret,
f_price_out decimal(18, 2) column secured with confidential,
f_qty decimal(18, 2) column secured with unclassified,
f_operator varchar(20) column secured with unclassified,
f_flag idssecuritylabel)
security policy goods;> > > > > > >
388: No resource permission.
Error in line 8
Near character position 19
>由于dbsso对mydb没有resource权限，创建表失败。
使用gbasedbt为用户dbsso授予resource权限。
[root@vmsvr2 ~]# su - gbasedbt
Last login: Wed Dec 8 16:03:30 CST 2021 on pts/0
[gbasedbt@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> grant resource to dbsso;
Permission granted.
>再次使用dbsso用户创建表。
[root@vmsvr2 ~]# su - dbsso
Last login: Wed Dec 8 16:05:54 CST 2021 on pts/0
[dbsso@vmsvr2 ~]$ source profile.gbaseserver
[dbsso@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> create table t_goods(f_goodsid serial column secured with unclassified,
f_goodsname varchar(50) column secured with unclassified,
f_price_in decimal(18, 2) column secured with secret,
f_price_out decimal(18, 2) column secured with confidential,
f_qty decimal(18, 2) column secured with unclassified,
f_operator varchar(20) column secured with unclassified,
f_flag idssecuritylabel)
security policy goods;> > > > > > >
Table created.
>在经过gbasedbt为dbsso授权后，dbsso可以创建带有安全功能的表。
插入数据（manager）
insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator) values('Windows10',588.00, 10000, 'manager');
insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('新冠疫苗（国药）', 199.00, 500000, 'manager', seclabel_by_name('goods', 'confidential'));
insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('Oracle 12C', 140000.00, 5, 'manager', seclabel_by_name('goods', 'unclassified_nc'));
insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('SQL Server 2017', 9999.00, 10, 'manager', seclabel_by_name('goods', 'unclassified_ec'));
insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('福临门花生油', 58.00, 10000, 'manager', seclabel_by_name('goods', 'unclassified'));使用manager用户，插入5条记录。
[root@vmsvr2 ~]# su - manager
[manager@vmsvr2 ~]$ source profile.gbaseserver
[manager@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator) values('Windows10',588.00, 10000, 'manager');
1 row(s) inserted.
> insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('新冠疫苗（国药）', 199.00, 500000, 'manager', seclabel_by_name('goods', 'confidential'));
1 row(s) inserted.
> insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('Oracle 12C', 140000.00, 5, 'manager', seclabel_by_name('goods', 'unclassified_nc'));
1 row(s) inserted.
> insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('SQL Server 2017', 9999.00, 10, 'manager', seclabel_by_name('goods', 'unclassified_ec'));
1 row(s) inserted.
> insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('福临门花生油', 58.00, 10000, 'manager', seclabel_by_name('goods', 'unclassified'));
1 row(s) inserted.
>插入数据（purchase_nc）
insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator) values('北京奔驰汽车',333333.00, 10, 'purchase_nc');
insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('北京烤鸭',188.00, 100, 'purchase_nc', seclabel_by_name('goods', 'unclassified_nc'));
insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('故宫门票',50.00, 1000, 'purchase_nc', seclabel_by_name('goods', 'unclassified'));使用purchase_nc用户，插入3条记录。
[root@vmsvr2 ~]# su - purchase_nc
[purchase_nc@vmsvr2 ~]$ source profile.gbaseserver
[purchase_nc@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator) values('北京奔驰汽车',333333.00, 10, 'purchase_nc');
1 row(s) inserted.
> insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('北京烤鸭',188.00, 100, 'purchase_nc', seclabel_by_name('goods', 'unclassified_nc'));
1 row(s) inserted.
> insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('故宫门票',50.00, 1000, 'purchase_nc', seclabel_by_name('goods', 'unclassified'));
1 row(s) inserted.
>插入数据（purchase_ec）
insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator) values('特斯拉汽车',250000.00, 20, 'purchase_ec');
insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('杭州小笼包',18.80, 200, 'purchase_ec', seclabel_by_name('goods', 'unclassified_ec'));
insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('上海迪士尼门票',888.00, 2000, 'purchase_ec', seclabel_by_name('goods', 'unclassified'));使用purchase_ec用户，插入3条记录。
[root@vmsvr2 ~]# su - purchase_ec
[purchase_ec@vmsvr2 ~]$ source profile.gbaseserver
[purchase_ec@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator) values('特斯拉汽车',250000.00, 20, 'purchase_ec');
1 row(s) inserted.
> insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('杭州小笼包',18.80, 200, 'purchase_ec', seclabel_by_name('goods', 'unclassified_ec'));
1 row(s) inserted.
> insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('上海迪士尼门票',888.00, 2000, 'purchase_ec', seclabel_by_name('goods', 'unclassified'));
1 row(s) inserted.
>插入数据（purchase_sc）
insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator) values('广汽凯美瑞',228888.00, 500, 'purchase_sc');
insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('广州腊肠',28.80, 300, 'purchase_sc', seclabel_by_name('goods', 'unclassified_sc'));
insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('桂林门票',288.00, 300, 'purchase_sc', seclabel_by_name('goods', 'unclassified'));使用purchase_sc用户，插入3条记录。
[root@vmsvr2 ~]# su - purchase_sc
[purchase_sc@vmsvr2 ~]$ source profile.gbaseserver
[purchase_sc@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator) values('广汽凯美瑞',228888.00, 500, 'purchase_sc');
1 row(s) inserted.
> insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('广州腊肠',28.80, 300, 'purchase_sc', seclabel_by_name('goods','unclassified_sc'));
1 row(s) inserted.
> insert into t_goods(f_goodsname, f_price_in, f_qty, f_operator, f_flag) values('桂林门票',288.00, 300, 'purchase_sc', seclabel_by_name('goods', 'unclassified'));
1 row(s) inserted.
>查询数据（manager）
select * from t_goods;使用manager用户，查询商品表t_goods。
[root@vmsvr2 ~]# su - manager
Last login: Wed Dec 8 16:48:54 CST 2021 on pts/0
[manager@vmsvr2 ~]$ source profile.gbaseserver
[manager@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> info tables;
Table name
t_goods
> select * from t_goods;
f_goodsid 1
f_goodsname Windows10
f_price_in 588.00
f_price_out
f_qty 10000.00
f_operator manager
f_flag BFFFFFFFFFFFFFFDBFFFFFFFFFFFFFFD000000000000000F
f_goodsid 2
f_goodsname 新冠疫苗（国药）
f_price_in 199.00
f_price_out
f_qty 500000.00
f_operator manager
f_flag 7FFFFFFFFFFFFFFE00000000000000000000000000000000
f_goodsid 3
f_goodsname Oracle 12C
f_price_in 140000.00
f_price_out
f_qty 5.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000002
f_goodsid 4
f_goodsname SQL Server 2017
f_price_in 9999.00
f_price_out
f_qty 10.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000004
f_goodsid 5
f_goodsname 福临门花生油
f_price_in 58.00
f_price_out
f_qty 10000.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 6
f_goodsname 北京奔驰汽车
f_price_in 333333.00
f_price_out
f_qty 10.00
f_operator purchase_nc
f_flag BFFFFFFFFFFFFFFD7FFFFFFFFFFFFFFE0000000000000002
f_goodsid 7
f_goodsname 北京烤鸭
f_price_in 188.00
f_price_out
f_qty 100.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000002
f_goodsid 8
f_goodsname 故宫门票
f_price_in 50.00
f_price_out
f_qty 1000.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 9
f_goodsname 特斯拉汽车
f_price_in 250000.00
f_price_out
f_qty 20.00
f_operator purchase_ec
f_flag BFFFFFFFFFFFFFFD7FFFFFFFFFFFFFFE0000000000000004
f_goodsid 10
f_goodsname 杭州小笼包
f_price_in 18.80
f_price_out
f_qty 200.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000004
f_goodsid 11
f_goodsname 上海迪士尼门票
f_price_in 888.00
f_price_out
f_qty 2000.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 12
f_goodsname 广汽凯美瑞
f_price_in 228888.00
f_price_out
f_qty 500.00
f_operator purchase_sc
f_flag BFFFFFFFFFFFFFFD7FFFFFFFFFFFFFFE0000000000000008
f_goodsid 13
f_goodsname 广州腊肠
f_price_in 28.80
f_price_out
f_qty 300.00
f_operator purchase_sc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000008
f_goodsid 14
f_goodsname 桂林门票
f_price_in 288.00
f_price_out
f_qty 300.00
f_operator purchase_sc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
14 row(s) retrieved.
>由于manager具有最高的权限，他可以访问所有数据，查询返回了14条数据。
查询数据（finance）
select * from t_goods;使用finance用户，查询商品表t_goods。
[root@vmsvr2 ~]# su - finance
[finance@vmsvr2 ~]$ source profile.gbaseserver
[finance@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> select * from t_goods;
f_goodsid 2
f_goodsname 新冠疫苗（国药）
f_price_in 199.00
f_price_out
f_qty 500000.00
f_operator manager
f_flag 7FFFFFFFFFFFFFFE00000000000000000000000000000000
f_goodsid 3
f_goodsname Oracle 12C
f_price_in 140000.00
f_price_out
f_qty 5.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000002
f_goodsid 4
f_goodsname SQL Server 2017
f_price_in 9999.00
f_price_out
f_qty 10.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000004
f_goodsid 5
f_goodsname 福临门花生油
f_price_in 58.00
f_price_out
f_qty 10000.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 6
f_goodsname 北京奔驰汽车
f_price_in 333333.00
f_price_out
f_qty 10.00
f_operator purchase_nc
f_flag BFFFFFFFFFFFFFFD7FFFFFFFFFFFFFFE0000000000000002
f_goodsid 7
f_goodsname 北京烤鸭
f_price_in 188.00
f_price_out
f_qty 100.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000002
f_goodsid 8
f_goodsname 故宫门票
f_price_in 50.00
f_price_out
f_qty 1000.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 9
f_goodsname 特斯拉汽车
f_price_in 250000.00
f_price_out
f_qty 20.00
f_operator purchase_ec
f_flag BFFFFFFFFFFFFFFD7FFFFFFFFFFFFFFE0000000000000004
f_goodsid 10
f_goodsname 杭州小笼包
f_price_in 18.80
f_price_out
f_qty 200.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000004
f_goodsid 11
f_goodsname 上海迪士尼门票
f_price_in 888.00
f_price_out
f_qty 2000.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 12
f_goodsname 广汽凯美瑞
f_price_in 228888.00
f_price_out
f_qty 500.00
f_operator purchase_sc
f_flag BFFFFFFFFFFFFFFD7FFFFFFFFFFFFFFE0000000000000008
f_goodsid 13
f_goodsname 广州腊肠
f_price_in 28.80
f_price_out
f_qty 300.00
f_operator purchase_sc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000008
f_goodsid 14
f_goodsname 桂林门票
f_price_in 288.00
f_price_out
f_qty 300.00
f_operator purchase_sc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
13 row(s) retrieved.
>由于财务人员（finance）只能查看集团采购类和零售类商品，无权查看OEM类商品。由manager录入的'Windows10属于OEM类商品，财务人员无权查看此记录，因此财务人员只能查看13条记录。
查询数据（store）
select * from t_goods;
select f_goodsid,f_goodsname,f_qty,f_operator,f_flag from t_goods;
info columns for t_goods;使用store用户，查询商品表t_goods。
[root@vmsvr2 ~]# su - store
[store@vmsvr2 ~]$ source profile.gbaseserver
[store@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> select * from t_goods;
8245: User cannot perform READ access to the protected column (f_price_in).
Error in line 1
Near character position 20由于库管人员（store）无权查看价格，所以使用select * from t_goods查询返回错误。
> info columns for t_goods;
Column name Type Nulls
f_goodsid serial no
f_goodsname varchar(50,0) yes
f_price_in decimal(18,2) yes
f_price_out decimal(18,2) yes
f_qty decimal(18,2) yes
f_operator varchar(20,0) yes
f_flag idssecuritylabel yes
> select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods;
8245: User cannot perform READ access to the protected column (f_price_out).
Error in line 1
Near character position 76由于库管人员（store）无权查看价格，所以使用select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods查询返回错误。
> select f_goodsid,f_goodsname,f_qty,f_operator,f_flag from t_goods;
f_goodsid 3
f_goodsname Oracle 12C
f_qty 5.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000002
f_goodsid 4
f_goodsname SQL Server 2017
f_qty 10.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000004
f_goodsid 5
f_goodsname 福临门花生油
f_qty 10000.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 7
f_goodsname 北京烤鸭
f_qty 100.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000002
f_goodsid 8
f_goodsname 故宫门票
f_qty 1000.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 10
f_goodsname 杭州小笼包
f_qty 200.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000004
f_goodsid 11
f_goodsname 上海迪士尼门票
f_qty 2000.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 13
f_goodsname 广州腊肠
f_qty 300.00
f_operator purchase_sc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000008
f_goodsid 14
f_goodsname 桂林门票
f_qty 300.00
f_operator purchase_sc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
9 row(s) retrieved.
>在去除库管人员（store）无权访问的两列价格数据后，查询可以执行，并返回9条记录。
库管人员（store）无权访问秘密和敏感的数据只能访问未分类的数据。manager录入的Windows10和各区域的采购人员录入的3个汽车商品均属于秘密商品，新冠疫苗（国药）属于敏感商品，所以库管人员只能查看9条记录。
查询数据（purchase_nc）
select * from t_goods;使用purchase_nc用户，查询商品表t_goods。
[root@vmsvr2 ~]# su - purchase_nc
Last login: Wed Dec 8 17:04:24 CST 2021 on pts/0
[purchase_nc@vmsvr2 ~]$ source profile.gbaseserver
[purchase_nc@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> select * from t_goods;
f_goodsid 2
f_goodsname 新冠疫苗（国药）
f_price_in 199.00
f_price_out
f_qty 500000.00
f_operator manager
f_flag 7FFFFFFFFFFFFFFE00000000000000000000000000000000
f_goodsid 3
f_goodsname Oracle 12C
f_price_in 140000.00
f_price_out
f_qty 5.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000002
f_goodsid 5
f_goodsname 福临门花生油
f_price_in 58.00
f_price_out
f_qty 10000.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 6
f_goodsname 北京奔驰汽车
f_price_in 333333.00
f_price_out
f_qty 10.00
f_operator purchase_nc
f_flag BFFFFFFFFFFFFFFD7FFFFFFFFFFFFFFE0000000000000002
f_goodsid 7
f_goodsname 北京烤鸭
f_price_in 188.00
f_price_out
f_qty 100.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000002
f_goodsid 8
f_goodsname 故宫门票
f_price_in 50.00
f_price_out
f_qty 1000.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 11
f_goodsname 上海迪士尼门票
f_price_in 888.00
f_price_out
f_qty 2000.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 14
f_goodsname 桂林门票
f_price_in 288.00
f_price_out
f_qty 300.00
f_operator purchase_sc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
8 row(s) retrieved.
>对于华北区的采购，他们只能查看标记为本区域的记录或未标记区域的记录。因此purchase_nc可以查看自己录入的3条记录，其它区域采购人员录入的门票类（无区域标记）共计2条商品，manager录入的2条无区域标记商品，1条标记为华北区的商品，合计8条商品。
华北区采购，不能查看其它区域采购录入的4条商品，不能查看manager录入1条华东区商品，不能查看1条OEM类商品。
查询数据（purchase_ec）
select * from t_goods;使用purchase_ec用户，查询商品表t_goods。
[root@vmsvr2 ~]# su - purchase_ec
Last login: Wed Dec 8 17:06:28 CST 2021 on pts/0
[purchase_ec@vmsvr2 ~]$ source profile.gbaseserver
[purchase_ec@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> select * from t_goods;
f_goodsid 2
f_goodsname 新冠疫苗（国药）
f_price_in 199.00
f_price_out
f_qty 500000.00
f_operator manager
f_flag 7FFFFFFFFFFFFFFE00000000000000000000000000000000
f_goodsid 4
f_goodsname SQL Server 2017
f_price_in 9999.00
f_price_out
f_qty 10.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000004
f_goodsid 5
f_goodsname 福临门花生油
f_price_in 58.00
f_price_out
f_qty 10000.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 8
f_goodsname 故宫门票
f_price_in 50.00
f_price_out
f_qty 1000.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 9
f_goodsname 特斯拉汽车
f_price_in 250000.00
f_price_out
f_qty 20.00
f_operator purchase_ec
f_flag BFFFFFFFFFFFFFFD7FFFFFFFFFFFFFFE0000000000000004
f_goodsid 10
f_goodsname 杭州小笼包
f_price_in 18.80
f_price_out
f_qty 200.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000004
f_goodsid 11
f_goodsname 上海迪士尼门票
f_price_in 888.00
f_price_out
f_qty 2000.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 14
f_goodsname 桂林门票
f_price_in 288.00
f_price_out
f_qty 300.00
f_operator purchase_sc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
8 row(s) retrieved.
>华东区采购的数据访问情况，和华北区类似，不再说明。
查询数据（purchase_sc）
select * from t_goods;使用purchase_sc用户，查询商品表t_goods。
[root@vmsvr2 ~]# su - purchase_sc
Last login: Wed Dec 8 17:10:01 CST 2021 on pts/0
[purchase_sc@vmsvr2 ~]$ source profile.gbaseserver
[purchase_sc@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> select * from t_goods;
f_goodsid 2
f_goodsname 新冠疫苗（国药）
f_price_in 199.00
f_price_out
f_qty 500000.00
f_operator manager
f_flag 7FFFFFFFFFFFFFFE00000000000000000000000000000000
f_goodsid 5
f_goodsname 福临门花生油
f_price_in 58.00
f_price_out
f_qty 10000.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 8
f_goodsname 故宫门票
f_price_in 50.00
f_price_out
f_qty 1000.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 11
f_goodsname 上海迪士尼门票
f_price_in 888.00
f_price_out
f_qty 2000.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 12
f_goodsname 广汽凯美瑞
f_price_in 228888.00
f_price_out
f_qty 500.00
f_operator purchase_sc
f_flag BFFFFFFFFFFFFFFD7FFFFFFFFFFFFFFE0000000000000008
f_goodsid 13
f_goodsname 广州腊肠
f_price_in 28.80
f_price_out
f_qty 300.00
f_operator purchase_sc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000008
f_goodsid 14
f_goodsname 桂林门票
f_price_in 288.00
f_price_out
f_qty 300.00
f_operator purchase_sc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
7 row(s) retrieved.
>对于华南区的采购，他们只能查看标记为本区域的记录或未标记区域的记录。因此purchase_sc可以查看自己录入的3条记录，其它区域采购人员录入的门票类（无区域标记）共计2条商品，manager录入的2条无区域标记商品，合计7条商品。
华南区采购，不能查看其它区域采购录入的4条商品，不能查看manager录入1条华东区商品和1条华北区商品，不能查看1条OEM类商品。
查询数据（sale_nc）
select * from t_goods;
select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods;使用sale_nc用户，查询商品表t_goods。
[root@vmsvr2 ~]# su - sale_nc
[sale_nc@vmsvr2 ~]$ source profile.gbaseserver
[sale_nc@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> select * from t_goods;
8245: User cannot perform READ access to the protected column (f_price_in).
Error in line 1
Near character position 20区域的销售，无权查看商品的进价。
> info columns for t_goods;
Column name Type Nulls
f_goodsid serial no
f_goodsname varchar(50,0) yes
f_price_in decimal(18,2) yes
f_price_out decimal(18,2) yes
f_qty decimal(18,2) yes
f_operator varchar(20,0) yes
f_flag idssecuritylabel yes
> select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods;
f_goodsid 2
f_goodsname 新冠疫苗（国药）
f_price_out
f_qty 500000.00
f_operator manager
f_flag 7FFFFFFFFFFFFFFE00000000000000000000000000000000
f_goodsid 3
f_goodsname Oracle 12C
f_price_out
f_qty 5.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000002
f_goodsid 5
f_goodsname 福临门花生油
f_price_out
f_qty 10000.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 7
f_goodsname 北京烤鸭
f_price_out
f_qty 100.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000002
f_goodsid 8
f_goodsname 故宫门票
f_price_out
f_qty 1000.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 11
f_goodsname 上海迪士尼门票
f_price_out
f_qty 2000.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 14
f_goodsname 桂林门票
f_price_out
f_qty 300.00
f_operator purchase_sc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
7 row(s) retrieved.
>对于华北区的销售，他们只能查看标记为本区域的记录或未标记区域的记录。因此sale_nc可以查看purchase_nc录入的非秘密类的2条记录，其它区域采购人员录入的门票类（无区域标记）共计2条商品，manager录入的2条无区域标记商品，1条标记为华北区的未分类商品，合计7条商品。
华北区销售，不能查看本区域的秘密类1条商品，不能查看其它区域采购录入的4条商品，不能查看manager录入1条华东区商品，不能查看1条OEM类商品。
查询数据（sale_ec）
select * from t_goods;
select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods;使用sale_ec用户，查询商品表t_goods。
[root@vmsvr2 ~]# su - sale_ec
[sale_ec@vmsvr2 ~]$ source profile.gbaseserver
[sale_ec@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> select * from t_goods;
8245: User cannot perform READ access to the protected column (f_price_in).
Error in line 1
Near character position 20
> select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods;
f_goodsid 2
f_goodsname 新冠疫苗（国药）
f_price_out
f_qty 500000.00
f_operator manager
f_flag 7FFFFFFFFFFFFFFE00000000000000000000000000000000
f_goodsid 4
f_goodsname SQL Server 2017
f_price_out
f_qty 10.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000004
f_goodsid 5
f_goodsname 福临门花生油
f_price_out
f_qty 10000.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 8
f_goodsname 故宫门票
f_price_out
f_qty 1000.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 10
f_goodsname 杭州小笼包
f_price_out
f_qty 200.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000004
f_goodsid 11
f_goodsname 上海迪士尼门票
f_price_out
f_qty 2000.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 14
f_goodsname 桂林门票
f_price_out
f_qty 300.00
f_operator purchase_sc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
7 row(s) retrieved.
>对于华东区的销售，他们只能查看标记为本区域的记录或未标记区域的记录。因此sale_ec可以查看purchase_ec录入的非秘密类的2条记录，其它区域采购人员录入的门票类（无区域标记）共计2条商品，manager录入的2条无区域标记商品，1条标记为华东区的未分类商品，合计7条商品。
华东区销售，不能查看本区域的秘密类1条商品，不能查看其它区域采购录入的4条商品，不能查看manager录入1条华北区商品，不能查看1条OEM类商品。
查询数据（sale_sc）
select * from t_goods;
select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods;使用sale_ec用户，查询商品表t_goods。
[root@vmsvr2 ~]# su - sale_sc
[sale_sc@vmsvr2 ~]$ source profile.gbaseserver
[sale_sc@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> select * from t_goods;
8245: User cannot perform READ access to the protected column (f_price_in).
Error in line 1
Near character position 20
> select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods;
f_goodsid 2
f_goodsname 新冠疫苗（国药）
f_price_out
f_qty 500000.00
f_operator manager
f_flag 7FFFFFFFFFFFFFFE00000000000000000000000000000000
f_goodsid 5
f_goodsname 福临门花生油
f_price_out
f_qty 10000.00
f_operator manager
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 8
f_goodsname 故宫门票
f_price_out
f_qty 1000.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 11
f_goodsname 上海迪士尼门票
f_price_out
f_qty 2000.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
f_goodsid 13
f_goodsname 广州腊肠
f_price_out
f_qty 300.00
f_operator purchase_sc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000008
f_goodsid 14
f_goodsname 桂林门票
f_price_out
f_qty 300.00
f_operator purchase_sc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
6 row(s) retrieved.
>对于华南区的销售，他们只能查看标记为本区域的记录或未标记区域的记录。因此sale_sc可以查看purchase_sc录入的非秘密类的2条记录，其它区域采购人员录入的门票类（无区域标记）共计2条商品，manager录入的2条无区域标记商品，合计6条商品。
华南区销售，不能查看本区域的秘密类1条商品，不能查看其它区域采购录入的4条商品，不能查看manager录入1条华北区商品和1条华东区商品，不能查看1条OEM类商品。
查询数据（gbasedbt）
select * from t_goods;[root@vmsvr2 ~]# su - gbasedbt
Last login: Wed Dec 8 16:08:45 CST 2021 on pts/0
[gbasedbt@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> select * from t_goods;
8245: User cannot perform READ access to the protected column (f_goodsid).
Error in line 1
Near character position 20
>结论：gbasedbt做为数据库管理员，由于没有安全管理员对其授权，无法访问带有安全功能的表中的数据。
查询数据（dbsso）
select * from t_goods;[root@vmsvr2 ~]# su - dbsso
Last login: Wed Dec 8 16:09:46 CST 2021 on pts/0
[dbsso@vmsvr2 ~]$ source profile.gbaseserver
[dbsso@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> select * from t_goods;
8245: User cannot perform READ access to the protected column (f_goodsid).
Error in line 1
Near character position 20
>是不是安全管理员为自己授予安全标签，就可以访问表中的数据呢？
> grant security label goods.manager to user dbsso for all access;
8218: A DBSECADM cannot grant a privilege, security label or exemption to self.
Error in line 1
Near character position 62
>
Database closed.事实上，安全管理员不能为自己授予任何安全标签，来访问安全表中的数据。
结论：安全管理员无法为自己授予安全标签，无法访问安全表中的数据。
查询数据（dbaao）
select * from t_goods;[root@vmsvr2 ~]# su - dbaao
Last login: Wed Dec 8 14:53:46 CST 2021 on pts/0
[dbaao@vmsvr2 ~]$ source profile.gbaseserver
[dbaao@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
387: No connect permission.
111: ISAM error: no record found.
[dbaao@vmsvr2 ~]$结论：审计管理员，没有数据库管理员对其授权，无法连接到数据库，无法访问表中的数据。
更新数据（sale_nc）
select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods where f_goodsid = 8;
update t_goods set f_price_out = 60 where f_goodsid = 8;操作演示如下：
[root@vmsvr2 ~]# su - sale_nc
Last login: Thu Dec 9 08:16:18 CST 2021 on pts/2
[sale_nc@vmsvr2 ~]$ source profile.gbaseserver
[sale_nc@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods where f_goodsid = 8;
f_goodsid 8
f_goodsname 故宫门票
f_price_out
f_qty 1000.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
1 row(s) retrieved.
> update t_goods set f_price_out = 60 where f_goodsid = 8;
1 row(s) updated.
> select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods where f_goodsid = 8;
f_goodsid 8
f_goodsname 故宫门票
f_price_out 60.00
f_qty 1000.00
f_operator purchase_nc
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
1 row(s) retrieved.
> update t_goods set f_price_in = 50 where f_goodsid = 8;
8245: User cannot perform READ access to the protected column (f_price_in).
Error in line 1
Near character position 53
>华北区的销售sale_nc可以更新商品的售价，不可以更新商品的进价。
结论：在开启三权分立功能后，可以实现不同用户对安全表的不同列，具有不同的读、写权限。
更新数据（sale_ec）
select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods where f_goodsid = 11;
update t_goods set f_price_out = 999.00 where f_goodsid = 11;操作演示如下：
[root@vmsvr2 ~]# su - sale_ec
Last login: Wed Dec 8 18:36:46 CST 2021 on pts/0
[sale_ec@vmsvr2 ~]$ source profile.gbaseserver
[sale_ec@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods where f_goodsid = 11;
f_goodsid 11
f_goodsname 上海迪士尼门票
f_price_out
f_qty 2000.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
1 row(s) retrieved.
> update t_goods set f_price_out = 999.00 where f_goodsid = 11;
1 row(s) updated.
> select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods where f_goodsid = 11;
f_goodsid 11
f_goodsname 上海迪士尼门票
f_price_out 999.00
f_qty 2000.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
1 row(s) retrieved.
> update t_goods set f_price_in = 999.00 where f_goodsid = 11;
8245: User cannot perform READ access to the protected column (f_price_in).
Error in line 1
Near character position 59
>华东区的销售sale_ec可以更新商品的售价，不可以更新商品的进价。
结论：在开启三权分立功能后，可以实现不同用户对安全表的不同列，具有不同的读、写权限。
更新数据（sale_sc）
select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods where f_goodsid = 11;
update t_goods set f_price_out = 1010.00 where f_goodsid = 11;操作演示如下：
[root@vmsvr2 ~]# su - sale_sc
Last login: Wed Dec 8 18:48:35 CST 2021 on pts/0
[sale_sc@vmsvr2 ~]$ source profile.gbaseserver
[sale_sc@vmsvr2 ~]$ dbaccess mydb -
Your evaluation license will expire on 2022-12-07 00:00:00
Database selected.
> select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods where f_goodsid = 11;
f_goodsid 11
f_goodsname 上海迪士尼门票
f_price_out 999.00
f_qty 2000.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
1 row(s) retrieved.
> update t_goods set f_price_out = 1010.00 where f_goodsid = 11;
1 row(s) updated.
> select f_goodsid,f_goodsname,f_price_out,f_qty,f_operator,f_flag from t_goods where f_goodsid = 11;
f_goodsid 11
f_goodsname 上海迪士尼门票
f_price_out 1010.00
f_qty 2000.00
f_operator purchase_ec
f_flag 3FFFFFFFFFFFFFFF00000000000000000000000000000000
1 row(s) retrieved.
> update t_goods set f_price_in = 999.00 where f_goodsid = 11;
8245: User cannot perform READ access to the protected column (f_price_in).
Error in line 1
Near character position 60
>对于没有打区域标记的数据，不同区域的人员，都具有访问权限。但对于列，仍然有访问控制限制。
结论：在开启三权分立功能后，可以实现不同用户对安全表的不同列，具有不同的读、写权限。用户可以通过标签，灵活控制不同用户对行的访问权限。
小结
通过以上用例，总结如下：
- GBase 8s可以在安装时，通过角色分离，部署三权分立功能。
- 在开启三权分立功能后，数据库管理员，安全管理员，审计管理员在权限上彼此分离，相互牵制。
- 在没有安全管理员的授权下，即使是数据库管理员，也无法访问业务表中的数据，保证数据安全。
- 即使是安全管理员，也无法为自己授权，来访问业务表中的数据，保证了业务数据安全。
- 在没有数据库授权下，审计管理员无法连接到数据库，无法访问表中的业务数据，保证了数据安全。
- 数据库管理员和安全管理员，均无权查看审计日志。
- 安全管理员做为系统安全的主要负责人，由其制订审计内容，保证安全审计的合理性。
- 对于普通的业务人员，可单独为其制订科学合理的安全标签，对数据的访问权限在行，列上进行严格管控，保证数据的操作安全。
附录
常用审计事件
审计代码 | 审计事件 |
ADCK | 添加 Chunk 文件 |
ADLG | 添加事务日志 |
ALIX | 修改索引 |
ALLC | 修改安全标签组件 |
ALSQ | 修改序列 |
ALTB | 修改表 |
ALUR | 修改用户 |
CRAM | 创建审计掩码 |
CRBS | 创建存储空间 |
CRDB | 创建数据库 |
CRDS | 创建数据库空间 |
CRIX | 创建索引 |
CRLB | 创建安全标签 |
CRLC | 创建安全标签组件 |
CRPL | 创建安全策略 |
CRPT | 解密失败或重试 |
CRRL | 创建角色 |
CRSN | 创建同义词 |
CRSP | 创建存储过程 |
CRSQ | 创建序列 |
CRTB | 创建表 |
CRTR | 创建触发器 |
CRUR | 创建用户 |
CRVW | 创建视图 |
DLRW | 删除行 |
DNDM | 禁用磁盘镜像 |
DRAG | 删除聚合 |
DRAM | 删除审计掩码 |
DRBS | 删除存储空间 |
DRCK | 删除 Chunk 文件 |
DRDB | 删除数据库 |
DRDS | 删除数据库空间 |
DRIX | 删除索引 |
DRLB | 删除安全标签 |
DRLC | 删除安全标签组件 |
DRLG | 删除事务日志 |
DRPL | 删除安全策略 |
DRRL | 删除角色 |
DRSN | 删除同义词 |
DRSP | 删除存储过程 |
DRSQ | 删除序列 |
DRTB | 删除表 |
DRTR | 删除触发器 |
DRUR | 删除用户 |
DRVW | 删除视图 |
EXSP | 执行存储过程 |
GRDB | 授权数据库访问 |
GRLB | 授权安全标签 |
GRRL | 授权角色 |
GRSA | 授权 DBSECADM |
GRTB | 授权表访问 |
GRXM | 授权豁免 |
INRW | 插入行 |
LGDB | 修改数据库日志模式 |
LKTB | 锁表 |
LSAM | 查看审计掩码 |
LSDB | 查看数据库 |
MDLG | 修改事务日志 |
ONAU | onaudit |
ONBR | onbar |
ONCH | oncheck |
ONIN | oninit |
ONLG | onlog |
ONLO | onload |
ONMN | onmonitor |
ONMO | onmode |
ONPA | onparams |
ONPL | onpload |
ONSP | onspaces |
ONST | onstat |
ONTP | ontape |
ONUL | onunload |
OPDB | 打开数据库 |
OPST | 优化存储 |
PWUR | 设置用户密码 |
RBSV | 回滚到检查点 |
RDRW | 读取行数据 |
RLSV | 释放检查点 |
RNUR | 用户更名 |
RNDB | 数据库更名 |
RNDS | 数据库空间更名 |
RNIX | 索引更名 |
RNLB | 安全标签更名 |
RNLC | 安全标签组件更名 |
RNPL | 安全策略更名 |
RNSQ | 序列更名 |
RVDB | 回收数据库访问权限 |
RVDR | 回收默认角色 |
RVLB | 回收安全标签权限 |
RVSA | 回收DBSECADM权限 |
RVTB | 回收表访问权限 |
RVXM | 回收豁免权 |
STIL | 设置数据库隔离等级 |
TCTB | Truncate 表 |
ULTB | 解锁表 |
UPAM | 更新审计掩码 |
UPDM | 启动磁盘镜像 |
UPRW | 更新当前行 |
操作系统用户管理
操作系统用户清单
[root@vmsvr2 ~]# cat user.lst
gbasedbt
dbsso
dbaao
manager
finance
store
purchase_nc
purchase_ec
purchase_sc
sale_nc
sale_ec
sale_sc批量创建用户，设置用户密码
[root@vmsvr2 ~]# cat ua.sh
#!/bin/bash
while read line
do
useradd $line
echo "111111" | passwd --stdin $line
done < user.lst批量删除用户
[root@vmsvr2 ~]# cat ud.sh
#!/bin/bash
while read line
do
userdel -r $line
done < user.lst
[root@vmsvr2 ~]#
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
