ORA-01031: insufficient privileges ALTER PLUGGABLE DATABASE to OPEN

oracleoracle-12cpermissions

I'm currently working with Oracle database version 12c. I have created a common admin user – "C##ADMIN" with DBA Role. But when trying to execute the following script:

ALTER PLUGGABLE DATABASE TESTPDB OPEN;

I hit error:

ORA-00990: missing or invalid privilege

How should I assign the correct roles/privileges to "C##ADMIN" in order to run script above?

Best Answer

Just grant SYSDBA/DBA privilege to the user to all containers and login as SYSDBA to perform the task.

Demo:

SQL> create user c##admin identified by c##admin container=all;

User created.

SQL> grant sysdba to c##admin container=all;

Grant succeeded.

SQL> conn c##admin as sysdba
Enter password:
Connected.
SQL> alter pluggable database orclpdb open;

Related Solutions

Oracle 12c create user

If you want to create a user that can admin any of the pluggable databases, prefix the username with C##.

For example:

create user c##name identified by password;

Linux – How the sys user gets the sysdba privilege

From the 12c docs:

The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA or SYSOPER. Connecting as a SYSDBA user invokes the SYSDBA privilege; connecting as SYSOPER invokes the SYSOPER privilege. Oracle Enterprise Manager Database Control does not permit you to log in as user SYS without connecting as SYSDBA or SYSOPER.

And you cannot revoke it:

SQL> revoke sysdba from sys;
revoke sysdba from sys
*
ERROR at line 1:
ORA-01998: REVOKE failed: user SYS always has SYSOPER
and SYSDBA

More information from the docs:

These administrative privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself. Methods for authenticating database administrators with these privileges include operating system (OS) authentication, password files, and strong authentication with a directory-based authentication service.

These privileges can also be thought of as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other fashion.

and:

When you grant SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM administrative privilege to a user, that user's name and privilege information are added to the database password file. A user's name remains in the password file only as long as that user has at least one of these privileges. If you revoke all of these privileges, Oracle Database removes the user from the password file.

Best Answer

Related Solutions

Oracle 12c create user

Linux – How the sys user gets the sysdba privilege

Related Question