标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-00742 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (103)
- 数据库 (1,701)
- DB2 (22)
- MySQL (74)
- Oracle (1,562)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (68)
- Oracle Bug (8)
- Oracle RAC (53)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (571)
- Oracle安装升级 (94)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (81)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- fio测试io,导致磁盘文件系统损坏故障恢复
- ORA-742 写丢失常见bug记录
- Oracle 19c 202501补丁(RUs+OJVM)
- 避免 19c 数据库性能问题需要考虑的事项 (Doc ID 3050476.1)
- Bug 21915719 Database hang or may fail to OPEN in 12c IBM AIX or HPUX Itanium – ORA-742, DEADLOCK or ORA-600 [kcrfrgv_nextlwn_scn] ORA-600 [krr_process_read_error_2]
- ORA-600 ktuPopDictI_1恢复
- impdp导入数据丢失sys授权问题分析
- impdp 创建index提示ORA-00942: table or view does not exist
- 数据泵导出 (expdp) 和导入 (impdp)工具性能降低分析参考
- 19c非归档数据库断电导致ORA-00742故障恢复
- Oracle 19c – 手动升级到 Non-CDB Oracle Database 19c 的完整核对清单
- sqlite数据库简单操作
- Oracle 暂定和恢复功能
- .pzpq扩展名勒索恢复
- Oracle read only用户—23ai新特性:只读用户
- 迁移awr快照数据到自定义表空间
- .hmallox加密mariadb/mysql数据库恢复
- 2025年首个故障恢复—ORA-600 kcbzib_kcrsds_1
- 第一例Oracle 21c恢复咨询
- ORA-15411: Failure groups in disk group DATA have different number of disks.
标签归档:ORA-01031
创建视图提示ORA-01031
1、重现问题
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> select table_name from user_tables where table_name in('X_T','DEPT'); TABLE_NAME ------------------------------ X_T SQL> create view v_xff1 2 as 3 select * from X_T; View created. SQL> create view V_XFF AS 2 SELECT * FROM SCOTT.DEPT; SELECT * FROM SCOTT.DEPT * ERROR at line 2: ORA-01031: insufficient privileges SQL> SELECT COUNT(*) FROM SCOTT.DEPT; COUNT(*) ---------- 4
通过上面的试验证明:
1)在同一个schema下,有查询权限,就可以创建视图
2)在不同schema下,即使有了查询权限,创建视图,还是会提示ORA-01031
2、查看相关权限情况
SQL> select * from SESSION_PRIVS where 2 PRIVILEGE in('SELECT ANY TABLE','CREATE ANY VIEW','CREATE VIEW'); PRIVILEGE ---------------------------------------- SELECT ANY TABLE CREATE VIEW CREATE ANY VIEW SQL> SELECT * FROM SESSION_PRIVS WHERE PRIVILEGE NOT IN( 2 SELECT PRIVILEGE 3 FROM ROLE_SYS_PRIVS 4 WHERE ROLE IN(SELECT * FROM SESSION_ROLES)); no rows selected
通过上面权限查询得出:用户所具有的select 其他用户表的权限是用过role授权
3、单独授于select权限
SQL> conn / as sysdba Connected. SQL> grant select on SCOTT.DEPT to xff; Grant succeeded. SQL> conn xff/xifenfei Connected. SQL> create view V_XFF AS 2 SELECT * FROM SCOTT.DEPT; View created. SQL> select view_name from user_views; VIEW_NAME ------------------------------ V_XFF V_XFF1
4、产生问题原因
In order to create a view in a schema, that schema must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The view owner must be granted these privileges directly, rather than through a role. The reason is that privileges granted to roles cannot be inherited via objects
ORA-1031 While Creating A View On A Table On Which The Select Privilege Is Granted Via A Role
win平台登录sqlplus报ora-01031错误解决
今天一朋友告诉我,他的win平台的oracle不能在本地使用sqlplus / as sysdba登录数据库,提示ora-01031的错误,他说是administrator用户,应该不会出现权限不足的情况。我的登录上去一看,果真是这样的情况:
01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
// without the appropriate privilege. This error also occurs if
// attempting to install a database without the necessary operating
// system privileges.
// When Trusted Oracle is configure in DBMS MAC, this error may occur
// if the user was granted the necessary privilege at a higher label
// than the current login.
// *Action: Ask the database administrator to perform the operation or grant
// the required privileges.
// For Trusted Oracle users getting this error although granted the
// the appropriate privilege at a higher label, ask the database
// administrator to regrant the privilege at the appropriate label.
根据这个错误提示,我的第一反应就是当前的用户不属于ora_dba用户组,通过计算机管理–>本地用户和组–>administrator用户属性–>隶属于中只有一个administrators,果然没有ora_dba组
接下来的事情,就是添加ora_dba组到administrator用户中
点击刚刚隶属于下面的添加–>高级–>立即查找–选择ora_dba–点击确定–>再点击选择组中的确定–>点击用户属性的确定
添加把ora_dba添加到administrator用户所属组中,再尝试登录
ok,登录成功了,看来在win系统中,要想使用sqlplus / as sysdba 登录数据库,必须要隶属于ora_dba组,就算administrators组也不能越俎代庖。
根据这个错误提示,我的第一反应就是当前的用户不属于ora_dba用户组,通过计算机管理–>本地用户和组–>administrator用户属性–>隶属于中只有一个administrators,果然没有ora_dba组
接下来的事情,就是添加ora_dba组到administrator用户中
点击刚刚隶属于下面的添加–>高级–>立即查找–选择ora_dba–点击确定–>再点击选择组中的确定–>点击用户属性的确定
添加把ora_dba添加到administrator用户所属组中,再尝试登录
ok,登录成功了,看来在win系统中,要想使用sqlplus / as sysdba 登录数据库,必须要隶属于ora_dba组,就算administrators组也不能越俎代庖。