2

For one of my assignments I have been attempting to demonstrate password file functionality. However, I have run into some unexpected behaviors. I have tried searching around for answers but can't find any relevant ones; additionally, behavior differs from what the documentation specifies in some scenarios.

I am using 12c and have made sure that the remote_login_passwordfile parameter is set to EXCLUSIVE.

I'm sure I'm probably just missing some base knowledge as the behaviors are consistent; the problems I am having are as follows:

  1. 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?

  2. 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.

  3. 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.

  4. 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).

Any insights are greatly appreciated - thanks!

3
  1. 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.

  1. 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.

  1. 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


  1. 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.
  • @Stazz: Now my answer solves all of your issues. ;) – JSapkota Dec 2 '16 at 3:59
  • Much appreciated - I was sure I was just missing some specific nuances! – Stazz Dec 2 '16 at 21:30
-1

follow the following video for password file in oracle 12c

https://www.youtube.com/watch?v=Y7IrPNUEOSM&t=51s

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.