set password life time to unlimited in Oracle

by | Mar 11, 2013 | Oracle, RDBMS

While developing Icinga, I also need to test the Oracle implementation in IDOUtils which then leads me to my fancy XE install (which is stripped down to low-mem and is only started on demand)

So, I wasn’t really expecting that I should change my password in 7 days.

sol /etc/icinga # rlwrap sqlplus icinga@XE

SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 22:34:42 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-28002: the password will expire within 7 days

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>

Time for some sysdba magic

sol /etc/icinga # rlwrap sqlplus "sys@XE as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 22:44:46 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL

Get to know which profile the icinga user is using

SQL> SELECT profile FROM dba_users WHERE username='ICINGA';

PROFILE
--------------------------------------------------------------------------------
DEFAULT

Now check the password lifetime of the default profile.

SQL> SELECT resource_name,limit FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

Alter it to unlimited (note: it’s a local dev oracle xe install!).

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Though, this doesn’t fix the icinga user being gracefully expired already.

SQL> SELECT account_status FROM dba_users WHERE username='ICINGA';

ACCOUNT_STATUS
--------------------------------------------------------------------------------
EXPIRED(GRACE)

But updating this manually to the exact same password will resolve the issue.

SQL> ALTER USER ICINGA IDENTIFIED BY XXXXX;

User altered.

SQL> SELECT account_status FROM dba_users WHERE username='ICINGA';

ACCOUNT_STATUS
--------------------------------------------------------------------------------
OPEN

Now quit the sysdba session and re-login as icinga user.

sol /etc/icinga # rlwrap sqlplus icinga@XE

SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 22:56:38 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>

Voilà – Oracle beaten again 🙂

(Oh, and make sure to use rlwrap all the time. Native sqlplus just sucks on tab completion and so on).

Recover MySQL root password on Debian

by | Aug 25, 2012 | *nix, Debian, MySQL, RDBMS

Debian is pretty nice in providing a maintenance user, go to /etc/mysql/debian.cnf and locate the password. Given that, login as debian-sys-maint user.

# mysql -udebian-sys-maint -p

mysql> UPDATE mysql.user SET Password=PASSWORD('yournewpassword') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> q

For passwordless root login, you might put the updated password into the .my.cnf file.

# vim /root/.my.cnf

[client]
password = yournewpassword

getting a postgresql execution plan

by | Feb 27, 2012 | Postgresql, RDBMS

Like described here, prepared statements are also interesting to get analyzed by their execution plan. This is especially interesting to identify long lasting queries whilst developing Icinga with Postgresql.

A normal statement would look like this.

icinga=# EXPLAIN SELECT 1;
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)
(1 Zeile)

First cost is startup, second cost if all rows are retrieved.

A prepared statement with bind parameters can be called with an actual value, but keep in mind that the cost is calculated on the prepared statement instead of the actual value. To fetch the execution plan on the actual value, see below with analyze.

icinga=# PREPARE stmt(int) AS SELECT $1;

icinga=# EXPLAIN EXECUTE stmt(1);
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)
(1 Zeile)

On explain, you can use verbose as well.

icinga=# EXPLAIN VERBOSE EXECUTE stmt(1);
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)
   Output: $1
(2 Zeilen)

analyze will execute the query – for selects this will be rather harmless, but on insert/delete/update this may cause harm to your data! better wrap the test within a begin rollback call.
executing the query will explain the actual query, it’s runtime and results. this can be useful to track down wrongly expected outputs.

icinga=# BEGIN;

icinga=# EXPLAIN ANALYZE EXECUTE stmt(1);
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.004..0.005 rows=1 loops=1)
 Total runtime: 0.057 ms
(2 Zeilen)

icinga=# ROLLBACK;