ALTER USER system privilege in Oracle 12cR2

In Oracle 12cR2 a new security enhancement has been introduced, the “alter user” system privilege when granted to an account….this account won’t be able to change “SYS” password anymore.

Let us explore “ALTER USER” system privilege in Oracle 12cR1 first:

1

2

 

3

 

4

while in Oracle 12cR2:

i have granted SYSTEM account the “ALTER USER” system privilege:

SQL> SELECT * FROM DBA_SYS_PRIVS where grantee=’SYSTEM’;

11

As a SYSTEM user I can’t alter the SYS user password as shown below:

12

The oracle error raised is:

ORA-01031: insufficient privileges

Not only you can’t change the password, you can’t change any activity that is related to the “ALTER USER” command such as changing the “profile”, “quota” ….etc

This feature will be very helpful if you have a segregation of duties in your database infrastructure  where account management is maintained by different entity which will definitely be granted “ALTER USER” system privilege for password management and reset….in this case they wont have the power to change the “SYS” user password.

Is there any workaround to go over this security restriction ?

Yes.

Under SYS schema create the following procedure:

SQL> create procedure sys.sys_pass_change(my_pass in varchar2) as

begin

execute immediate ‘alter user sys identified by ‘||my_pass;

end;

/

SQL> grant execute on sys.sys_pass_change to system;

Then, accessing the database as “SYSTEM” user:

SQL> exec sys.sys_pass_change(‘yarn_9ns’);

33

in this example The SYSTEM account will execute the procedure with “definer’s” right.

 

 

 

 

 

 

 

 

 

 

ORA-01031: insufficient privileges while creating MATERIALIZED VIEW

One of the things i have found by coincidence in Oracle 11g is when you try to create a materialized view by a non-schema user (even if he has a ‘DBA‘ privilege), you will face the following error:

ERROR at line 1: ORA-01031: insufficient privileges

 

although the user is powerful but he could not create the materialized view under any schema (in the following example the schema name is “sico“).

SQL> CREATE MATERIALIZED VIEW sico.test_mv AS SELECT * FROM dual;
CREATE MATERIALIZED VIEW sico.test_mv AS SELECT * FROM dual
*
ERROR at line 1:
ORA-01031: insufficient privileges

 

you can create a materialized view using the schema owner after granting him the “create table” privilege.

 

check it and see !!!

I personally faced this in Oracle 11gR2.