Linux: Hassle with “mail”-command

I just had a little annoying trouble with “mail”. Despite it’s silly reason I will post it here to keep others from having to spend unnecessarily time on it.

I wanted to mail a logfile via “mail” from my Linux-Box. Easy, one might think. ;o)
But I got stuck with this error:

WARNING: RunAsUser for MSP ignored, check group ids (egid=3270, want=51)
can not chdir(/var/spool/clientmqueue/): Permission denied
Program mode requires special privileges, e.g., root or TrustedUser.

I used this line in my script:

echo "Monitor-Log `date`" | /bin/mail -s "Monitor-Log" -a /some/dir/monitor.log $RECIPIENTS -c $CCRECIPIENTS

The reason for the error was the sequence of the variables holding the mail-recipients (“$RECIPIENTS” and “$CCRECIPIENTS”). The last parameter to “mail” must ever be the final recipient. So all “CC”s have to go before that. The correct line is this:

echo "Monitor-Log `date`" | /bin/mail -s "Monitor-Log" -a /some/dir/monitor.log -c $CCRECIPIENTS $RECIPIENTS

As the given error-message is not straight for the root, I think this post was worth written.

Oracle: “ORA-28221: REPLACE not specified” on password-change

If we apply a PASSWORD_VERIFY_FUNCTION to a user’s profile, we would need a little more syntax than usual to change the users’s password. A simple “ALTER USER username IDENTIFIED BY password;” for most users would not work:

SQL> conn test/test123@testdb
Connected.

SQL> alter user test identified by QwertzQwertz2014;
alter user test identified by QwertzQwertz2014
*
ERROR at line 1:
ORA-28221: REPLACE not specified

We have to add a “REPLACE old_password” to our command:

SQL> alter user test identified by QwertzQwertz2014 replace test123;

User altered.

SQL>

This “REPLACE” can only be omitted, if the executing user has the “ALTER USER” system privilege or if a PASSWORD_VERIFY_FUNCTION is not in use. In the presence of a PASSWORD_VERIFY_FUNCTION the REPLACE can only be omitted if the user changes his/her password for the first time.

This is important to keep in mind for cases when we want the password to be set via an application. Here the application’s logic must be able to handle the above.

Reference: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4003.htm#SQLRF53632

Edit 2014-08-27:
It took some time for me to grasp the logic behind this syntax – but now I think I got it:

As the users passwords are always stored encrypted / hashed in the database, there would be otherwise no chance for a PASSWORD_VERIFY_FUNCTION to check the new password for a minimum number of changed characters compared to the old password.

If the new password would differ in at least one single character, the hash-value of that password would be completely different to the hash of the old password – so no use for the aforementioned check. That way we could just check for “NEW_PWD != OLD_PWD”. Since there is no profile limit for a minimum number of changed password-characters, the only way to check this is in a PASSWORD_VERIFY_FUNCTION – and hence it makes sense to just request the old password ( or request the old password at all ) in case we use a PASSWORD_VERIFY_FUNCTION.

Just verified this on My Oracle Support (MOS) and found it confirmed under
“Password Verify Function Not Enforcing Difference Between Old and New Passwords” (Doc ID 816932.1)

Oracle: Deleting child-record-referenced parent-rows

Foreign key references/constraints are a neat thing to keep your data’s integrity. But it can be a pain if you need to delete records in a parent-table.

Unless you have identified and deleted all referencing child-records, the DB won’t let you delete the parent-records. To identify the children you need to know the foreign-key-structure put on that parent-table.

Figure out which child-tables are referencing your parent-table:

SELECT a.owner CHILD_OWNER,
  a.constraint_name CHILD_CONSTRAINT,
  c.COLUMN_NAME CHILD_COLUMN,
  a.table_name CHILD_TABLE,
  a.r_owner PARENT_OWNER,
  a.r_constraint_name PARENT_REF_NAME,
  d.column_name PARENT_COLUMN,
  b.table_name AS PARENT_TABLE,
  a.delete_rule
FROM dba_constraints a,
  dba_constraints b,
  dba_cons_columns c,
  dba_cons_columns d
WHERE 
a.constraint_type='R' 
AND a.r_constraint_name=b.constraint_name
AND a.constraint_name  =c.constraint_name
AND b.constraint_name  =d.constraint_name
and a.owner='TEST'
ORDER BY PARENT_TABLE;

Then you can drill down by the references to the child-records and delete them first.

If your circumstances (applications, user) would allow, it would perhaps by easier to “alter” all your foreign-keys with “ON DELETE CASCADE”.
Unfortunately you can not simply modify them to get that enabled. You’ll have to drop and re-create them with “ON DELETE CASCADE”.

alter table t2 drop constraint T2_FK1;
alter tablet2 add constraint T2_FK1 foreign key (dept) references t1 (dept) on delete cascade;

But mind to lock the db for your users prior to drop the constraints or else you could mess up your data-integrity!

At a first glance I wondered, if it wouldn’t be wise to always use the CASCADE-option – until I deleted my first parent-row.

SQL> select * from t1;

      DEPT
----------
       102
       103
       104
       105
       107

5 rows selected.

SQL> select * from t2;

    USERID       DEPT
---------- ----------
        11        105
         2        102
         3        103
         4        104

4 rows selected.

SQL> delete from t1 where dept=102;

1 row deleted.

SQL>

The DB gave no information about the implicitly deleted child-record! This could lead to some problems as Laurent Schneider explained here very nice (read John Flack’s comment there too):

http://laurentschneider.com/wordpress/2007/08/on-delete-cascade.html