Linux中创建oracle视图并授权 |
您所在的位置：网站首页 › oracle视图权限提供第三方 › Linux中创建oracle视图并授权 |
|
目录 第一步： 第二步： 第三步：创建视图 注意点： 示例 第一步：su - oracle 第二步：sqlplus / as sysdba 第三步：创建视图CREATE OR REPLACE VIEW 视图名称 AS sql语句 第四步：授权grant select on 视图名称 to 用户; grant select on ctp_affair_view to V3XUSER; 注意点在Oracle里，表的别名不能用as,列的别名可以用as（否则会报错右括号多余） select a.appname from appinfo a;-- 正确 select a.appname from appinfo as a;-- 错误 在创建视图的Oracle语句里面，每一个表都要写为 用户名·数据表 V3XUSER.org_member 示例 CREATE OR REPLACE VIEW bul_data_view AS SELECT DISTINCT org_member.CODE AS CREATE_USER_CODE, org_unit.NAME AS ORG_UNIT_NAME, org_unit.CODE AS ORG_UNIT_CODE, up1.CODE AS UPDATE_USER_CODE, up2.CODE AS AUDIT_USER_CODE, up3.CODE AS PUBLISH_USER_CODE, to_char(bul_data.PUBLISH_CHOOSE) PUBLISH_CHOOSE, to_char(bul_data.WRITE_PUBLISH) WRITE_PUBLISH, to_char(bul_data.OPEN_RECORD_READ) OPEN_RECORD_READ, to_char(bul_data.SHORT_CONTENT) SHORT_CONTENT FROM V3XUSER.bul_data INNER JOIN V3XUSER.org_member ON bul_data.CREATE_USER = org_member.ID LEFT JOIN V3XUSER.org_unit ON bul_data.ACCOUNTID = org_unit.ID LEFT JOIN (SELECT m.CODE, m.ID FROM V3XUSER.bul_data b LEFT JOIN V3XUSER.org_member m ON m.ID = b.UPDATE_USER) up1 ON up1.id = bul_data.UPDATE_USER LEFT JOIN (SELECT m.CODE, m.ID FROM V3XUSER.bul_data b LEFT JOIN V3XUSER.org_member m ON m.ID = b.AUDIT_USER_ID) up2 ON up2.id = bul_data.AUDIT_USER_ID LEFT JOIN (SELECT m.CODE, m.ID FROM V3XUSER.bul_data b LEFT JOIN V3XUSER.org_member m ON m.ID = b.PUBLISH_USER_ID) up3 ON up3.id = bul_data.PUBLISH_USER_ID; |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |