1. 创建测试用户和测试表
-- 创建用户
sp_iqaddlogin 'user1','pwd@123' ;
grant resource to user1 ;
sp_iqaddlogin 'user2','pwd@456' ;
grant resource to user2 ;
-- 用user1登陆IQ,创建表test1,并插入数据
create table test1 (id int, name char(8), age tinyint, primary key(id));
insert into test1 values(1,'aaaaaaaa',20);
insert into test1 values(2,'bbbbbbbb',30);
insert into test1 values(3,'cccccccc',40);
commit;
2. 为表进行授权
-- 用user1登陆IQ,为user2用户授予test1表的select 和 insert 权限
grant select , insert on test1 to user2;
3. 获取表的授权信息
--get_a_table_auths.sql
PARAMETERS table_name, user_name;
SELECT T.table_id, T.creator, T.table_name, U.user_name,
P.grantee, E.user_name AS grantee_name,
P.grantor, O.user_name AS grantor_name,
P.selectauth, P.insertauth, P.deleteauth,
P.updateauth, P.alterauth, P.referenceauth
FROM SYS.SYSTABLEPERM P JOIN SYS.SYSTAB T ON T.table_id = P.stable_id
JOIN SYS.SYSUSER U ON U.user_id = T.creator
JOIN SYS.SYSUSER E ON E.user_id = P.grantee
JOIN SYS.SYSUSER O ON O.user_id = P.grantor
WHERE T.table_type IN ( 1, 3 ) AND T.table_name='{table_name}'
AND E.user_name = '{user_name}'
ORDER BY T.table_name, U.user_name, grantee_name, grantor_name;
4. 执行脚本
dbisql -c "uid=user1;pwd=pwd@123" -onerror exit -nogui READ get_a_table_auths.sql ['test1'] ['user2']
执行之后,输出信息中的selectauth和insertauth字段的值都是'Y',表明user2在test1表上具有查询和插入操作权限。