Tag: database cybersecurity

ALTER USER system privilege in Oracle 12cR2

arcsdegeo2 Comments

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.

 

 

 

 

 

 

 

 

 

 

Advertisement

Oracle Virtual Private Database

arcsdegeo1 Comment

The idea of Virtual Private Database is to enable users having access to a table to see ONLY subset of
data within the table. This technique is based on a function that will enable SQL execution to append a WHRE clause predicate. The subset of data can be from row-level or column-level perspective.

For demonstration, I will be using a table called EMP under SCOTT schema.

1.PNG

I will also create 2 accounts:
SQL> create user manager identified by mono670;
SQL>grant create session to to manager;
SQL>grant select on SCOTT.EMP to manager;
SQL>create user clerk identified by clerk332;

SQL>grant create session to clerk;
SQL>grant select on SCOTT.EMP to clerk;
connecting as accounts MANAGER & CLERK I can see the full table data !!!

2

3.PNG

Now I will create security administrator account:
SQL> alter session set container=CDB$root;
SQL> create user security_admin identified by zorro3_g;
SQL> grant create session to security_admin;
SQL> grant create procedure to security_admin;
Now I am going to create the function:
SQL> CREATE OR REPLACE FUNCTION VPD_FUNC
(schema_v IN VARCHAR2, tbl_v IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN ‘upper(ename) = SYS_CONTEXT(”USERENV”, ”CURRENT_USER”) OR upper(job) =
SYS_CONTEXT(”USERENV”, ”CURRENT_USER”)’;
END;
/

 

4

Then as a sys user in the pluggable database PDB_ORIGIN configure the policy:
SQL> begin
sys.dbms_rls.add_policy(
object_schema=>’SCOTT’,
object_name=>’EMP’,
policy_name=>’VPD1_POLICY’,
function_schema=>’security_admin’,
policy_function=>’VPD_FUNC’,
statement_types => ‘SELECT’);
end;
/
Connecting as account “manager” I can see data related to “manager” records:

5

Same to “CLERK”

6

This is a very nice security feature that many organizations can use to restrict  access to data within the table itself.