informix11.5只读用户测试_DBA_Regan_新浪博客

只读用户信息

regan:/home/informix>id inforread

uid=1003(inforread) gid=1024(inforread) groups=1024(inforread)

环境变量同informix，dbaccess权限为755，这样是为了只读用户也能执行dbaccess命令

创建数据库

regan:/home/informix>dbaccess sysmaster -

> create database regan;  

regan:/home/informix>dbaccess regan -

Database selected.

创建测试表

>  create table test ( a char(1));   

Table created.

新建的只读用户登录

regan:/home/inforread>dbaccess regan -

  387: No connect permission.

  111: ISAM error:  no record found.

授予只读用户connect权限，未授予表权限。

> grant connect to inforread;

Permission granted.

> insert into test values ('a');

1 row(s) inserted.

只读用户尝试操作表：

> insert into test values ('a');

1 row(s) inserted.

> 

> select * from test;

a 

a

a

2 row(s) retrieved.

> delete from test;

2 row(s) deleted.

> 

----这里看到只读用户却能进行增删改操作。查看网上其他资料说connect权限拥有了增删改查权限。如果是这样的话，就无法实现只读了。然后查找了其他资料，尝试其他操作。

回收所有权限后，只授予select权限：

> revoke all on regan:test from inforread;

Permission revoked.

> grant select on regan:test to inforread;

Permission granted.

结果仍然是只读用户拥有所有权限。

然后我查看了这个库上的权限

regan:/home/inforread>dbschema -d regan -p all

DBSCHEMA Schema Utility       INFORMIX-SQL Version 11.50.FC9

grant dba to "informix";

grant connect to "inforread";

grant select on "informix".test to "inforread" as "informix";

grant select on "informix".test to "public" as "informix";

grant update on "informix".test to "public" as "informix";

grant insert on "informix".test to "public" as "informix";

grant delete on "informix".test to "public" as "informix";

grant index on "informix".test to "public" as "informix";

revoke usage on language SPL from public ;

grant usage on language SPL to public ;

所以这里看出来其实是建表时自动授予了public这个用户所有权限，，不知道为什么。

不过既然知道原因了，那就好办了：

> revoke all on regan:test from public;

Permission revoked.

regan:/home/inforread>dbschema -d regan -p all

DBSCHEMA Schema Utility       INFORMIX-SQL Version 11.50.FC9

grant dba to "informix";

grant connect to "inforread";

grant select on "informix".test to "inforread" as "informix";

revoke usage on language SPL from public ;

grant usage on language SPL to public ;

只读用户操作：

> select * from test;

a 

a

a

2 row(s) retrieved.

> insert into test values ('a');

  275: The Insert privilege is required for this operation.

Error in line 1

Near character position 18

> delete from test;

  274: No DELETE permission for test.

Error in line 1

Near character position 16

> update test set a='b';

  273: No UPDATE permission for test.

Error in line 1

Near character position 13

> 

这样，inforread就只对这个表只有读的权限了。

小结下就是

create 表后要从public回收权限，，（其实最好就是哪里能配置默认不授予public权限）

revoke all on regan:表名 from public;