This is why we can't have nice things...

This is probably old hat for most of my audience but I needed to get this down on paper (or bits actually) for posterity... The granularity of Oracle privileges leaves a lot to be desired. Today I had a requirement for an application program to reset passwords. I can understand this but the dev team asked for ALTER USER so they could let their application reset passwords.

[oracle@localhost ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jul 3 06:19:45 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user dave identified by dave;

User created.

SQL> grant connect, alter user to dave;

Grant succeeded.

SQL> connect dave/dave 
Connected.
SQL> alter user sys identified by muahaha;

User altered.

SQL> connect sys/muahaha as sysdba;
Connected.
SQL> drop user person_i_am_not_fond_of cascade;
SQL> drop tablespace tablespace_i_am_not_fond_of including contents and datafiles;
...

Well you get the idea. Now the user has access to a whole host of privileges, many of which I wish I never had even as a DBA.

The argument can be made for auditing... but would you leave the barn door open and then get upset when the cow decides to head out to pasture for a snack?

The current workaround is to write a Definer's Rights procedure owned by a privileged user to allow this... Will cover that in a future post but wanted to make sure we had a compelling reason why... I would say taking over SYS is a compelling reason to not give out ALTER USER privs like they are candy. OK time to take off my Cranky DBA hat now :)