调用者权利和定义者权利子句
指定子程序的权利属性。权利属性影响单元在运行时,执行的SQL语句的名称解析和权限检查。
PG模式:
SECURITY INVOKER
SECURITY DEFINER
Oracle模式:
AUTHID CURRENT_USER
AUTHID DEFINER
一、测试KingbaseES 的PG 模式
数据准备
创建用户:2个普通用户,1个superuser用户
createuser u01 password '123456';
create schema authorization u01;
createuser u02 password '123456';
create schema authorization u02;
createuser u03 password '123456' superuser ;
create schema authorization u03;
每个用户创建同名表,插入数据:用户名
createtable u01.t_user asselect'U01'as name ;
createtable u02.t_user asselect'U02'as name ;
createtable u03.t_user asselect'U03'as name ;
altertable u01.t_user owner to u01;
altertable u02.t_user owner to u02;
altertable u03.t_user owner to u03;
用户U01创建函数,分别使用不同的权利属性。
默认权利模式是定义者-INVOKER。
函数中,T_USER表名不含SCHEMA。
CREATEOR REPLACE function u01.TEST_default()
returns text
language plpgsql
AS
$$
declare
v1 text;
BEGINselect name into v1 from t_user;
return v1;
END ;
$$;
CREATEOR REPLACE function u01.TEST_definer()
returns text
language plpgsql
SECURITY DEFINER
AS
$$
declare
v1 text;
BEGINselect name into v1 from t_user;
return v1;
END ;
$$;
CREATEOR REPLACE function u01.TEST_INVOKER()
returns text
language plpgsql
SECURITY INVOKER
AS
$$
declare
v1 text;
BEGINselect name into v1 from t_user;
return v1;
END ;
$$;
select proname
, casewhen prosecdef then'DEFINER'else'INVOKER'end prosecdef
, (select lanname from pg_language where oid = prolang)
from pg_proc
where pronamespace ='u01'::regnamespace;
proname | prosecdef | lanname
--------------+-----------+---------
test_default | INVOKER | plpgsql
test_definer | DEFINER | plpgsql
test_invoker | INVOKER | plpgsql
使用U03 (superuser)用户测试
u03用户拥有同名表
$ ksql -U u03 -d kingbase <<EOF
selectuser||' DEFAULT : '|| U01.TEST_default() as name
unionallselectuser||' DEFINER : '|| U01.TEST_definer()
unionallselectuser||' CURRENT_USER: '|| U01.TEST_invoker();
EOF
name
-----------------------
u03 DEFAULT : U03
u03 DEFINER : U01
u03 CURRENT_USER: U03
(3 行记录)
u03用户没有有同名表
U01.TEST_default 和 U01.TEST_invoker 函数报错,不能在用户u03找到 t_user 表。
$ ksql -U u03 -d kingbase <<EOF
droptable u03.t_user;
selectuser||' DEFAULT : '|| U01.TEST_default() as name
unionallselectuser||' DEFINER : '|| U01.TEST_definer()
unionallselectuser||' CURRENT_USER: '|| U01.TEST_invoker();
EOF
DROPTABLE
错误: 关系 "t_user" 不存在
第1行select name from t_user
^
查询: select name from t_user
背景: 在SQL语句的第5行的PL/pgSQL函数u01.test_default()
$ ksql -U u01 -d kingbase <<EOF
selectuser||' DEFINER : '|| U01.TEST_definer() ;
EOF
?column?
-----------------------
u03 DEFINER : U01
(1 行记录)
使用u02 普通用户测试
u02拥有同名表
USAGE ON SCHEMA u01,需要赋权给普通用户。
$ ksql -U u02 -d kingbase <<EOF
selectuser||' DEFAULT : '|| U01.TEST_default() as name
unionallselectuser||' DEFINER : '|| U01.TEST_definer()
unionallselectuser||' CURRENT_USER: '|| U01.TEST_invoker();
EOF
name
-----------------------
u02 DEFAULT : U02
u02 DEFINER : U01
u02 CURRENT_USER: U02
(3rows)
u02没有同名表
U01.TEST_default 和 U01.TEST_invoker 函数报错,不能在用户u02找到 t_user 表。
$ ksql -U u02 -d kingbase <<EOF
droptable u02.t_user;
selectuser||' DEFAULT : '|| U01.TEST_default() as name
unionallselectuser||' DEFINER : '|| U01.TEST_definer()
unionallselectuser||' CURRENT_USER: '|| U01.TEST_invoker();
EOF
DROPTABLE
错误: 关系 "t_user" 不存在
LINE 1: select name from t_user
^
QUERY: select name from t_user
CONTEXT: 在SQL语句的第5行的PL/pgSQL函数u01.test_default()
$ ksql -U u02 -d kingbase <<EOF
selectuser||' DEFINER : '|| U01.TEST_definer() ;
EOF
?column?
-----------------------
u02 DEFINER : U01
(1row)
二、测试KingbaseES的Oracle模式
数据准备
创建用户:2个普通用户,1个superuser用户
createuser u01 password '123456';
create schema authorization u01;
createuser u02 password '123456';
create schema authorization u02;
createuser u03 password '123456' superuser ;
create schema authorization u03;
每个用户创建同名表,插入数据:用户名
createtable u01.t_user asselect'U01'as name ;
createtable u02.t_user asselect'U02'as name ;
createtable u03.t_user asselect'U03'as name ;
altertable u01.t_user owner to u01;
altertable u02.t_user owner to u02;
altertable u03.t_user owner to u03;
用户U01创建函数,分别使用不同的AUTHID属性。
默认权利模式是定义者-INVOKER。
函数中,T_USER表名不含SCHEMA。
$ ksql -U u01 -d kingbase <<EOF
\set SQLTERM /CREATEOR REPLACE function U01.TEST_default
return varchar2
AS
v1 varchar2(10);
BEGINselect name into v1 from t_user;
return v1;
END ;
/CREATEOR REPLACE function U01.TEST_invoker
return varchar2
AUTHID CURRENT_USERAS
v1 varchar2(10);
BEGINselect name into v1 from t_user;
return v1;
END ;
/CREATEOR REPLACE function U01.TEST_definer
return varchar2
AUTHID definer
AS
v1 varchar2(10);
BEGINselect name into v1 from t_user;
return v1;
END ;
/
EOF
CREATEFUNCTIONCREATEFUNCTIONCREATEFUNCTIONselect proname
, casewhen prosecdef then'DEFINER'else'INVOKER'end prosecdef
, (select lanname from pg_language where oid = prolang)
from pg_proc
where pronamespace ='u01'::regnamespace;
proname | prosecdef | lanname
-------------------+-----------+---------
test_default | INVOKER | plsql
test_invoker | INVOKER | plsql
test_definer | DEFINER | plsql
(3rows)
使用u03(superuser)用户测试
u03 用户拥有同名表
$ ksql -U u03 -d kingbase <<EOF
selectuser||' DEFAULT : '|| U01.TEST_default() as name
unionallselectuser||' DEFINER : '|| U01.TEST_definer()
unionallselectuser||' INVOKER : '|| U01.test_invoker();
EOF
name
-----------------------
u03 DEFAULT : U01
u03 DEFINER : U01
u03 INVOKER : U01
(3rows)
u03 用户没有同名表
$ ksql -U u03 -d kingbase <<EOF
droptable u03.t_user;
selectuser||' DEFAULT : '|| U01.TEST_default() as name
unionallselectuser||' DEFINER : '|| U01.TEST_definer()
unionallselectuser||' INVOKER : '|| U01.test_invoker();
EOF
DROPTABLE
name
-----------------------
u03 DEFAULT : U01
u03 DEFINER : U01
u03 INVOKER : U01
(3rows)
使用普通用户测试
普通用户拥有同名表
SELECT ON u01.t_user,需要赋权给普通用户。
USAGE ON SCHEMA u01,需要赋权给普通用户。
$ ksql -U u02 -d kingbase <<EOF
selectuser||' DEFAULT : '|| U01.TEST_default() as name
unionallselectuser||' DEFINER : '|| U01.TEST_definer()
unionallselectuser||' INVOKER : '|| U01.test_invoker();
EOF
name
-----------------------
u02 DEFAULT : U01
u02 DEFINER : U01
u02 INVOKER : U01
(3rows)
普通用户没有同名表
$ ksql -U u02 -d kingbase <<EOF
droptable u02.t_user;
selectuser||' DEFAULT : '|| U01.TEST_default() as name
unionallselectuser||' DEFINER : '|| U01.TEST_definer()
unionallselectuser||' INVOKER : '|| U01.test_invoker();
EOF
name
-----------------------
u02 DEFAULT : U01
u02 DEFINER : U01
u02 INVOKER : U01
(3rows)
三、测试Oracle规则
数据准备
创建用户:2个普通用户,1个DBA用户
createuser u01 identified by123456;
grantconnect,resource,unlimited tablespace to u01;
createuser u02 identified by123456;
grantconnect,resource,unlimited tablespace to u02;
createuser u03 identified by123456;
grant dba to u03;
每个用户创建同名表,插入数据:用户名
createtable u01.t_user asselect'U01'as name from dual;
createtable u02.t_user asselect'U02'as name from dual;
createtable u03.t_user asselect'U03'as name from dual;
用户U01创建函数,分别使用不同的AUTHID属性。
默认权利模式是定义者-DEFINER。
函数中,T_USER表名不含SCHEMA。
CREATEOR REPLACE function U01.TEST_default
return varchar2
AS
v1 varchar2(10);
BEGINselect name into v1 from t_user;
return v1;
END ;
/CREATEOR REPLACE function U01.TEST_CURRENT_USER
return varchar2
AUTHID CURRENT_USERAS
v1 varchar2(10);
BEGINselect name into v1 from t_user;
return v1;
END ;
/CREATEOR REPLACE function U01.TEST_definer
return varchar2
AUTHID definer
AS
v1 varchar2(10);
BEGINselect name into v1 from t_user;
return v1;
END ;
/select OBJECT_NAME,AUTHID from SYS.DBA_PROCEDURES
where OWNER='U01';
OBJECT_NAME AUTHID
--------------- ---------
TEST_DEFAULT DEFINER
TEST_CURRENT_USER CURRENT_USER
TEST_DEFINER DEFINER
使用DBA用户测试
DBA用户拥有同名表
$ sqlplus -S u03/123456<<EOF
selectuser||' DEFAULT : '||U01.TEST_default() as name from dual unionallselectuser||' DEFINER : '||U01.TEST_definer() from dual unionallselectuser||' CURRENT_USER: '||U01.TEST_CURRENT_USER() from dual;
EOF
NAME
---------------------
U03 DEFAULT : U01
U03 DEFINER : U01
U03 CURRENT_USER: U03
DBA用户没有同名表
CURRENT_USER:由于调用者用户没有同名表,所以报错:ORA-00942: table or view does not exist 。
$ sqlplus -S u03/123456<<EOF
droptable u03.t_user purge;
selectuser||' DEFAULT : '||U01.TEST_default() as name from dual unionallselectuser||' DEFINER : '||U01.TEST_definer() from dual unionallselectuser||' CURRENT_USER: '||U01.TEST_CURRENT_USER() from dual;
EOF
Table dropped.
ERROR:
ORA-00942: tableorview does not exist
ORA-06512: at "U01.TEST_CURRENT_USER", line 7
$ sqlplus -S u03/123456<<EOF
selectuser||' DEFAULT : '||U01.TEST_default() as name from dual unionallselectuser||' DEFINER : '||U01.TEST_definer() from dual ;
EOF
NAME
---------------------
U03 DEFAULT : U01
U03 DEFINER : U01
使用普通用户测试
普通用户拥有同名表
需要赋权给普通用户。
$ sqlplus -S u03/123456<<EOF
selectuser||' DEFAULT : '||U01.TEST_default() as name from dual unionallselectuser||' DEFINER : '||U01.TEST_definer() from dual unionallselectuser||' CURRENT_USER: '||U01.TEST_CURRENT_USER() from dual;
EOF
ERROR:
ORA-00942: tableorview does not exist
ORA-06512: at "U01.TEST_CURRENT_USER", line 7norows selected
$ sqlplus -S u01/123456<<EOF
grantexecuteon u01.TEST_DEFAULT to u02;
grantexecuteon u01.TEST_DEFINER to u02;
grantexecuteon u01.TEST_CURRENT_USER to u02;
EOF
Grant succeeded.
Grant succeeded.
Grant succeeded.
$ sqlplus -S u02/123456<<EOF
droptable u02.t_user purge;
selectuser||' DEFAULT : '||U01.TEST_default() as name from dual unionallselectuser||' DEFINER : '||U01.TEST_definer() from dual unionallselectuser||' CURRENT_USER: '||U01.TEST_CURRENT_USER() from dual;
EOF
NAME
---------------------
U02 DEFAULT : U01
U02 DEFINER : U01
U02 CURRENT_USER: U02
普通用户没有同名表
$ sqlplus -S u02/123456<<EOF
selectuser||' DEFAULT : '||U01.TEST_default() as name from dual unionallselectuser||' DEFINER : '||U01.TEST_definer() from dual unionallselectuser||' CURRENT_USER: '||U01.TEST_CURRENT_USER() from dual;
EOF
Table dropped.
ERROR:
ORA-00942: tableorview does not exist
ORA-06512: at "U01.TEST_CURRENT_USER", line 7norows selected
$ sqlplus -S u02/123456<<EOF
selectuser||' DEFAULT : '||U01.TEST_default() as name from dual unionallselectuser||' DEFINER : '||U01.TEST_definer() from dual ;
EOF
NAME
---------------------
U02 DEFAULT : U01
U02 DEFINER : U01
四、总结
程序权利属性列表
PG模式 | Oracle模式 | Oracle规则 | |
权利属性子句 | SECURITY INVOKER|DEFINER | AUTHID CURRENT_USER|DEFINER | AUTHID CURRENT_USER|DEFINER |
默认属性 | INVOKER | INVOKER | DEFINER |
权限需求 | USAGE ON SCHEMA | USAGE ON SCHEMA & SELECT ON table_name | EXECUTE ON function_name |
DEFINER | 根据定义者解析表名 | 根据定义者解析表名 | 根据定义者解析表名 |
INVOKER | 根据调用者解析表名 | 根据定义者解析表名 | 根据调用者解析表名 |