database - SYSDBA 无法删除 Oracle 12.1.0.2 中的用户

Question

甲骨文新手。我已经在 CentOS 6.8 上成功安装了 Oracle DB。我创建了两个数据库:

1. 奥克兰
2. AVDB

“AVDB”数据库中存在一些用户。我希望在 AVDB 数据库中删除一个用户。但是，即使我以 SYS AS SYSDBA 用户身份连接，我仍收到权限不足错误。我什至无法使用 sys 用户创建用户。

但是，当我连接到与不同用户相同的数据库(我试图删除的用户是 avuser)时，我能够创建用户。

[oracle@via7was ~]$ sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 2 14:13:16 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,
Oracle Database Vault and Real Application Testing options

SQL> drop user avuser cascade;
drop user avuser cascade
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> create user test1 identified by secret;
create user test1 identified by secret
                                *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> disconnect
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,
Oracle Database Vault and Real Application Testing options
SQL> conn avuser
Enter password:
Connected.
SQL> create user test1 identified by secret;

User created.

Answer 1

First, determine if the vault is active:

 SQL> SELECT PARAMETER, VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

        PARAMETER
        ----------------------------------------------------------------
        VALUE
        ----------------------------------------------------------------
        Oracle Database Vault
        TRUE

Then connect as the database owner and disable the vault:

SQL> conn avuser
        Enter password:
        Connected.
        SQL> EXEC DVSYS.DBMS_MACADM.DISABLE_DV;

        PL/SQL procedure successfully completed.

Connect as SYSDBA and restart the database:

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Drop the user by connecting as SYSDBA:

SQL> conn sys as sysdba
    Enter password:
    Connected.
    SQL> drop user avuser cascade;
    drop user avuser cascade
    *
    ERROR at line 1:
    ORA-01940: cannot drop a user that is currently connected

If you get the above error, find out the active sessions of the user being dropped and kill them:

SQL> SELECT s.sid, s.serial#, s.status, p.spid FROM v$session s, v$process p WHERE s.username = 'AVUSER' AND p.addr(+) = s.paddr;

               SID    SERIAL# STATUS   SPID
        ---------- ---------- -------- ------------------------
                17      48252 INACTIVE 45579

        SQL> ALTER SYSTEM KILL SESSION '17,48252';

        System altered.

        SQL> drop user avuser cascade;

        User dropped.

Note: It's always advisable to re-enable the vault for security reasons after your activities are completed.