Oracle设置sys用户以普通用户身份登录数据库

最新推荐文章于 2022-10-27 23:28:13 发布

lk_db

最新推荐文章于 2022-10-27 23:28:13 发布

阅读量6.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/lk_db/article/details/51208813

Oracle 专栏收录该内容

120 篇文章

订阅专栏

这篇博客介绍了如何在Oracle数据库中，使sys超级用户以普通用户的权限进行登录，涉及关键设置O7_DICTIONARY_ACCESSIBILITY的调整。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、查看当前数据库版本及状态信息
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select dbid,name,open_mode from v$database;
DBID NAME OPEN_MODE
---------- --------- --------------------
1435632369 ORCL READ WRITE

2、查看默认设置（sys必须以as sysdba权限登录时）
SQL> show user
USER is "SYS"
SQL> show parameter O7_DICTIONARY_ACCESSIBILITY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------

O7_DICTIONARY_ACCESSIBILITY boolean FALSE

SQL> conn sys/oracle

ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Warning: You are no longer connected to ORACLE.

SQL> conn sys/oracle as sysdba
Connected.

3、修改参数O7_DICTIONARY_ACCESSIBILITY=true，重启实例
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2253784 bytes
Variable Size 327158824 bytes
Database Buffers 79691776 bytes
Redo Buffers 4268032 bytes
Database mounted.
Database opened.
SQL> show parameter O7_DICTIONARY_ACCESSIBILITY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean TRUE

4、此时sys可以以普通用户身份登录数据库
SQL> conn sys/oracle
Connected.
SQL> show user
USER is "SYS"

以普通用户身份登录数据库后，sys用户无启停数据库的权限
SQL> shutdown immediate
ORA-01031: insufficient privileges

SQL> startup force
ORA-01031: insufficient privileges
SQL>

必须以as sysdba方式登录后才可以启停数据库
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2253784 bytes
Variable Size 327158824 bytes
Database Buffers 79691776 bytes
Redo Buffers 4268032 bytes
Database mounted.
Database opened.

5、将参数O7_DICTIONARY_ACCESSIBILITY修改为默认值false
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=false scope=spfile;
System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2253784 bytes
Variable Size 327158824 bytes
Database Buffers 79691776 bytes
Redo Buffers 4268032 bytes
Database mounted.
Database opened.
SQL>