HGDB创建只读用户

文章目录文档用途详细信息文档用途本文档用于指导如何在企业版和安全版创建只读用户。详细信息一、企业版创建只读用户1、以超级用户highgo登录数据库创建用户highgo# create user read_only with password read;CREATEROLE2、设置为只读的transactionhighgo# alter user read_only set default_transaction_read_only on;ALTERROLE3、访问非 public 模式中的表默认在highgo数据库的public模式下的对象是可以访问的,如果要访问别的schema的表则需要两步(1)首先要有使用schema的权限highgo# grant usage on schema test_schema to read_only ;GRANT(2)然后加所有表的只读权限highgo# grant select on all tables in schema test_schema to read_only;GRANT(3)如果不想给所有表的查询权限则单独给某个表的查询权限highgo# grant select on TABLE test_schema.abc to read_only;GRANT4、可以进行一下权限测试[highgolocalhost~]$ psql-U read_only highgo highgoselect*fromtest_schema.abclimit1;a|b-------------------------------------1|db18340e7e9a86ea85a64addd9ea309f(1row)highgoinsertintotest_schema.abcvalues(10,10);ERROR:25006: cannotexecuteINSERTinaread-onlytransactionhighgodeletefromtest_schema.abcwherea1;ERROR:25006: cannotexecuteDELETEinaread-onlytransactionhighgoupdatetest_schema.abcsetbxxwherea1;ERROR:25006: cannotexecuteUPDATEinaread-onlytransaction5、如果要在别的数据库访问(1)先要用highgo超级用户登录然后\c到对应的数据库highgo# \c testPSQL:Release5.6.4Connectedto:HighGoDatabaseV5.6Enterprise EditionRelease5.6.4-64-bitProduction You are now connectedtodatabasetestasuserhighgo.(2)执行下面的命令将对应的schema的表查询权限给这个用户test数据库的public模式的usage权限是默认就有的只需要添加表的只读权限即可test# grant select on all tables in schema public to read_only;GRANT6、授予对只读用户的默认访问权限对于对应模式 schema 下后期新创建的表也生效。默认在对应模式下新创建的数据表,只读用户是没有访问权限的,需要手工赋权; 可以修改默认权限让只读用户直接拥有新表的访问权限 这样即使是该模式中新加的表read_only用户都有只读权限test# alter default privileges in schema public grant select on tables to read_only;ALTERDEFAULTPRIVILEGES二、安全版开启三权创建只读用户1、查看安全配置参数hg_sepofpowerson,已开启三权分立highgoselectshow_secure_param();show_secure_param-----------------------------------------hg_sepofpowerson,hg_macontrolmin,hg_rowsecureoff,hg_showlogininfoon,hg_clientnoinput30min(s),hg_idcheck.enableon,hg_idcheck.pwdlock5time(s),hg_idcheck.pwdlocktime24hour(s),hg_idcheck.pwdvaliduntil7day(s),hg_idcheck.pwdpolicyhigh,hg_sepv4v4,(1row)2、登录sysdba管理员用户创建只读用户highgo# create user read_only_secure with password readsecure123;CREATEROLE3、设置为只读的transactionhighgo# alter user read_only_secure set default_transaction_read_only on;ALTERROLE4、把test_schema_secure模式usage权限赋给只读用户read_only_securehighgo# grant usage on schema test_schema_secure to read_only_secure ;GRANT5、如果表的属主是sysdba需要先更改表的属主为普通用户再赋只读权限给其他用户(1)把test_schema_secure模式下的表只读权限赋给只读用户read_only_secure报错highgo# grant select on all tables in schema test_schema_secure to read_only_secure;ERROR: Cant grant it to other role. highgo# grant select on TABLE test_schema_secure.abc to read_only_secure; ERROR: Cantgrantittoother role.这个时候会发现不管只把某一张表或者整个模式下表只读权限赋给只读用户read_only_secure都会提示Can’t grant it to other role.(2)把表的属主更给为普通用户read_securehighgo# grant usage on schema test_schema_secure to read_secure ;GRANThighgo# alter table test_schema_secure.abc owner to read_secure;ALTERTABLEhighgo\dttest_schema_secure.abc ListofrelationsSchema|Name|Type|Owner|Size|Description------------------------------------------------------------------test_schema_secure|abc|table|read_secure|16kB|(1row)(3)表数量过多可以通过直接生成sql语句更改表属主highgo# SELECThighgo-# format(ALTER TABLE %I.%I OWNER TO read_secure;, schemaname, tablename) AS alter_sqlhighgo-# FROM pg_tableshighgo-# WHERE schemaname test_schema_secure;alter_sql----------------------------------------------------------ALTERTABLEtest_schema_secure.abc OWNERTOread_secure;ALTERTABLEtest_schema_secure.a01 OWNERTOread_secure;ALTERTABLEtest_schema_secure.a02 OWNERTOread_secure;ALTERTABLEtest_schema_secure.abs OWNERTOread_secure;(4rows)(4)登录普通用户read_securegrant select权限给只读用户read_only_secure[rootslave~]# psql highgo read_securehighgograntselectonTABLEtest_schema_secure.abctoread_only_secure;GRANThighgo# \dp test_schema_secure.abcAccessprivilegesSchema|Name|Type|Accessprivileges|Columnprivileges|Policies-----------------------------------------------------------------------------------------test_schema_secure|abc|table|sysdbaarwdDxt/sysdba|||||read_only_securer/sysdba||(1row)(5)登录普通用户测试[rootslave~]# psql highgo read_only_securehighgoselect*fromtest_schema_secure.abc;a|b-------------------------------------1|db18340e7e9a86ea85a64addd9ea309f(1row)(6)设置test_schema_secure模式新增表用户read_only_secure也有只读权限highgo# alter default privileges in schema test_schema_secure grant select on tables to read_only_secure;ALTERDEFAULTPRIVILEGES(7)新增表测试highgo# create table test_schema_secure.a02(a int,b text);CREATETABLEhighgo# \dp test_schema_secure.a02AccessprivilegesSchema|Name|Type|Accessprivileges|Columnprivileges|Policies-----------------------------------------------------------------------------------------test_schema_secure|a02|table|sysdbaarwdDxt/sysdba|||||read_only_securer/sysdba||(1row)注意1、sysdba是数据库管理员不建议使用sysdba管理业务表建议新建业务账号并更改表的属主为新的业务账号避免影响测评。2、表的属主为普通用户无需更改表的属主可以直接grant select权限给对应用户。3、如果表的属主为sysdba用户需要把所有表的只读权限赋给只读用户不允许关库可以(3)生成对应sql允许关库可以采用第三步操作关闭三权赋权之后再开启三权即可。4、安全版和企业版一样默认普通用户有数据库的public模式的usage权限。三、安全版关闭三权创建只读用户1、关闭三权并重启数据库生效[rootslave~]# psql highgo sysssopsql(4.5.7)Typehelpforhelp.highgoselectset_secure_param(hg_sepofpowers,off);set_secure_param---------------------------------setconfiguration successfully.(1row)highgo\q[rootslave~]#[rootslave~]# pg_ctl stopwaitingforservertoshut down....done server stopped[rootslave~]# pg_ctl startwaitingforservertostart....2025-07-2815:52:06.526CST[17087]LOG: Password detection moduleisdisabled2025-07-2815:52:06.529CST[17087]LOG:startingHighGo Security Enterprise EditionDatabaseSystem4.5.7onCentOS7 x86_64,buildon202108042025-07-2815:52:06.531CST[17087]LOG: listeningonIPv4 address0.0.0.0,port58662025-07-2815:52:06.531CST[17087]LOG: listeningonIPv6 address::,port58662025-07-2815:52:06.533CST[17087]LOG: listeningonUnix socket/tmp/.s.PGSQL.58662025-07-2815:52:06.588CST[17088]LOG:databasesystem was shut down at2025-07-2815:52:00CST2025-07-2815:52:06.590CST[17088]LOG: Switchover the SSHA Role.CurrentisNONE2025-07-2815:52:06.598CST[17087]LOG:databasesystemisreadytoaccept connections done server started2、查看安全配置参数hg_sepofpowersoff,已关闭三权分立highgoselectshow_secure_param();show_secure_param-----------------------------------------hg_sepofpowersoff,hg_macontrolmin,hg_rowsecureoff,hg_showlogininfoon,hg_clientnoinput30min(s),hg_idcheck.enableoff,hg_idcheck.pwdlock5time(s),hg_idcheck.pwdlocktime24hour(s),hg_idcheck.pwdvaliduntil7day(s),hg_idcheck.pwdpolicyhigh,hg_sepv4off,(1row)3、单独将表abs的只读权限赋给用户read_only_securehighgo# grant select on TABLE test_schema_secure.abs to read_only_secure;GRANThighgo# \dp test_schema_secure.absAccessprivilegesSchema|Name|Type|Accessprivileges|Columnprivileges|Policies-----------------------------------------------------------------------------------------test_schema_secure|abs|table|sysdbaarwdDxt/sysdba|||||read_only_securer/sysdba||(1row)4、将所有表只读权限赋给用户read_only_securehighgo# grant select on all tables in schema test_schema_secure to read_only_secure;GRANT5、查看相关权限highgo# \dt test_schema_secure.*ListofrelationsSchema|Name|Type|Owner|Size|Description------------------------------------------------------------------test_schema_secure|a01|table|sysdba|8192bytes|test_schema_secure|abc|table|sysdba|16kB|test_schema_secure|abs|table|sysdba|16kB|(3rows)highgo# \dp test_schema_secure.a01AccessprivilegesSchema|Name|Type|Accessprivileges|Columnprivileges|Policies-----------------------------------------------------------------------------------------test_schema_secure|a01|table|sysdbaarwdDxt/sysdba|||||read_only_securer/sysdba||(1row)highgo# \dp test_schema_secure.abcAccessprivilegesSchema|Name|Type|Accessprivileges|Columnprivileges|Policies-----------------------------------------------------------------------------------------test_schema_secure|abc|table|sysdbaarwdDxt/sysdba|||||read_only_securer/sysdba||(1row)highgo# \dp test_schema_secure.absAccessprivilegesSchema|Name|Type|Accessprivileges|Columnprivileges|Policies-----------------------------------------------------------------------------------------test_schema_secure|abs|table|sysdbaarwdDxt/sysdba|||||read_only_securer/sysdba||(1row)6、设置test_schema_secure模式新增表用户read_only_secure也有只读权限highgo# alter default privileges in schema test_schema_secure grant select on tables to read_only_secure;ALTERDEFAULTPRIVILEGES