GBase 8s V8.8 运维管理：认识一个环境变量NODEFDAC

在数据库运维管理中权限控制是保障数据安全的重要环节。今天我们来介绍GBase 8sgbase database中一个实用的环境变量——NODEFDAC它可以帮助我们精细控制新建表的默认访问权限。在非ANSI兼容的数据库中通过设置NODEFDAC环境变量为yes数据库服务器会在创建新表时不授予PUBLIC默认的表访问权限。export NODEFDACyes关键注意事项- yes 设置区分大小写并且对前导和尾随空格敏感- 设置中包含大写字母或空格相当于未设置NODEFDAC- 当NODEFDAC未设置或设置为除 yes 之外的任何值在非ANSI兼容的数据库中创建表默认会向PUBLIC授予默认权限- 在ANSI兼容的数据库中启用NODEFDAC没有任何效果实际演示场景一默认行为未设置NODEFDAC-- gbasedbt用户建库建表 create database testdac with log; Database created. create table t1(id int); Table created. -- 查看缺省情况下PUBLIC对新建表的默认访问权限 -- insert, delete, update, select, index select * from systabauth where tabid100; grantor root grantee public tabid 100 tabauth su-idx---- 1 row(s) retrieved.创建普通用户并赋予库级connect权限 create user tmp_u_001 with password GBase_123; User created. grant connect to tmp_u_001; Permission granted.tmp_u_001用户连接后自动获得对t1表的PUBLIC权限可以正常操作 connect to testdacol_tl3302_zm0519 user tmp_u_001; ENTER PASSWORD: Disconnected. Connected. select * from t1; id No rows found. insert into t1 values(1); 1 row(s) inserted. update t1 set id2; 1 row(s) updated.场景二设置NODEFDACyes后# 设置环境变量无需重启数据库 export NODEFDACyes database testdac; Database selected. create table t2(id int); Table created. -- 查看权限t2表没有缺省权限了 select * from systabauth where tabid99; grantor root grantee public tabid 100 -- 这里的100是上面的t1表 tabauth su-idx---- 1 row(s) retrieved.tmp_u_001用户对t2表无任何权限操作被拒绝 connect to testdacol_tl3302_zm0519 user tmp_u_001; ENTER PASSWORD: Disconnected. Connected. select * from t2; 272: No SELECT permission for t2. Error in line 1 Near character position 16 insert into t2 values(1); 275: The Insert privilege is required for this operation. Error in line 1 Near character position 16 delete from t2; 274: No DELETE permission for t2. Error in line 1 Near character position 14 update t2 set id1; 273: No UPDATE permission for t2. Error in line 1 Near character position 11NODEFDAC环境变量是GBase 8s中一个简单但实用的权限控制开关。对于需要严格数据访问控制的场景建议在数据库服务器环境中配置 export NODEFDACyes 避免新建表自动向PUBLIC开放权限从而提升数据库的安全性。运维人员可根据实际业务需求灵活选用。欢迎访问南大通用技术社区获取更多GBase 8s技术文章与最佳实践