成都网站建设推来客,网站开发框架图,wordpress附件下载失败,营业执照名称查询系统有时候在ORACLE数据库创建视图时会遇到:ORA-01031:insufficient privileges错误,我也多次碰到了各种创建视图出错的情况#xff0c;很多时候也没有太在意#xff0c;今天被一同事问起这个问题#xff0c;顺便总结一下出错的各种场景。 场景1#xff1a;使用sys或system账号…有时候在ORACLE数据库创建视图时会遇到:ORA-01031:insufficient privileges错误,我也多次碰到了各种创建视图出错的情况很多时候也没有太在意今天被一同事问起这个问题顺便总结一下出错的各种场景。 场景1使用sys或system账号登陆数据库创建dm、ods账号授予connect、resource角色 1: [oracleDB-Server ~]$ sqlplus / as sysdba 2: 3: SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 14 10:28:49 2014 4: 5: Copyright (c) 1982, 2007, Oracle. All Rights Reserved. 6: 7: 8: Connected to: 9: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production 10: With the Partitioning, OLAP, Data Mining and Real Application Testing options 11: 12: 13: 14: SQL create user dm identified by dm default tablespace tbs_dm_data; 15: 16: User created. 17: 18: 19: 20: SQL grant connect, resource to dm; 21: 22: Grant succeeded. 23: 24: 25: 26: SQL create user ods identified by ods default tablespace tbs_ods_data; 27: 28: User created. 29: 30: SQL grant connect ,resource to ods; 31: 32: Grant succeeded. 在另外一个窗口以dm账号登录数据库 1: [oracleDB-Server bdump]$ sqlplus /nolog 2: 3: SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 14 10:35:30 2014 4: 5: Copyright (c) 1982, 2007, Oracle. All Rights Reserved. 6: 7: SQL conn dm 8: Enter password: 9: Connected. 创建测试表test并插入数据。然后创建该表对应的视图v_dm_test时报ORA-01031: insufficient privileges 1: SQL create table dm.test 2: 2 ( 3: 3 name varchar2(12) 4: 4 ); 5: 6: Table created. 7: 8: SQL insert into dm.test 9: 2 select kerry from dual; 10: 11: 0 rows created. 12: 13: SQL commit; 14: 15: SQL create or replace view v_dm_test 16: 2 as 17: 3 select * from dm.test; 18: create or replace view v_dm_test 19: * 20: ERROR at line 1: 21: ORA-01031: insufficient privileges 22: 23: 24: SQL 结论在这个场景出现这个错误是因为账号dm并没有授予创建视图的权限。需要授予dm账号创建视图的权限。以sys/system等具有DBA权限的账号登陆数据库授予dm账号创建视图的权限。 1: sys 账号 2: 3: SQL show user; 4: USER is SYS 5: SQL grant create view to dm; 6: 7: Grant succeeded. 8: 9: dm 账号 10: 11: SQL show user 12: USER is DM 13: SQL create or replace view v_dm_test 14: 2 as 15: 3 select * from dm.test; 16: 17: View created. 场景2在上面的场景中,在ods账号下创建test_ods表并插入数据。然后授权select给dm用户然后在dm用户下创建视图 1: ods login database 2: 3: SQL show user 4: USER is ODS 5: SQL create table ods.test_ods 6: 2 ( 7: 3 name varchar2(12) 8: 4 ); 9: 10: Table created. 11: 12: SQL insert into ods.test_ods 13: 2 select jimmy from dual; 14: 15: 1 row created. 16: 17: SQL commit; 18: 19: Commit complete. 20: 21: SQL grant select on ods.test_ods to dm; 22: 23: Grant succeeded. 24: 25: 26: dm login database 27: 28: SQL conn dm 29: Enter password: 30: Connected. 31: SQL select * from ods.test_ods; 32: 33: NAME 34: ------------ 35: jimmy 36: 37: SQL create or replace view v_ods_test 38: 2 as 39: 3 select * from ods.test_ods; 40: 41: View created. 先删除视图v_ods_test,然后收回用户dm创建视图的权限。 1: sys login database 2: SQL show user 3: USER is SYS 4: SQL revoke create view from dm; 5: 6: Revoke succeeded. 7: 8: SQL 然后在dm下创建视图时会出现场景一的错误 1: SQL show user 2: USER is DM 3: SQL create or replace view v_ods_test 4: 2 as 5: 3 select * from ods.test_ods; 6: create or replace view v_ods_test 7: * 8: ERROR at line 1: 9: ORA-01031: insufficient privileges 但是即使dm没有创建视图的权限了我依然可以在sys用户下创建dm下视图 1: SQL show user; 2: USER is SYS 3: SQL create or replace view dm.v_ods_test 4: 2 as 5: 3 select * from ods.test_ods; 6: 7: View created. 场景3: 在上面场景中我们依然给予DM账号创建视图的权限然后按如下步骤去测试 1: SQL show user 2: USER is ODS 3: SQL create table ods.test_view 4: 2 ( 5: 3 name varchar2(12) 6: 4 ) 7: 5 ; 8: 9: Table created. 10: 11: SQL insert into ods.test_view 12: 2 select kkk from dual; 13: 14: 1 row created. 15: 16: SQL commit; 17: 18: Commit complete. 创建角色role_select_test然后将表test_view的查询权限授予该角色最后将该角色授予dm用户 1: sys user login 2: 3: SQL show user 4: USER is SYS 5: SQL create role role_select_test; 6: 7: Role created. 8: 9: SQL grant select on ods.test_view to role_select_test; 10: 11: Grant succeeded. 12: 13: SQL grant role_select_test to dm; 14: 15: Grant succeeded. 但是在dm用户下创建视图时报错。 1: SQL conn dm 2: Enter password: 3: Connected. 4: SQL select * from ods.test_view; 5: 6: NAME 7: ------------ 8: kkk 9: 10: SQL create or replace view dm.v_ods_test2 11: 2 as 12: 3 select * from ods.test_view; 13: select * from ods.test_view 14: * 15: ERROR at line 3: 16: ORA-01031: insufficient privileges 这时如果显示将表ods.test_view的查询权限授予dm后就可以创建视图。 1: SQL show user 2: USER is ODS 3: SQL grant select on ods.test_view to dm; 4: 5: Grant succeeded. 6: 7: 8: 9: SQL show user 10: USER is DM 11: SQL create or replace view dm.v_odst_test2 12: 2 as 13: 3 select * from ods.test_view; 14: 15: View created. 结论 创建create view 的时候是不可以利用相应的role隐式授权的必须显式的授予这个对象相应的权限。metalink解释如下 reasonUnder SQL, if a user can select another users table and has the privilege to create a view, then the create view works. Yet, a create view on the other users table generates ORA-01031 if the select privilege has been granted to a role and not directly. 官方文档关于创建视图的权限 Privileges Required to Create Views To create a view, you must meet the following requirements: You must have been granted the CREATE VIEW (to create a view in your schema) or CREATE ANY VIEW (to create a view in another users schema) system privilege, either explicitly or through a role. You must have been explicitly granted the SELECT, INSERT, UPDATE, or DELETE object privileges on all base objects underlying the view or the SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, or DELETE ANY TABLE system privileges. You may not have obtained these privileges through roles. Additionally, in order to grant other users access to your view, you must have received object privilege(s) to the base objects with the GRANT OPTION option or appropriate system privileges with the ADMIN OPTION option. If you have not, grantees cannot access your view.
