HAS_TRIGGER_PRIVILEGE

说明

判断指定用户在指定触发器上是否具有权限

语法

has_trigger_privilege ::=

has_trigger_privilege ::=
    HAS_TRIGGER_PRIVILEGE ( user_id , table_oid , trigger_owner_oid , schema_id )

参数

user_id

OID类型，被查询用户的用户ID。

table_oid

OID类型，被查询触发器的相关表ID。

trigger_owner_oid

OID类型，被查询触发器的属主ID。

schema_id

OID类型，被查询用户的模式ID。

返回值

BOOL 表示是否有权限

示例

-- 准备环境
CONNECT SYSDBA/szoscar55;
DROP FUNCTION FUNC1;
DROP USER U1;
DROP TABLE T1;

CREATE TABLE T1(a INT);
CREATE TRIGGER TRIG1 BEFORE INSERT ON T1 FOR EACH ROW
BEGIN
    INSERT INTO T1 VALUES(:NEW.A);
END;
/
CREATE USER U1 PASSWORD 'szoscar55';

-- 授权
GRANT SELECT ON T1 TO U1;

-- 查询触发器是否有权限
CREATE OR REPLACE FUNCTION FUNC1(user_name TEXT, table_name TEXT, trig_name TEXT) RETURN BOOL AS
DECLARE 
	schema_id INT;
	user_id  INT;
	table_id INT;
	trig_owner_id INT;
BEGIN      
	SELECT oid INTO table_id FROM sys_class WHERE relname = table_name;
	SELECT relnamespace INTO schema_id FROM sys_class WHERE relname = table_name;
	SELECT usesysid INTO user_id FROM v_sys_user WHERE usename = user_name;
	SELECT tgowner INTO trig_owner_id FROM sys_trigger WHERE tgname = trig_name;
	  
	RETURN HAS_TRIGGER_PRIVILEGE(user_id,  table_id, trig_owner_id, schema_id);	  
END;

/
SELECT FUNC1('U1', 'T1', 'TRIG1');
FUNC1(boolean)      |
--------------------
true                |
总数目：1

DROP TABLE T1;
DROP USER U1;
DROP FUNCTION FUNC1;