ORA-65175: cannot grant SYSDBA privilege locally in the root

Question

I have a oracle 12c database . I would like to grant sysdba to C##user1. Here is user table.

When I execute this command I can get a error.

grant sysdba to c##user1 container=current

Error report -
SQL Error: ORA-65175: cannot grant SYSDBA privilege locally in the root
65175. 00000 -  "cannot grant SYSDBA privilege locally in the root"
*Cause:    An attempt was made to grant SYSDBA privilege locally in the root
           of a multitenant container database (CDB).
*Action:   While connected to the root, SYSDBA privilege can only be granted
           commonly.

and when I execute this command , I can get 2 users of C##user1.

grant sysdba to c##user1 container=all

How can I grant sysdba to C##user1. Thank you for viewing. Pls help me.

Solution 1

Are you trying to grant sysdba to c##user1 at the Container or Root level? This is an important distinction within 12C, as the Container is logically separate from the rest of the CDB. The CON_ID column will tell you where each user resides - Con_ID=0 means that the row pertains to the entire CDB, whereas CON_ID=1 means that the row pertains to the root.

You currently have two "C##user1" users, one is a common user that is present in all containers (CON_ID=0,) and the other is a local user that is specific to the root.

You already have one "C##user1" user that has the SYSDBA privilege on the entire CDB, so if that's what you want, you can connect to the root and drop the local "C##user1" user. If you just wanted a local user with the SYSDBA privilege on that root only, I would recommend dropping the "C##user1" common user, then connecting to the root and granting sysdba to the local user there.

The article I linked to is titled "Overview of the Multitenant Architecture", I would suggest giving it a review before you make a decision either way.

Solution 2

under cdb connection try it

grant sysdba to c##user1 container=all

Comments

[美丽美丽花]

Thank you for answer.when I use C##user1(conId=1) I get ora01031 error. insufficient priviledge sys.dbms_session.;;;;;And when I use C##user1(conId=0) table or view not found.How can I fix this?

[1991DBA]

"C##user1(conId=1)" is the local user without SYSDBA privileges, so you won't be able to call a lot of system-level procedures like DBMS_SESSION. When you connect to the common "C##user1(conId=0)" user, you will be logging into the Container of the CDB, not the root. You won't be able to see the tables in the root until you issue an "ALTER SESSION SET CONTAINER = CDB$ROOT" or whatever your root happens to be named.