# 创建业务角色
CREATE ROLE ORDER_READONLY;
CREATE ROLE ORDER_OPERATOR;
CREATE ROLE ORDER_ADMIN;
CREATE ROLE REPORT_USER;
CREATE ROLE DEVELOPER;

# 创建部门角色
CREATE ROLE DEPT_SALES;
CREATE ROLE DEPT_FINANCE;
CREATE ROLE DEPT_HR;

# 创建功能角色
CREATE ROLE DATA_IMPORT;
CREATE ROLE DATA_EXPORT;
CREATE ROLE DATA_ANALYST;

# 查看所有角色
SELECT
ROLENAME,
ROLEID,
CREATE_TIME
FROM SYSCAT.ROLES
ORDER BY ROLENAME;

# 删除角色
DROP ROLE DEVELOPER;

# 角色继承关系
# 基础角色
GRANT SELECT ON TABLE ORDERS TO ORDER_READONLY;
GRANT SELECT ON TABLE CUSTOMER TO ORDER_READONLY;

# 继承基础角色
GRANT ORDER_READONLY TO ORDER_OPERATOR;
GRANT INSERT, UPDATE ON TABLE ORDERS TO ORDER_OPERATOR;

# 管理角色继承
GRANT ORDER_OPERATOR TO ORDER_ADMIN;
GRANT DELETE ON TABLE ORDERS TO ORDER_ADMIN;
GRANT CONTROL ON TABLE ORDERS TO ORDER_ADMIN;

# 查看角色继承关系
SELECT
ROLENAME,
GRANTEETYPE,
GRANTEE
FROM SYSCAT.ROLEAUTH
WHERE GRANTEETYPE = ‘R’
ORDER BY ROLENAME;

# 查看角色权限
SELECT
ROLENAME,
OBJECTTYPE,
OBJECTNAME,
PRIVILEGE
FROM SYSCAT.ROLEOBJECTAUTH
WHERE ROLENAME = ‘ORDER_OPERATOR’;

# 授予表权限
GRANT SELECT ON TABLE ORDERS TO ORDER_READONLY;
GRANT SELECT, INSERT, UPDATE ON TABLE ORDERS TO ORDER_OPERATOR;
GRANT ALL ON TABLE ORDERS TO ORDER_ADMIN;

# 授予列级权限
GRANT SELECT (ORDER_ID, CUSTOMER_ID, TOTAL_AMOUNT)
ON TABLE ORDERS TO ORDER_READONLY;

# 授予视图权限
GRANT SELECT ON VIEW V_ORDER_SUMMARY TO REPORT_USER;

# 授予存储过程执行权限
GRANT EXECUTE ON PROCEDURE SP_PROCESS_ORDER TO ORDER_OPERATOR;

# 授予包执行权限
GRANT EXECUTE ON PACKAGE PKG_ORDER_PROCESS TO ORDER_OPERATOR;

# 查看表权限
SELECT
GRANTEE,
GRANTEETYPE,
TABSCHEMA,
TABNAME,
SELECTAUTH,
INSERTAUTH,
UPDATEAUTH,
DELETEAUTH,
CONTROLAUTH
FROM SYSCAT.TABAUTH
WHERE TABNAME = ‘ORDERS’
ORDER BY GRANTEE;

# 授予数据库权限
GRANT DBADM ON DATABASE TO db_admin_user;
GRANT SECADM ON DATABASE TO security_admin;
GRANT ACCESSCTRL ON DATABASE TO access_admin;
GRANT DATAACCESS ON DATABASE TO data_admin;

# 授予创建权限
GRANT CREATETAB ON DATABASE TO developer_user;
GRANT CREATEIN ON SCHEMA FGEDB TO developer_user;

# 授予模式权限
GRANT CREATEIN ON SCHEMA FGEDB TO developer_user;
GRANT ALTERIN ON SCHEMA FGEDB TO developer_user;
GRANT DROPIN ON SCHEMA FGEDB TO developer_user;

# 授予表空间权限
GRANT USE OF TABLESPACE USERSPACE1 TO developer_user;

# 查看数据库权限
SELECT
GRANTOR,
GRANTEE,
GRANTEETYPE,
DBADMAUTH,
CREATETABAUTH
FROM SYSCAT.DBAUTH
ORDER BY GRANTEE;

# 查看模式权限
SELECT
GRANTEE,
GRANTEETYPE,
SCHEMANAME,
CREATEINAUTH,
ALTERINAUTH,
DROPINAUTH
FROM SYSCAT.SCHEMAAUTH
ORDER BY SCHEMANAME;

# 审查用户权限
SELECT
‘TABLE’ AS OBJECT_TYPE,
GRANTEE AS USER_NAME,
TABSCHEMA || ‘.’ || TABNAME AS OBJECT_NAME,
SELECTAUTH,
INSERTAUTH,
UPDATEAUTH,
DELETEAUTH
FROM SYSCAT.TABAUTH
WHERE GRANTEE = ‘APP_USER’

UNION ALL

SELECT
‘SCHEMA’ AS OBJECT_TYPE,
GRANTEE AS USER_NAME,
SCHEMANAME AS OBJECT_NAME,
CREATEINAUTH,
ALTERINAUTH,
DROPINAUTH,
NULL
FROM SYSCAT.SCHEMAAUTH
WHERE GRANTEE = ‘APP_USER’

ORDER BY OBJECT_TYPE, OBJECT_NAME;

# 审查角色权限
SELECT
ROLENAME,
OBJECTTYPE,
OBJECTNAME,
PRIVILEGE
FROM SYSCAT.ROLEOBJECTAUTH
WHERE ROLENAME IN (‘ORDER_READONLY’, ‘ORDER_OPERATOR’)
ORDER BY ROLENAME, OBJECTNAME;

# 审查权限继承
SELECT
ROLENAME,
GRANTEE,
GRANTEETYPE
FROM SYSCAT.ROLEAUTH
WHERE GRANTEETYPE = ‘R’
ORDER BY ROLENAME;

# 查找过度授权
SELECT
GRANTEE,
COUNT(*) AS PRIVILEGE_COUNT
FROM SYSCAT.TABAUTH
GROUP BY GRANTEE
HAVING COUNT(*) > 50
ORDER BY PRIVILEGE_COUNT DESC;

# 回收表权限
REVOKE SELECT ON TABLE ORDERS FROM USER app_user;
REVOKE INSERT, UPDATE ON TABLE ORDERS FROM USER app_user;

# 回收角色
REVOKE ORDER_READONLY FROM USER app_user;

# 回收数据库权限
REVOKE DBADM ON DATABASE FROM USER db_admin_user;

# 回收模式权限
REVOKE CREATEIN ON SCHEMA FGEDB FROM USER developer_user;

# 批量回收权限
CREATE OR REPLACE PROCEDURE SP_REVOKE_ALL_PRIVILEGES(
IN p_user_name VARCHAR(128)
)
LANGUAGE SQL
BEGIN
DECLARE v_sql VARCHAR(1000);

FOR tab AS cur CURSOR FOR
SELECT TABSCHEMA, TABNAME
FROM SYSCAT.TABAUTH
WHERE GRANTEE = UPPER(p_user_name)
DO
SET v_sql = ‘REVOKE ALL ON TABLE ‘ || tab.TABSCHEMA || ‘.’ || tab.TABNAME ||
‘ FROM USER ‘ || p_user_name;
EXECUTE IMMEDIATE v_sql;
END FOR;
END;

# 调用存储过程
CALL SP_REVOKE_ALL_PRIVILEGES(‘app_user’);