GBase 8C审计脚本

GBase 8C审计脚本

–安全培训用例
–密码错误一定次数
show failed_login_attempts;
–帐户被锁定时间超过设定值
show password_lock_time;
—手动锁定账号
ALTER USER testuser ACCOUNT LOCK;
–手动解锁账号
ALTER USER test ACCOUNT UNLOCK;
–删除不用的账号
DROP USER test_user  CASCADE;

–设置账户有效期
alter user test_user VALID BEGIN ‘2024-04-24 08:00:00’ VALID UNTIL ‘2024-04-25 14:23:00′;

–密码加密策略
show password_encryption_type; —0，1，2，3（md5/sha256/sm3）

—查看所有的账户策略信息
select* from pg_authid where rolname =’test_user’;

—密码的复杂度
show password_policy; —-（1、 0）1为校验密码复杂度
–示例
create user u1 identified by ‘111111’;
create user u1 identified by ‘123456qw’;

—密码重用（多少次、多少天内修改的密码不能重复）
show password_reuse_time;
show password_reuse_max;
–密码的有效期限
show password_effect_time;  —密码有效期
show password_notify_time;  —密码到期天数提醒
–设置密码失效
create user u1 password ‘Gbase_123’ expired;
alter user u1 password ‘Gbase_123’ expired;

–用户权限
grant \ revoke
create database test_1;
create user test1 identified by ‘Gbase_123′;
–dba        权限
grant ALL privileges to test1;
–回收DBA权限
revoke ALL privileges from test1;
–回收publibc权限连接权限，授予链接权限
revoke connect on database test_1 from public;
grant CONNECT ON DATABASE test_1 to test1;
gsql -d test_1 -U test1 -h 192.168.4.71 -p 5432 -W Gbase_123 -r
–授权后可以可以登陆成功
revoke connect on database test_1 from test1;
gsql -d test1 -U test1 -h 192.168.4.71 -p 5432 -W Gbase_123 -r
–select/insert/update/delete表权限
create table t3(id int,name varchar(10));  –使用拥有权限的用户创建表
–登录新用户进行查询等操作
select * from t3;
insert into t3 values(1,’a’);
update t3 set name=’update’ where id =1;
delete from t3 where id =1;
–进行授权
grant select ON table t3 to test1;
grant insert ON table t3 to test1;
grant update ON table t3 to test1;
grant delete ON table t3 to test1;
–进行查询
select * from t3;
insert into t3 values(1,’a’);
update t3 set name=’update’ where id =1;
delete from t3 where id =1;
–权限回收
revoke select ON table t3 from test1;
revoke insert ON table t3 from test1;
revoke update ON table t3 from test1;
revoke delete ON table t3 from test1;

–审计
–1打开审计日志开关
alter system set audit_enabled=on;
–2查看审计日志目录
show  audit_directory;
–查看审计日志
select detail_info,type,result from pg_query_audit(sysdate-1/3600,sysdate);
–查看审计中的锁表记录
select detail_info,type,result from pg_query_audit(sysdate-1/3600,sysdate) where type =’lock_user’;
–使用审计对象进行查看
select detail_info,type,result from pg_query_audit(sysdate-1/3600,sysdate) where object_name=’test1′;
–使用审计查看数据库删除的操作
select detail_info,type,result from pg_query_audit(sysdate-1/3600,sysdate) where type =’ddl_database’;

–查看审计内容
show audit_system_object;
alter system set audit_system_object=127;
–打开DML日志
alter system set audit_dml_state=1;
create table test3(a int);
alter table test3 add (name varchar(50));

select detail_info,type,result from pg_query_audit(sysdate-1/3600,sysdate) where object_name=’test3′;
–打开DQL日志
alter system set audit_dml_state_select=1;
–查看登录失败的审计记录
select detail_info,type,result from pg_query_audit(sysdate-1,sysdate)
where type=’login_failed’;
–查看登陆成功的审计记录
select detail_info,type,result from pg_query_audit(sysdate-1,sysdate)
where type=’login_success’;
–审计删除
–1、审计文件占用的磁盘空间或者审计文件的个数超过指定的最大值时，系统将删除最早的审计文件，并记录审计文件删除信息到审计日志中
SHOW audit_space_limit;  –自动删除
–2、手动删除
SELECT pg_delete_audit(‘2024-04-24 00:00:00′,’2024-04-24 23:59:59’);