关于sysdba,sysoper,dba的区别(r3笔记第62天)-白红宇

关于sysdba,sysoper,dba的区别(r3笔记第62天)

阅读量：1961 次

发布时间：2019-04-27

本文共 1878 字，大约阅读时间需要 6 分钟。

关于sysdba,sysoper,dba这些名词在工作中可能接触的比较多，如果接触的环境是服务器端的，sysdba可能是经常用到的。如果是数据库的维护工作，dba就是必备的权限。

System Privilege	Operations Authorized
SYSDBA	Perform STARTUP and SHUTDOWN operations ALTER DATABASE: open, mount, back up, or change character set CREATE DATABASE DROP DATABASE CREATE SPFILE ALTER DATABASE ARCHIVELOG ALTER DATABASE RECOVER Includes the RESTRICTED SESSION privilege Effectively, this system privilege allows a user to connect as user SYS.
SYSOPER	Perform STARTUP and SHUTDOWN operations CREATE SPFILE ALTER DATABASE OPEN/MOUNT/BACKUP ALTER DATABASE ARCHIVELOG ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME\|CHANGE\|CANCEL\|CONTROLFILE requires connecting as SYSDBA.) Includes the RESTRICTED SESSION privilege

System Privilege

Operations Authorized

SYSDBA

Perform STARTUP and SHUTDOWN operations

ALTER DATABASE: open, mount, back up, or change character set

CREATE DATABASE

DROP DATABASE

CREATE SPFILE

ALTER DATABASE ARCHIVELOG

ALTER DATABASE RECOVER

Includes the RESTRICTED SESSION privilege

Effectively, this system privilege allows a user to connect as user SYS.

SYSOPER

Perform STARTUP and SHUTDOWN operations

CREATE SPFILE

ALTER DATABASE OPEN/MOUNT/BACKUP

ALTER DATABASE ARCHIVELOG

ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting as SYSDBA.)

Includes the RESTRICTED SESSION privilege

#1 sys，system用户都是数据库创建时内置的用户。sys绑定的是sysdba系统权限，system绑定的是dba角色。#2 如果直接拿sys来登录,不使用sysdba，会有下面的错误。sqlplus sys/oracleORA-28009: connection as SYS should be as SYSDBA or SYSOPER#3 sysdba对应的用户时SYS,而sysoper对应的用户PUBLIC conn / as sysoperUSER is "PUBLIC" conn / as sysdbaUSER is "SYS"#4 如果在服务端登录，随便用一个用户。因为设置了操作系统级的验证，所以都可以使用sysdba.conn test/test as sysdbaORA-01031: insufficient privileges#5 关于sys，不得不提到密码文件。如果没有密码文件。会报如下的错误。

[ora11g@rac1 dbs]$ sqlplus sys/oracle@test01 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 22 05:40:03 2014

ERROR:ORA-01031: insufficient privileges

[ora11g@rac1 dbs]$ orapwd password=oracle file=orapwTEST01 entries=2

SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 22 05:40:37 2014

SQL> exit#6 关于sysdba和dba的区别，可以通过如下的例子来简单示范一下。

[ora11g@rac1 dbs]$ sqlplus n1/n1 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 22 05:56:40 2014

SQL> select count(*)from cat;

COUNT(*)4809

SQL> conn n1/n1

COUNT(*)406

转载地址：http://sxpsf.baihongyu.com/

你可能感兴趣的文章