Oracle 11g 关于权限DBA和SYSDBA的区别是什么

笔者查询后得出
DBA属于一个角色，也就是各种权限的综合；
而SYSDBA则是一种认证，也就是SYS的认证，可以对数据库进行shutdown等等的操作；

这里涉及到两个表即dba_role_privs和v$pwfile_users

先看DBA；
新建一个cat用户，赋予它DBA的权限，然后执行各种操作

SQL> create user cat identified by cat;

User created.

SQL> grant dba to cat;

Grant succeeded.

SQL> select * from dba_role_privs where grantee='CAT';

GRANTEE 										   GRANTED_ROLE 									      ADMIN_OPT DEFAULT_R
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ --------- ---------
CAT											   DBA											      NO	YES

SQL> select * from v$pwfile_users;

USERNAME										   SYSDBA	   SYSOPER	   SYSASM
------------------------------------------------------------------------------------------ --------------- --------------- ---------------
SYS	
SQL> conn cat/cat
Connected.
SQL> create user catt identified by catt;

User created.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARCHIVED  STATUS					     FIRST_CHANGE# FIRST_TIME	   NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- --------------- ------------ ---------------
	 1	    1	     100   52428800	   512		1 YES	    INACTIVE						   1780744 08-FEB-20		1796736 09-FEB-20
	 2	    1	     101   52428800	   512		1 YES	    INACTIVE						   1796736 09-FEB-20		1812567 11-FEB-20
	 3	    1	     102   52428800	   512		1 NO	    CURRENT						   1812567 11-FEB-20	     2.8147E+14

SQL> shutdown immediate
ORA-01031: insufficient privileges

总结：授予了DBA权限的用户，可以做很多事情，但是无法关闭数据库。
创建用户的时候注意，录入数据库的用户都是大写。

接下来我们来看下授予sysdba的用户可以做些什么，新建一个dog用户，并执行如下操作

SQL> create user dog identified by dog;

User created.

SQL> show user
USER is "SYS"
SQL> grant sysdba to dog;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME										   SYSDBA	   SYSOPER	   SYSASM
------------------------------------------------------------------------------------------ --------------- --------------- ---------------
SYS											   TRUE 	   TRUE 	   FALSE
DOG											   TRUE 	   FALSE	   FALSE

SQL> select * from dba_role_privs where grantee='DOG';

no rows selected

SQL> conn dog/dog as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> create user abc identified by abc;

User created.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------- ---------
STATUS						 FIRST_CHANGE# FIRST_TIME
------------------------------------------------ ------------- ---------------
NEXT_CHANGE# NEXT_TIME
------------ ---------------
	 1	    1	     100   52428800	   512		1 YES
INACTIVE					       1780744 08-FEB-20
     1796736 09-FEB-20

	 2	    1	     101   52428800	   512		1 YES
INACTIVE					       1796736 09-FEB-20
     1812567 11-FEB-20

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------- ---------
STATUS						 FIRST_CHANGE# FIRST_TIME
------------------------------------------------ ------------- ---------------
NEXT_CHANGE# NEXT_TIME
------------ ---------------

	 3	    1	     102   52428800	   512		1 NO
CURRENT 					       1812567 11-FEB-20
  2.8147E+14


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut 

总结：被授予了sysdba的用户，登录时，需要+尾缀 as sysdba，所有DBA权限能做的都可以，并且还可以关闭数据库，相当强大哦；