- Deleting the password file itself (without replacing it) and then doing a subsequent lookup of v$pwfile_users removes all of the sys-privileged users from the CBD, but users from my PDB still remain in the lookup. Are PDB passwords stored somewhere other than that file? How are they showing up if the file no longer exists?
The password of PDB users(local users) as well as common users with administrative privileges are stored in data dictionary in encrypted form.
SQL> select password from sys.user$ where name='JAY';
PASSWORD
--------------------------------------------------------------------------------
4FF80DF8D8DBE02E
SQL> select username, common from dba_users where username='JAY';
USERNAME COM
--------- ---
JAY NO
If no password file exit common user is unable to get sysdba privilege.
SQL> create user testuser identified by testuser container=all;
User created.
SQL> grant sysdba to testuser;
grant sysdba to testuser
*
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled
Unable to add common users to the password file as I have removed the password file prior to creating this user.
But assigning SYSDBA to local users is possible!
SQL> alter session set container=orapdb1;
Session altered.
SQL> create user newuser identified by newuser container=current;
User created.
SQL> grant sysdba to newuser;
Grant succeeded.
It confirms that the password for PDB administrative users are stored in data dictionary rather password file.
- The IGNORECASE option for the new file seems to only have an effect on common users rather than PDB users; newly-assigned CDBs cannot login without the correct case, but PDB users can, again making me wonder about some separate password storage.
Same applies here. As PDB administrative privileged users(local users) are not stored in password file IGNORECASE and ENTRIES do not apply to them.
- In both the CDB and the PDB, the ENTRIES option seems to have no effect - despite setting it to 2, I can continually add way more than 2 users in both containers.
It allows us to store the password of the privileged user until the Operating System block is filled(generally up to 4 passwords per block) as per the Oracle Documentation. I have demonstrated this behavior after the excerpt from Oracle Documentation.
ENTRIES
This argument specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.
I have created password as:
[oracle@ora12c dbs]$ orapwd file=orapworacdb entries=2
Enter password for SYS:
Then I created four test users and tried to grant the SYSDBA privilege but the grant failed for fourth user. Becuase there were already four users with SYSDBA privilege including SYS.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
C##_ABC TRUE FALSE FALSE FALSE FALSE FALSE 0
C##_ABC2 TRUE FALSE FALSE FALSE FALSE FALSE 0
C##_ABC3 TRUE FALSE FALSE FALSE FALSE FALSE 0
When I tried to grant SYSDBA to fourth user C##_ABC4 I got the following error.
SQL> grant sysdba to c##_abc4 container=all;
grant sysdba to c##_abc4 container=all
*
ERROR at line 1:
ORA-01996: GRANT failed: password file
'/u01/app/oracle/product/12.1.0/db_1/dbs/orapworacdb' is full
- I wanted to demonstrate that the password files actually worked by doing some sort of sysoper operation with one of my newly-privileged accounts, such as shutting the database down. I can do this in the PDB but it will not let me do it in the CDB despite the documentation explicitly stating that sysoper users have that privilege. Am I missing some other piece of information that would allow this to happen? The error I get is insufficient privileges (and yes, I have made sure to connect using as sysoper).
It depends upon how you have granted the SYSOPER role. To shutdown the database instance user should have SYSOPER privilege on all containers.
The following demonstrate the scenario.
I have common user call C##_TEST.
[oracle@ora12c dbs]$ sqlplus sys@oracdb as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:47:05 2016
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select common from dba_users where username='C##_TEST';
COM
---
YES
I have granted SYSOPER to the user for the ORACDB(CDB) only.
SQL> grant sysoper to c##_test container=current;
Grant succeeded.
Then I have connected to container database using C##_TEST user as SYSOPER and tried to shut down the database and got the ORA-01031 error.
[oracle@ora12c dbs]$ sqlplus c##_test/test@oracdb as sysoper
SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:49:05 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> shut immediate;
ORA-01031: insufficient privileges
SQL>
Now I again granted the SYSOPER privilege with the option container=all. And this time the user can shut down the database.
[oracle@ora12c dbs]$ sqlplus sys@oracdb as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:49:36 2016
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> grant sysoper to c##_test container=all;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12c dbs]$ sqlplus c##_test/test@oracdb as sysoper
SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:50:06 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
