1. 创建视图
create or replace VIEW V_TYBXD AS
select a.billcode as 单据号,m.stdname as 组织机构,s1.stdname as 提交人,s2.stdname as 报销人,
s3.ywdltype as 业务大类,s4.stdname as 支付方式, a.EXPENSEMONEY as 报销金额，a.MEMO as 事由
from FO_ACCOUNTBILL a left join md_org m on a.unitid=m.recid left join md_staff s1 on a.PROPOSER=s1.recid
left join md_staff s2 on a.staff=s2.recid left join md_ywdl s3 on a.ywdl=s3.recid left join md_paytype s4 on a.paytype=s4.recid order by a.billdate desc
with read only;
2. 查询视图
select * from V_TYBXD;
3. 创建一个用户给第三方系统对接使用
create user fanpu identified by 123456;
4. 授权这个用户查询视图的权限
grant select on view_car_runtime_b tofanpu ; //查询视图权限
grant connect tofanpu ; //连接数据库权限
grant create synonym tofanpu ; //新建同义词权限
5.登录到新用户fanpu,并为视图设置一个别名以方便使用 （不让每次都是加上视图创建者的名称 JTGX2.）
create synonym V_TYBXD for JTGX2.V_TYBXD;
6.以管理员登陆数据库，设置用户fanpu的允许访问会话数, 泛指被泛滥访问导致是数据库开销问题
alter system set resource_limit=true scope=both sid='*'; //使用resource_limit及profile限制用户连接
create profile fanpu_profile limit SESSIONS_PER_USER 1 FAILED_LOGIN_ATTEMPTS unlimited; //新建一个user profile:fanpu_profile
alter user fanpu profile fanpu_profile; //更改用户fanpu的profile为fanpu_profile
alter profile user01_profile limit SESSIONS_PER_USER 2; //限定用户fanpu的连接数为2
本文由
bigwei08028 创作，除注明转载/出处外，均为本站原创，转载前请务必署名
最后编辑时间为: 2021-11-01 09:02 Monday