KingabseES例程-函数和过程的 INVOKER 与 DEFINER

调用者权利和定义者权利子句

指定子程序的权利属性。权利属性影响单元在运行时，执行的SQL语句的名称解析和权限检查。

PG模式：

SECURITY INVOKER
SECURITY DEFINER

Oracle模式：

AUTHID CURRENT_USER 
AUTHID DEFINER

一、测试KingbaseES 的PG 模式

数据准备

  • 创建用户：2个普通用户，1个superuser用户

    create user u01 password '123456';
create schema authorization u01;
create user u02 password '123456';
create schema authorization u02;
create user u03 password '123456' superuser ;
create schema authorization u03;

  • 每个用户创建同名表，插入数据：用户名

    create table u01.t_user as select 'U01' as name ;
create table u02.t_user as select 'U02' as name ;
create table u03.t_user as select 'U03' as name ;
alter table u01.t_user owner to u01;
alter table u02.t_user owner to u02;
alter table u03.t_user owner to u03;

  • 用户U01创建函数，分别使用不同的权利属性。

    默认权利模式是定义者-INVOKER。

    函数中，T_USER表名不含SCHEMA。

    
CREATE OR REPLACE function u01.TEST_default()
    returns text
    language plpgsql
AS
$$
declare
    v1 text;
BEGIN
    select name into v1 from t_user;
    return v1;
END ;
$$;


CREATE OR REPLACE function u01.TEST_definer()
    returns text
    language plpgsql
    SECURITY DEFINER
AS
$$
declare
    v1 text;
BEGIN
    select name into v1 from t_user;
    return v1;
END ;
$$;

CREATE OR REPLACE function u01.TEST_INVOKER()
    returns text
    language plpgsql
    SECURITY INVOKER
AS
$$
declare
    v1 text;
BEGIN
    select name into v1 from t_user;
    return v1;
END ;
$$;

select proname
     , case when 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
select user || ' DEFAULT     : ' || U01.TEST_default() as name
union all
select user || ' DEFINER     : ' || U01.TEST_definer()
union all
select user || ' 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
drop table u03.t_user;
select user || ' DEFAULT     : ' || U01.TEST_default() as name
union all
select user || ' DEFINER     : ' || U01.TEST_definer()
union all
select user || ' CURRENT_USER: ' || U01.TEST_invoker();
EOF

DROP TABLE
错误:  关系 "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
select user || ' DEFINER     : ' || U01.TEST_definer() ;
EOF

       ?column?
-----------------------
 u03 DEFINER     : U01
(1 行记录)

使用u02 普通用户测试

  • u02拥有同名表

    USAGE ON SCHEMA u01，需要赋权给普通用户。

    
$ ksql -U u02 -d kingbase <<EOF
select user || ' DEFAULT     : ' || U01.TEST_default() as name
union all
select user || ' DEFINER     : ' || U01.TEST_definer()
union all
select user || ' CURRENT_USER: ' || U01.TEST_invoker();
EOF
         name
-----------------------
 u02 DEFAULT     : U02
 u02 DEFINER     : U01
 u02 CURRENT_USER: U02
(3 rows)

  • u02没有同名表

    U01.TEST_default 和 U01.TEST_invoker 函数报错，不能在用户u02找到 t_user 表。

    $ ksql -U u02 -d kingbase <<EOF
drop table u02.t_user;
select user || ' DEFAULT     : ' || U01.TEST_default() as name
union all
select user || ' DEFINER     : ' || U01.TEST_definer()
union all
select user || ' CURRENT_USER: ' || U01.TEST_invoker();
EOF

DROP TABLE
错误:  关系 "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
select user || ' DEFINER     : ' || U01.TEST_definer() ;
EOF

       ?column?
-----------------------
 u02 DEFINER     : U01
(1 row)

二、测试KingbaseES的Oracle模式

数据准备

  • 创建用户：2个普通用户，1个superuser用户

    create user u01 password '123456';
create schema authorization u01;
create user u02 password '123456';
create schema authorization u02;
create user u03 password '123456' superuser ;
create schema authorization u03;

  • 每个用户创建同名表，插入数据：用户名

    create table u01.t_user as select 'U01' as name ;
create table u02.t_user as select 'U02' as name ;
create table u03.t_user as select 'U03' as name ;
alter table u01.t_user owner to u01;
alter table u02.t_user owner to u02;
alter table u03.t_user owner to u03;

  • 用户U01创建函数，分别使用不同的AUTHID属性。

    默认权利模式是定义者-INVOKER。

    函数中，T_USER表名不含SCHEMA。

    $ ksql -U u01 -d kingbase <<EOF
\set SQLTERM /
CREATE OR REPLACE function U01.TEST_default
    return varchar2
AS
    v1 varchar2(10);
BEGIN
    select name into v1 from t_user;
    return v1;
END ;
/

CREATE OR REPLACE function U01.TEST_invoker
    return varchar2
    AUTHID CURRENT_USER
AS
    v1 varchar2(10);
BEGIN
    select name into v1 from t_user;
    return v1;
END ;
/

CREATE OR REPLACE function U01.TEST_definer
    return varchar2
    AUTHID definer
AS
    v1 varchar2(10);
BEGIN
    select name into v1 from t_user;
    return v1;
END ;
/
EOF

CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION

select proname
     , case when 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
(3 rows)

使用u03(superuser)用户测试

  • u03 用户拥有同名表

    $ ksql -U u03 -d kingbase <<EOF
select user || ' DEFAULT     : ' || U01.TEST_default() as name
union all
select user || ' DEFINER     : ' || U01.TEST_definer()
union all
select user || ' INVOKER     : ' || U01.test_invoker();
EOF
         name
-----------------------
 u03 DEFAULT     : U01
 u03 DEFINER     : U01
 u03 INVOKER     : U01
(3 rows)

  • u03 用户没有同名表

    $ ksql -U u03 -d kingbase <<EOF
drop table u03.t_user;
select user || ' DEFAULT     : ' || U01.TEST_default() as name
union all
select user || ' DEFINER     : ' || U01.TEST_definer()
union all
select user || ' INVOKER     : ' || U01.test_invoker();
EOF
DROP TABLE
         name
-----------------------
 u03 DEFAULT     : U01
 u03 DEFINER     : U01
 u03 INVOKER     : U01
(3 rows)

使用普通用户测试

  • 普通用户拥有同名表

    SELECT ON u01.t_user，需要赋权给普通用户。

    USAGE ON SCHEMA u01，需要赋权给普通用户。

      
$ ksql -U u02 -d kingbase <<EOF
select user || ' DEFAULT     : ' || U01.TEST_default() as name
union all
select user || ' DEFINER     : ' || U01.TEST_definer()
union all
select user || ' INVOKER     : ' || U01.test_invoker();
EOF
         name
-----------------------
 u02 DEFAULT     : U01
 u02 DEFINER     : U01
 u02 INVOKER     : U01
(3 rows)

  • 普通用户没有同名表

    $ ksql -U u02 -d kingbase <<EOF
drop table u02.t_user;
select user || ' DEFAULT     : ' || U01.TEST_default() as name
union all
select user || ' DEFINER     : ' || U01.TEST_definer()
union all
select user || ' INVOKER     : ' || U01.test_invoker();
EOF
         name
-----------------------
 u02 DEFAULT     : U01
 u02 DEFINER     : U01
 u02 INVOKER     : U01
(3 rows)

三、测试Oracle规则

数据准备

  • 创建用户：2个普通用户，1个DBA用户

    create user u01 identified by 123456;
grant connect,resource,unlimited tablespace to u01;
create user u02 identified by 123456;
grant connect,resource,unlimited tablespace to u02;
create user u03 identified by 123456;
grant dba to u03;

  • 每个用户创建同名表，插入数据：用户名

    create table u01.t_user as select 'U01' as name from dual;
create table u02.t_user as select 'U02' as name from dual;
create table u03.t_user as select 'U03' as name from dual;

  • 用户U01创建函数，分别使用不同的AUTHID属性。

    默认权利模式是定义者-DEFINER。

    函数中，T_USER表名不含SCHEMA。

    CREATE OR REPLACE function U01.TEST_default
    return varchar2
AS
    v1 varchar2(10);
BEGIN
    select name into v1 from t_user;
    return v1;
END ;
/

CREATE OR REPLACE function U01.TEST_CURRENT_USER
    return varchar2
    AUTHID CURRENT_USER
AS
    v1 varchar2(10);
BEGIN
    select name into v1 from t_user;
    return v1;
END ;
/

CREATE OR REPLACE function U01.TEST_definer
    return varchar2
    AUTHID definer
AS
    v1 varchar2(10);
BEGIN
    select 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
select user||' DEFAULT     : '||U01.TEST_default() as name from dual  union all
select user||' DEFINER     : '||U01.TEST_definer() from dual  union all
select user||' 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
drop table u03.t_user purge;
select user||' DEFAULT     : '||U01.TEST_default() as name from dual  union all
select user||' DEFINER     : '||U01.TEST_definer() from dual  union all
select user||' CURRENT_USER: '||U01.TEST_CURRENT_USER() from dual;
EOF

Table dropped.

ERROR:
ORA-00942: table or view does not exist
ORA-06512: at "U01.TEST_CURRENT_USER", line 7


$ sqlplus -S u03/123456 <<EOF
select user||' DEFAULT     : '||U01.TEST_default() as name from dual  union all
select user||' DEFINER     : '||U01.TEST_definer() from dual ;
EOF


NAME
---------------------
U03 DEFAULT     : U01
U03 DEFINER     : U01

使用普通用户测试

  • 普通用户拥有同名表

    需要赋权给普通用户。

    $ sqlplus -S u03/123456 <<EOF
select user||' DEFAULT     : '||U01.TEST_default() as name from dual  union all
select user||' DEFINER     : '||U01.TEST_definer() from dual  union all
select user||' CURRENT_USER: '||U01.TEST_CURRENT_USER() from dual;
EOF
ERROR:
ORA-00942: table or view does not exist
ORA-06512: at "U01.TEST_CURRENT_USER", line 7

no rows selected


$ sqlplus -S u01/123456 <<EOF
grant execute  on  u01.TEST_DEFAULT to u02;
grant execute  on  u01.TEST_DEFINER to u02;
grant execute  on  u01.TEST_CURRENT_USER to u02;
EOF

Grant succeeded.

Grant succeeded.

Grant succeeded.


$ sqlplus -S u02/123456 <<EOF
drop table u02.t_user purge;
select user||' DEFAULT     : '||U01.TEST_default() as name from dual  union all
select user||' DEFINER     : '||U01.TEST_definer() from dual  union all
select user||' 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
select user||' DEFAULT     : '||U01.TEST_default() as name from dual  union all
select user||' DEFINER     : '||U01.TEST_definer() from dual  union all
select user||' CURRENT_USER: '||U01.TEST_CURRENT_USER() from dual;
EOF

Table dropped.

ERROR:
ORA-00942: table or view does not exist
ORA-06512: at "U01.TEST_CURRENT_USER", line 7

no rows selected

$ sqlplus -S u02/123456 <<EOF
select user||' DEFAULT     : '||U01.TEST_default() as name from dual  union all
select user||' 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 根据调用者解析表名 根据定义者解析表名 根据调用者解析表名
posted @   KINGBASE研究院  阅读(59)  评论(0)  编辑  收藏  举报
相关博文：
· KingbaseES角色和权限介绍
· KingbaseES 的角色和权限管理
· 39、安全_1（权限、用户profile文件、密码文件、VPD对行级访问的控制）
· 数据库注入提权总结（四）
· Postgresql数据库系统表和系统视图以及show命令
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！
历史上的今天：
2021-12-13 alter role 导致的数据库无法登录问题
点击右上角即可分享
微信分享提示