oracle授权消失,pg数据库授权表给只读用户之后,权限慢慢消失

越来越多的互联网企业在使用postgresql数据库,我们也不例外。

昨天开发请我建立了一个只读用户abc_tmp_test用户,并且将mkl_rw用户下的32个表授权给只读用户用。ok,请简单轻松的一个需求,很快就完成了。

但是今天开发来和我说,昨天授权的几个表中,有部分表还是没有权限去读取,让我帮忙看看。一开始,我以为是昨天遗漏了,先道了一个歉,再次进行了授权,授权完成之后,检查了32个表,都能被只读用户查询,于是放心的告诉开发,昨天的所有表都已经授权好了,我也检查过一次了。这次肯定不会漏了。

万万没想到,半小时后,开发来和我说,不行,还是有其中几个表没有权限。我之前的连接还没断开,再次跑了一遍之前的检查语句,确实没有权限了。卧槽?这是咋回事?数据库中有雷锋了?

我再次授权了一次,并且检查了information_schema.table_privileges,确认了再次授权后,是新增了32行记录。这次我没有先通知开发,说已经授权完成了,而是过了一会,我再次去查,变成了28行,又过了一会,变成了16行!

也就是我授权的32个表的select权限给只读用户,过一段时间之后,这32个表中的一些表的权限会慢慢消失!而且消失权限的表,也没有发现先授权的先消失,后授权的后消息的规律,但是可以发现最终剩下的,就是那16个表。我开始怀疑起人生了……

难道是pg中授权的表的数量有限?不能超过16个?也没查到相关的参数啊。

难道是那16个表有什么特殊设置?从建表语句中也没看到啊。

难道授权之后需要checkpoint刷盘?测试了checkpoint还是一样丢权限。

难道真的有雷锋出现啊。还说什么pg和oracle一样牛,一样稳定,连基本的授权都会丢。

正在逐个检查参数之际,同事通过检查log,发现了drop table的语句……

原来如此,这个案例,可以用下面的测试过程模拟出来了:

-bash-4.2$ psql -d mybidbaa -U mkl_rw

psql (9.6.2)

Type "help" for help.

mybidbaa=> --创建表bbbdba_test,并且授权给abc_tmp_test用户

mybidbaa=> create table bbbdba_test as select now();

SELECT 1

mybidbaa=>

mybidbaa=>

mybidbaa=> grant select on mkl_rw.bbbdba_test to abc_tmp_test;

GRANT

mybidbaa=>

mybidbaa=>

mybidbaa=>

mybidbaa=>

mybidbaa=> \q

-bash-4.2$ psql -d mybidbaa -U abc_tmp_test

psql (9.6.2)

Type "help" for help.

mybidbaa=>

mybidbaa=> --用abc_tmp_test登录,可以查到bbbdba_test表。

mybidbaa=> select * from mkl_rw.bbbdba_test;

now

-------------------------------

2017-11-16 16:08:14.123217+08

(1 row)

mybidbaa=> \q

-bash-4.2$

-bash-4.2$

-bash-4.2$ psql -d mybidbaa -U mkl_rw

psql (9.6.2)

Type "help" for help.

mybidbaa=> --删除表bbbdba_test,然后重建表bbbdba_test。

mybidbaa=> drop table mkl_rw.bbbdba_test;

DROP TABLE

mybidbaa=>

mybidbaa=>

mybidbaa=> create table bbbdba_test as select now();

SELECT 1

mybidbaa=> \q

-bash-4.2$

-bash-4.2$

-bash-4.2$

-bash-4.2$ psql -d mybidbaa -U abc_tmp_test

psql (9.6.2)

Type "help" for help.

mybidbaa=> --可以看到,权限丢了!!

mybidbaa=> select * from mkl_rw.bbbdba_test;

ERROR: permission denied for relation bbbdba_test

mybidbaa=> \q

-bash-4.2$

-bash-4.2$

-bash-4.2$

-bash-4.2$

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

-bash-4.2$psql-dmybidbaa-Umkl_rw

psql(9.6.2)

Type"help"forhelp.

mybidbaa=>--创建表bbbdba_test,并且授权给abc_tmp_test用户

mybidbaa=>createtablebbbdba_testasselectnow();

SELECT1

mybidbaa=>

mybidbaa=>

mybidbaa=>grantselectonmkl_rw.bbbdba_testtoabc_tmp_test;

GRANT

mybidbaa=>

mybidbaa=>

mybidbaa=>

mybidbaa=>

mybidbaa=>\q

-bash-4.2$psql-dmybidbaa-Uabc_tmp_test

psql(9.6.2)

Type"help"forhelp.

mybidbaa=>

mybidbaa=>--用abc_tmp_test登录,可以查到bbbdba_test表。

mybidbaa=>select *frommkl_rw.bbbdba_test;

now

-------------------------------

2017-11-1616:08:14.123217+08

(1row)

mybidbaa=>\q

-bash-4.2$

-bash-4.2$

-bash-4.2$psql-dmybidbaa-Umkl_rw

psql(9.6.2)

Type"help"forhelp.

mybidbaa=>--删除表bbbdba_test,然后重建表bbbdba_test。

mybidbaa=>droptablemkl_rw.bbbdba_test;

DROPTABLE

mybidbaa=>

mybidbaa=>

mybidbaa=>createtablebbbdba_testasselectnow();

SELECT1

mybidbaa=>\q

-bash-4.2$

-bash-4.2$

-bash-4.2$

-bash-4.2$psql-dmybidbaa-Uabc_tmp_test

psql(9.6.2)

Type"help"forhelp.

mybidbaa=>--可以看到,权限丢了!!

mybidbaa=>select *frommkl_rw.bbbdba_test;

ERROR:permissiondeniedforrelationbbbdba_test

mybidbaa=>\q

-bash-4.2$

-bash-4.2$

-bash-4.2$

-bash-4.2$

是的,如果table被drop了之后,再次重建,此时原本授权给只读用户的权限,也会消失。

向开发确认,是否有drop之后重建表的操作,开发确认,有段程序确实会定期的逐个drop表后重建表!!

为什么要进行drop表之后重建表的操作?开发说是通过调用框架清理数据,框架就是这么干的。

ok,明白了目的是为了清理数据,而不涉及到表结构的修改,那么其实用truncate来清理就可以了。如下测试,权限不会丢。

-bash-4.2$

-bash-4.2$

-bash-4.2$ psql -d mybidbaa -U mkl_rw

psql (9.6.2)

Type "help" for help.

mybidbaa=> grant select on mkl_rw.bbbdba_test to abc_tmp_test;

GRANT

mybidbaa=> truncate table mkl_rw.bbbdba_test;

TRUNCATE TABLE

mybidbaa=> \q

-bash-4.2$ psql -d mybidbaa -U abc_tmp_test

psql (9.6.2)

Type "help" for help.

mybidbaa=> select * from mkl_rw.bbbdba_test;

now

-----

(0 rows)

mybidbaa=> \q

-bash-4.2$

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

-bash-4.2$

-bash-4.2$

-bash-4.2$psql-dmybidbaa-Umkl_rw

psql(9.6.2)

Type"help"forhelp.

mybidbaa=>grantselectonmkl_rw.bbbdba_testtoabc_tmp_test;

GRANT

mybidbaa=>truncatetablemkl_rw.bbbdba_test;

TRUNCATETABLE

mybidbaa=>\q

-bash-4.2$psql-dmybidbaa-Uabc_tmp_test

psql(9.6.2)

Type"help"forhelp.

mybidbaa=>select *frommkl_rw.bbbdba_test;

now

-----

(0rows)

mybidbaa=>\q

-bash-4.2$

最终,开发修改了代码,再次授权那32张表之后,权限不再慢慢消失了。

这个故事中可以学到的教训有二:

1. 大千世界无奇不有,数据库中没有雷锋,而是有各种万万没想到的逻辑。

2. 幸亏我们在建库的时候,建库标准要求设置了log_statement=ddl, 才能在log中发现线索。(其实我们oracle和pg的建库标准,都设置了记录ddl)

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值