db2中有些函数名子比较奇观,是 '=', '<', '>' 等。这些函数由于同名的函数不止一个,可能会导致权限回收的时候产生一些问题,比如:
db2tst@NODE01:~> db2 "select varchar(FUNCSCHEMA,10) as FUNCSCHEMA, varchar(FUNCNAME,10) as FUNCNAME, varchar(SPECIFICNAME,30) as SPECIFICNAME from syscat.functions where funcname='=' and FUNCSCHEMA='SYSPROC' "
FUNCSCHEMA FUNCNAME SPECIFICNAME
---------- ---------- ------------------------------
SYSPROC = SQL200703111424938
SYSPROC = SQL200703111424947
2 record(s) selected.
db2tst@NODE01:~> db2 "select * from sysibmadm.privileges where objectname in ('SQL200703111424947') and AUTHID='PUBLIC'"
AUTHID AUTHIDTYPE PRIVILEGE GRANTABLE OBJECTNAME OBJECTSCHEMA OBJECTTYPE
----------- ---------- ----------- --------- -------------------- ------------------ ------------------------
PUBLIC G EXECUTE N SQL200703111424947 SYSPROC FUNCTION
2 record(s) selected.
问题是,如何从PUBLIC撤销这个函数的执行权限?
直接 REVOKE EXECUTE ON FUNCTION SYSPROC.'=' FROM PUBLIC 是不行的,会报错SQL0476N,因为函数名有重复,只是参数不同,所以得指定参数格式。但是我们又不知道如何指定
db2tst@NODE01:~> db2 "REVOKE EXECUTE ON FUNCTION SYSPROC.\"=\" FROM PUBLIC"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0476N Reference to routine "SYSPROC.=" was made without a signature, but
the routine is not unique in its schema. SQLSTATE=42725
这时候可以使用 revoke SPECIFIC FUNCTION,使用它对应的 SPECIFICNAME 来撤销:
db2tst@NODE01:~> db2 -v "revoke execute on SPECIFIC FUNCTION SYSPROC.SQL200703111424947 from PUBLIC restrict"
revoke execute on SPECIFIC FUNCTION SYSPROC.SQL200703111424947 from PUBLIC restrict
DB20000I The SQL command completed successfully.