Uwe Hesse

This user hasn't shared any biographical information

Homepage: http://uhesse.com

orapwd gives OPW-00029 Password complexity failed in #Oracle 12.2

When I tried to create a password file for a 12.2 database, it initially failed with my favorite (simple) password:

[oracle@uhesse dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprima password=oracle

OPW-00029: Password complexity failed for SYS user : Password must contain at least 8 characters.

Two options to resolve this: Either provide a password that passes the complexity check, like:

[oracle@uhesse dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprima password=Very_Complex§1

Or create the password file in 12.1 format (default being 12.2 format)

[oracle@uhesse dbs]$ orapwd describe file=orapwprima
Password file Description : format=12.2 
[oracle@uhesse dbs]$ rm orapwprima
[oracle@uhesse dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprima password=oracle format=12
[oracle@uhesse dbs]$ orapwd describe file=orapwprima
Password file Description : format=12 

The only drawback of the 12.1 format seems to be the lack of support for granting administrative privileges to external users and enabling SSL and Kerberos authentication for administrative users, according to the documentation. Which means for me I will keep my passwords simple 🙂

, ,

2 Comments

Getting started with VPD in #Oracle

Virtual Private Database (VPD) aka Fine Grained Access Control may seem complicated at first when you look at the fine documentation about it.

This article gives a short and simple example to get you started easily. The demo below is with 12c Multitenant but works the same with 11g:

SQL> connect reports_owner/reports_owner@pdb1
Connected.
SQL> select * from reports_table;

REPORT_NAME		       F
------------------------------ -
Monthly report R1	       M
Monthly report R2	       M
Daily report R1 	       D
Daily report R2 	       D

SQL> grant create session to monthly identified by monthly;

Grant succeeded.

SQL> grant select on reports_table to monthly;

Grant succeeded.

SQL> grant create session to daily identified by daily;

Grant succeeded.

SQL> grant select on reports_table to daily;

Grant succeeded.

Say this is a reporting system with a central table that is used for monthly and daily reports. We have separate users for the daily and monthly reports.
Initially, they see both kinds of entries in the table:

SQL> connect monthly/monthly@pdb1
Connected.
SQL> select * from reports_owner.reports_table;

REPORT_NAME		       F
------------------------------ -
Monthly report R1	       M
Monthly report R2	       M
Daily report R1 	       D
Daily report R2 	       D

SQL> connect daily/daily@pdb1
Connected.
SQL> select * from reports_owner.reports_table;

REPORT_NAME		       F
------------------------------ -
Monthly report R1	       M
Monthly report R2	       M
Daily report R1 	       D
Daily report R2 	       D

VPD enables both users to keep the same statement but they see only rows for monthly respectively daily frequency:

SQL> connect reports_owner/reports_owner@pdb1
Connected.
SQL> create or replace function frequency_function (schema varchar2, tab varchar2) 
return varchar2 
is
who varchar2(20);
freq char(1);
begin
 select  sys_context('userenv','session_user') into who from dual;
 case who
  when 'MONTHLY' then freq:='M';
  when 'DAILY' then freq:='D';
  else freq:='?';
 end case;

 return 'frequency=''' || freq || '''';
end;
/     

Function created.

SQL> begin
dbms_rls.add_policy(
 object_schema=>'REPORTS_OWNER',
 object_name=>'REPORTS_TABLE',
 policy_name=>'FREQUENCY_POLICY',
 function_schema=>'REPORTS_OWNER',
 policy_function=>'frequency_function');
end;
/   

PL/SQL procedure successfully completed.

SQL> connect monthly/monthly@pdb1
Connected.
SQL> select * from reports_owner.reports_table;

REPORT_NAME		       F
------------------------------ -
Monthly report R1	       M
Monthly report R2	       M

SQL> connect daily/daily@pdb1
Connected.
SQL> select * from reports_owner.reports_table;

REPORT_NAME		       F
------------------------------ -
Daily report R1 	       D
Daily report R2 	       D

This is possible because VPD silently attaches a WHERE condition to the seemingly unchanged statement:

SQL> connect sys/oracle@pdb1 as sysdba
Connected.
SQL> grant select_catalog_role to monthly;

Grant succeeded.

SQL> connect monthly/monthly@pdb1
Connected.
SQL> select * from reports_owner.reports_table;

REPORT_NAME		       F
------------------------------ -
Monthly report R1	       M
Monthly report R2	       M

SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	16xw6bqhp67gw, child number 0
-------------------------------------
select * from reports_owner.reports_table

Plan hash value: 2149466028

-----------------------------------------------------------------------------------
| Id  | Operation	  | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |		  |	  |	  |	3 (100)|	  |
|*  1 |  TABLE ACCESS FULL| REPORTS_TABLE |	2 |    38 |	3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FREQUENCY"='M')


18 rows selected.

The VPD policy adds that WHERE condition depending on the session user. Funny effect:

SQL> connect reports_owner/reports_owner@pdb1
Connected.
SQL> select * from reports_table;

no rows selected

SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	8xc692y0axx0x, child number 0
-------------------------------------
select * from reports_table

Plan hash value: 2149466028
-----------------------------------------------------------------------------------
| Id  | Operation	  | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |		  |	  |	  |	3 (100)|	  |
|*  1 |  TABLE ACCESS FULL| REPORTS_TABLE |	1 |    19 |	3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FREQUENCY"='?')


18 rows selected.

SYS is not subject to this policy, because of his privilege EXEMPT ACCESS POLICY:

SQL> connect sys/oracle@pdb1 as sysdba
Connected.
SQL> select * from reports_owner.reports_table;

REPORT_NAME		       F
------------------------------ -
Monthly report R1	       M
Monthly report R2	       M
Daily report R1 	       D
Daily report R2 	       D

SQL> grant exempt access policy to reports_owner;

Grant succeeded.

SQL> connect reports_owner/reports_owner@pdb1
Connected.
SQL> select * from reports_table;

REPORT_NAME		       F
------------------------------ -
Monthly report R1	       M
Monthly report R2	       M
Daily report R1 	       D
Daily report R2 	       D

SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	8xc692y0axx0x, child number 1
-------------------------------------
select * from reports_table

Plan hash value: 2149466028

-----------------------------------------------------------------------------------
| Id  | Operation	  | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |		  |	  |	  |	3 (100)|	  |
|   1 |  TABLE ACCESS FULL| REPORTS_TABLE |	4 |    76 |	3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------


13 rows selected.

So much for starters. Things get more complicated especially if there are no different database users as in the above simplified example. That is usually where an application context is being used – but that could be a topic for another article 🙂

Leave a comment

Joining Accenture Enkitec Group

I have accepted an offer by the Accenture Enkitec Group to join them starting next month. That unit is a kind of ‘special force’ inside the large Accenture Corporation with particular expertise in Oracle Database and Oracle Engineered Systems technology.

Although I feel quite a bit sad to leave Oracle after all those years, the opportunity to work together with all these bright people – you can see some of them here – outshines that largely 🙂

 

7 Comments

%d bloggers like this: