Oracle Tips and Tricks — David Fitzjarrell

February 11, 2015

What’s The Password?

Filed under: General — dfitzjarrell @ 14:29

A very interesting question was posted in one of the forums I participate in:


is there any way how I can revoke the right from a normal user to change it's own password in the database?

This, obviously, sparked a fairly lively thread (which I will not re-post here) regarding security and the underlying reason such a request was made. The user who posted the original question continued in the thread to explain:


Of course the user should access his own data but I'm trying to prevent that the user can change his own password as he likes. It is part of the security awarness of my company 
and I'm searching for a way to realize that.

This raised more questions and commentary, including the fact that not allowing a user to change his or her own password really implements less security because someone else (the DBA) needs to set it. Any time a password needs to be reset by an administrative user security is compromised, in my opinion. Of course it’s going to be necessary at some point for the DBA to intervene and reset a user’s password — usually when said user has forgotten the password. At those times the DBA will reset the password and force the user to change it to something new at the next login, thus preserving security since no one but the affected user will know his or her password. But, some people are hard to convince.


I think it is more safe, when the "stupid" user can't change his password. He has to raise an incident and the DBA then will change the password

This continued on for a while with various forum members adding comments. So CAN a user be prevented from changing his or her own password and still preserve the ability for the DBA to do so? It appears not, as Pete Finnigan reported back in 2008. He tried using a trigger to generate an error whenever an ALTER USER command was submitted. Why ALTER USER? Let’s find out.

When a user is created in Oracle a curious thing happens; that user automatically, and silently, receives ALTER USER privileges for his or her own account, without an explicit grant. There is nothing to revoke, so that won’t disable the functionality:


SQL> revoke alter user from blorbo;
revoke alter user from blorbo
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'BLORBO'


SQL>

The password function uses ALTER USER behind the scenes to effect the password change. Pete thought that using a trigger would solve the dilemma of a user changing his or her own password, and on one level it does. The trigger is simple, really:


SQL> create or replace trigger alter_trigger
  2  before alter
  3  on database
  4  declare
  5  begin
  6  if (ora_dict_obj_type = 'USER') then
  7  raise_application_error(-20010,'you cannot change your own password');
  8  end if;
  9  end;
 10  /

Trigger created.

SQL>

So far, so good, so let’s test this on a sample user account:


SQL> connect blorbo/#########
Connected.
SQL> password
Changing password for BLORBO
Old password:
New password:
Retype new password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20010: you cannot change your own password
ORA-06512: at line 4


Password unchanged
SQL>

Eureka!!! It works!!! Unfortunately it works TOO well, as now SYS and SYSTEM can’t change a user password:


SQL> show user
USER is "SYS"
SQL> alter user blorbo identified by yoopa;
alter user blorbo identified by yoopa
                                *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20010: you cannot change your own password
ORA-06512: at line 4


SQL>

That’s no good; passwords now cannot be changed and when they expire, well, the user loses access to the database which is not the intended result. Dropping the trigger restores the ability of SYS and SYSTEM to effect password changes but also opens the ‘self-service password door’ we tried to close for “security” reasons.

Another possibility was presented by John Watson in that thread, a basic function to be used as the password verify function. He posted this:


create or replace function no_self_change
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
begin
if sys_context('userenv','session_user')=username then
return false;
else
return true;
end if;
end;
/

and it does, indeed, work:


SQL> password
Changing password for BLORBO
Old password:
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed


Password unchanged
SQL>
SQL> connect / as sysdba
Connected.
SQL> alter user blorbo identified by yimpski;

User altered.

SQL>

So, we see it is possible to affect the ability for a user to change his or her own database password without affecting the ability of the DBA to do so. But, to be honest, preventing a user from changing their own password is not enforcing security, it’s reducing security by allowing another user (the DBA) to know a user’s password. The DBA should be trustworthy but would YOU want your DBA to know your password? I think not.

Passwords are personal, they should never be shared, and choosing strong passwords is a difficult enough task without adding another person to get the job done. Sometimes what seems like a good security idea at the time turns out to be more of a security risk; not allowing a user to change their own password, in my opinion, falls into that category.

So, there.

Advertisements

1 Comment »

  1. I have a similar problem:

    If I do this, I run in a failure (only have the “UPDATE”-clause):

    MERGE INTO D25571_SYS.DT461 z USING (SELECT
    rtrim(ltrim(a.DI001001)) as DI001001, — Projekt-ID
    rtrim(ltrim(a.DI001003)) as DI001003, — ID übergeordnetes Projekt
    rtrim(ltrim(a.DI001004)) as DI001004, — ID übergeordneter Vorgang
    rtrim(ltrim(a.DI001014)) as DI001014, — Code
    a.DI001015, — Prio
    rtrim(ltrim(a.DI001016)) as DI001016, — Kalender
    a.DI002833, — Splitting
    a.DI001018, — Struktur-Terminrechnung
    a.DI001019, — Wunsch-Anfang
    a.DI001020, — Wunsch-Ende
    a.DI001021, — Frühester Anfang
    a.DI001022, — Frühestes Ende
    a.DI001479, — Spätester Anfang
    a.DI001480, — Spätestes Ende
    a.DI001023, — Kalk. Anfang
    a.DI001024, — Kalk. Ende
    a.DI001025, — Ist-Anfang
    a.DI001026, — Ist-Ende
    a.DI001027, — Planung 1: Anfang
    a.DI001028, — Planung 1: Ende
    a.DI001029, — Planung 1: Abweichung Anfang
    a.DI001030, — Planung 1: Abweichung Ende
    a.DI001031, — Dauer-Soll ZR
    a.DI001032, — Dauer-Ist ZR
    a.DI001033, — Dauer-Rest Zeitrechnung
    a.DI001034, — Dauer-Soll
    a.DI001035, — Dauer-Ist
    a.DI001036, — Dauer-Rest
    a.DI001037, — Aufwand-Soll
    a.DI001038, — Aufwand-Ist
    a.DI001039, — Aufwand-Rest
    a.DI001481, — Rückmeldetermin
    a.DI001040, — Verbrauchter Aufwand
    a.DI001041, — %-erl. Termin
    a.DI001042, — Status
    0 as DI059008, — Requeststatus
    a.DI001043, — Netzplanzyklus
    a.DI001044, — Eingelastet
    a.DI001045, — Datum letzte Terminrechnung
    a.DI002834, — Planung früh
    0 as DI003110, — Checkliste
    a.DI001048, — Anzahl Vorgänge
    a.DI001049, — Anzahl AOB
    a.DI001050, — Datum letzte Kapazitätsrechnung
    a.DI022744, — Uhrzeit letzte Kapazitätsrechnung
    a.DI001617, — Anfangstermin-Lage
    rtrim(ltrim(a.DI005352)) as DI005352, — Produkt
    a.DI001278, — Planung 2: Anfang
    a.DI001286, — Planung 2: Ende
    a.DI001280, — Planung 3: Anfang
    a.DI001287, — Planung 3: Ende
    a.DI001284, — Planung 4: Anfang
    a.DI001288, — Planung 4: Ende
    a.DI001285, — Letzte Planung: Anfang
    a.DI001289, — Letzte Planung: Ende
    a.DI001290, — Planung 2: Abweichung Anfang
    a.DI001294, — Planung 2: Abweichung Ende
    a.DI001291, — Planung 3: Abweichung Anfang
    a.DI001295, — Planung 3: Abweichung Ende
    a.DI001292, — Planung 4: Abweichung Anfang
    a.DI001296, — Planung 4: Abweichung Ende
    a.DI001293, — Letzte Planung: Abweichung Anfang
    a.DI001297, — Letzte Planung: Abweichung Ende
    rtrim(ltrim(a.DI001052)) as DI001052, — Hauptprojekt-ID
    a.DI001053, — Ebene
    rtrim(ltrim(a.DI004394)) as DI004394, — Letzte Kopiervorlage
    rtrim(ltrim(a.DI005360)) as DI005360, — SAP-Leistungsart
    rtrim(ltrim(a.DI001058)) as DI001058, — Klasse
    rtrim(ltrim(a.DI000195)) as DI000195, — Land
    a.DI000202, — Ort
    a.DI009000, — Kostenstelle: Kosten-Soll
    a.DI009001, — Kostenstelle: Kosten-Ist
    a.DI009003, — Kostenstelle: Kosten-Rest
    a.DI009004, — Kostenstelle: Kosten-Gesamt
    a.DI022745, — Uhrzeit letzte Zeitrechnung
    rtrim(ltrim(a.DI001061)) as DI001061, — Kunde
    rtrim(ltrim(a.DI001062)) as DI001062, — Manager
    rtrim(ltrim(a.DI001063)) as DI001063, — Kundenmanager
    a.DI001067, — Wiedervorlage
    rtrim(ltrim(a.DI001068)) as DI001068, — Freigabe
    a.DI009034, — Planbudget
    a.DI009070, — Plan eigenes Budget
    a.DI009071, — Verdichtetes Planbudget
    a.DI009141, — Verteiltes Planbudget Kostenstelle
    –a.DI000634, — Kostenbudget
    a.DI009142, — Eigenes Budget verteilt gepllant
    a.DI009032, — Eigenes Budget genehmigt
    a.DI009033, — Budget verdichtet genehmigt
    a.DI009143, — Kostenstellenbudget verteilt genehmigt
    a.DI009144, — Eigenes Budget verteilt genehmigt
    rtrim(ltrim(a.DI009311)) as DI009311, — Aktiver Benutzer
    a.DI000661, — Kosten-Soll
    a.DI000662, — Kosten-Ist
    a.DI000673, — Kosten-Rest
    a.DI000674, — Kosten-Gesamt
    a.DI000688, — Abweichung Aufwand-Gesamt-Soll
    a.DI000689, — %-Abweichung Aufwand-Gesamt-Soll
    rtrim(ltrim(a.DI560559)) as DI560559, — Projektbezeichnung
    rtrim(ltrim(a.DI560560)) as DI560560, — Projektbezeichnung 2
    rtrim(ltrim(a.DI560561)) as DI560561, — Gruppe 1
    0 as DI000693, — Gruppe 2
    0 as DI000694, — Gruppe 3
    rtrim(ltrim(a.DI000260)) as DI000260, — Kurze Bezeichnung
    rtrim(ltrim(a.DI000960)) as DI000960, — OLE
    null as DI057890, — Release-Projekt-ID
    rtrim(ltrim(a.DI000964)) as DI000964, — Symbol -OLE
    rtrim(ltrim(a.DI002377)) as DI002377, — Symbol
    rtrim(ltrim(a.DI002716)) as DI002716, — Adresse
    rtrim(ltrim(a.DI002924)) as DI002924, — Dokumenten-Verzeichnis
    a.DI000141, — Gesperrt
    a.DI005433, — Planung 1: Aufwand
    a.DI005434, — Planung 2: Aufwand
    a.DI005435, — Planung 3: Aufwand
    a.DI005436, — Planung 4: Aufwand
    a.DI005437, — Letzte Planung: Aufwand
    a.DI005438, — Planung 1: Kosten
    a.DI005439, — Planung 2: Kosten
    a.DI005440, — Planung 3: Kosten
    a.DI005441, — Planung 4: Kosten
    a.DI005442, — Letzte Planung: Kosten
    a.DI005443, — Planung 1 fixiert am
    a.DI005444, — Planung 2 fixiert am
    a.DI005445, — Planung 3 fixiert am
    a.DI005446, — Planung 4: fixiert am
    a.DI005447, — Letzte Planung: fixiert am
    rtrim(ltrim(a.DI005448)) as DI005448, — Planung 1: fixiert von
    rtrim(ltrim(a.DI005449)) as DI005449, — Planung 2: fixiert von
    rtrim(ltrim(a.DI005450)) as DI005450, — Planung 3: fixiert von
    rtrim(ltrim(a.DI005451)) as DI005451, — Planung 4: fixiert von
    rtrim(ltrim(a.DI005452)) as DI005452, — Letzte Planung: fixiert von
    a.DI006445, — Planungsart
    rtrim(ltrim(a.DI006515)) as DI006515, — Managereinschätzung Gesamtprojekt
    a.DI006516, — Managereinschätzungsdatum Gesamtprojekt
    a.DI006801, — %-fachlich–%-Std. 461
    a.DI006802, — Termin fachliche Verdichtung
    a.DI006926, — Aufwandsbudget
    a.DI007568, — versendet
    rtrim(ltrim(a.DI007786)) as DI007786, — Kostenstelle
    a.DI007787, — Einsparung 1. Jahr
    a.DI007788, — Einsparung 2. Jahr
    a.DI007789, — Einsparung 3. Jahr
    a.DI007790, — Einsparung 4. Jahr
    a.DI007791, — Einsparung 5. Jahr
    0 as DI007792, — Aktuelle Phase
    0 as DI007793, — Grösse
    a.DI007794, — Zielerreichung
    null as DI007795, — Klasse
    rtrim(ltrim(a.DI007796)) as DI007796, — Risiko
    rtrim(ltrim(a.DI007797)) as DI007797, — Strategiebeitrag
    rtrim(ltrim(a.DI007798)) as DI007798, — Nutzen
    a.DI001013 — Any
    FROM D25570_SYS.DT461 a
    ) q
    ON (q.DI001001 = z.DI001001)

    WHEN matched THEN UPDATE SET
    z.DI001003 = q.DI001003 , — ID übergeordnetes Projekt
    z.DI001004 = q.DI001004 , — ID übergeordneter Vorgang
    z.DI001013 = q.DI001013 — Any
    ;

    The failure is:
    SQL-Fehler: ORA-01792: Höchstzahl für Spalten in einer Tabelle oder einer View ist 1000
    01792. 00000 – “maximum number of columns in a table or view is 1000”
    *Cause: An attempt was made to create a table or view with more than 1000
    columns, or to add more columns to a table or view which pushes
    it over the maximum allowable limit of 1000. Note that unused
    columns in the table are counted toward the 1000 column limit.
    *Action: If the error is a result of a CREATE command, then reduce the
    number of columns in the command and resubmit. If the error is
    a result of an ALTER TABLE command, then there are two options:
    1) If the table contained unused columns, remove them by executing
    ALTER TABLE DROP UNUSED COLUMNS before adding new columns;
    2) Reduce the number of columns in the command and resubmit.

    If I do the same but with “UPDATE and INSERT”-clause, I receive no failure:

    MERGE INTO D25571_SYS.DT461 z USING (SELECT
    rtrim(ltrim(a.DI001001)) as DI001001, — Projekt-ID
    rtrim(ltrim(a.DI001003)) as DI001003, — ID übergeordnetes Projekt
    rtrim(ltrim(a.DI001004)) as DI001004, — ID übergeordneter Vorgang
    rtrim(ltrim(a.DI001014)) as DI001014, — Code
    a.DI001015, — Prio
    rtrim(ltrim(a.DI001016)) as DI001016, — Kalender
    a.DI002833, — Splitting
    a.DI001018, — Struktur-Terminrechnung
    a.DI001019, — Wunsch-Anfang
    a.DI001020, — Wunsch-Ende
    a.DI001021, — Frühester Anfang
    a.DI001022, — Frühestes Ende
    a.DI001479, — Spätester Anfang
    a.DI001480, — Spätestes Ende
    a.DI001023, — Kalk. Anfang
    a.DI001024, — Kalk. Ende
    a.DI001025, — Ist-Anfang
    a.DI001026, — Ist-Ende
    a.DI001027, — Planung 1: Anfang
    a.DI001028, — Planung 1: Ende
    a.DI001029, — Planung 1: Abweichung Anfang
    a.DI001030, — Planung 1: Abweichung Ende
    a.DI001031, — Dauer-Soll ZR
    a.DI001032, — Dauer-Ist ZR
    a.DI001033, — Dauer-Rest Zeitrechnung
    a.DI001034, — Dauer-Soll
    a.DI001035, — Dauer-Ist
    a.DI001036, — Dauer-Rest
    a.DI001037, — Aufwand-Soll
    a.DI001038, — Aufwand-Ist
    a.DI001039, — Aufwand-Rest
    a.DI001481, — Rückmeldetermin
    a.DI001040, — Verbrauchter Aufwand
    a.DI001041, — %-erl. Termin
    a.DI001042, — Status
    0 as DI059008, — Requeststatus
    a.DI001043, — Netzplanzyklus
    a.DI001044, — Eingelastet
    a.DI001045, — Datum letzte Terminrechnung
    a.DI002834, — Planung früh
    0 as DI003110, — Checkliste
    a.DI001048, — Anzahl Vorgänge
    a.DI001049, — Anzahl AOB
    a.DI001050, — Datum letzte Kapazitätsrechnung
    a.DI022744, — Uhrzeit letzte Kapazitätsrechnung
    a.DI001617, — Anfangstermin-Lage
    rtrim(ltrim(a.DI005352)) as DI005352, — Produkt
    a.DI001278, — Planung 2: Anfang
    a.DI001286, — Planung 2: Ende
    a.DI001280, — Planung 3: Anfang
    a.DI001287, — Planung 3: Ende
    a.DI001284, — Planung 4: Anfang
    a.DI001288, — Planung 4: Ende
    a.DI001285, — Letzte Planung: Anfang
    a.DI001289, — Letzte Planung: Ende
    a.DI001290, — Planung 2: Abweichung Anfang
    a.DI001294, — Planung 2: Abweichung Ende
    a.DI001291, — Planung 3: Abweichung Anfang
    a.DI001295, — Planung 3: Abweichung Ende
    a.DI001292, — Planung 4: Abweichung Anfang
    a.DI001296, — Planung 4: Abweichung Ende
    a.DI001293, — Letzte Planung: Abweichung Anfang
    a.DI001297, — Letzte Planung: Abweichung Ende
    rtrim(ltrim(a.DI001052)) as DI001052, — Hauptprojekt-ID
    a.DI001053, — Ebene
    rtrim(ltrim(a.DI004394)) as DI004394, — Letzte Kopiervorlage
    rtrim(ltrim(a.DI005360)) as DI005360, — SAP-Leistungsart
    rtrim(ltrim(a.DI001058)) as DI001058, — Klasse
    rtrim(ltrim(a.DI000195)) as DI000195, — Land
    a.DI000202, — Ort
    a.DI009000, — Kostenstelle: Kosten-Soll
    a.DI009001, — Kostenstelle: Kosten-Ist
    a.DI009003, — Kostenstelle: Kosten-Rest
    a.DI009004, — Kostenstelle: Kosten-Gesamt
    a.DI022745, — Uhrzeit letzte Zeitrechnung
    rtrim(ltrim(a.DI001061)) as DI001061, — Kunde
    rtrim(ltrim(a.DI001062)) as DI001062, — Manager
    rtrim(ltrim(a.DI001063)) as DI001063, — Kundenmanager
    a.DI001067, — Wiedervorlage
    rtrim(ltrim(a.DI001068)) as DI001068, — Freigabe
    a.DI009034, — Planbudget
    a.DI009070, — Plan eigenes Budget
    a.DI009071, — Verdichtetes Planbudget
    a.DI009141, — Verteiltes Planbudget Kostenstelle
    –a.DI000634, — Kostenbudget
    a.DI009142, — Eigenes Budget verteilt gepllant
    a.DI009032, — Eigenes Budget genehmigt
    a.DI009033, — Budget verdichtet genehmigt
    a.DI009143, — Kostenstellenbudget verteilt genehmigt
    a.DI009144, — Eigenes Budget verteilt genehmigt
    rtrim(ltrim(a.DI009311)) as DI009311, — Aktiver Benutzer
    a.DI000661, — Kosten-Soll
    a.DI000662, — Kosten-Ist
    a.DI000673, — Kosten-Rest
    a.DI000674, — Kosten-Gesamt
    a.DI000688, — Abweichung Aufwand-Gesamt-Soll
    a.DI000689, — %-Abweichung Aufwand-Gesamt-Soll
    rtrim(ltrim(a.DI560559)) as DI560559, — Projektbezeichnung
    rtrim(ltrim(a.DI560560)) as DI560560, — Projektbezeichnung 2
    rtrim(ltrim(a.DI560561)) as DI560561, — Gruppe 1
    0 as DI000693, — Gruppe 2
    0 as DI000694, — Gruppe 3
    rtrim(ltrim(a.DI000260)) as DI000260, — Kurze Bezeichnung
    rtrim(ltrim(a.DI000960)) as DI000960, — OLE
    null as DI057890, — Release-Projekt-ID
    rtrim(ltrim(a.DI000964)) as DI000964, — Symbol -OLE
    rtrim(ltrim(a.DI002377)) as DI002377, — Symbol
    rtrim(ltrim(a.DI002716)) as DI002716, — Adresse
    rtrim(ltrim(a.DI002924)) as DI002924, — Dokumenten-Verzeichnis
    a.DI000141, — Gesperrt
    a.DI005433, — Planung 1: Aufwand
    a.DI005434, — Planung 2: Aufwand
    a.DI005435, — Planung 3: Aufwand
    a.DI005436, — Planung 4: Aufwand
    a.DI005437, — Letzte Planung: Aufwand
    a.DI005438, — Planung 1: Kosten
    a.DI005439, — Planung 2: Kosten
    a.DI005440, — Planung 3: Kosten
    a.DI005441, — Planung 4: Kosten
    a.DI005442, — Letzte Planung: Kosten
    a.DI005443, — Planung 1 fixiert am
    a.DI005444, — Planung 2 fixiert am
    a.DI005445, — Planung 3 fixiert am
    a.DI005446, — Planung 4: fixiert am
    a.DI005447, — Letzte Planung: fixiert am
    rtrim(ltrim(a.DI005448)) as DI005448, — Planung 1: fixiert von
    rtrim(ltrim(a.DI005449)) as DI005449, — Planung 2: fixiert von
    rtrim(ltrim(a.DI005450)) as DI005450, — Planung 3: fixiert von
    rtrim(ltrim(a.DI005451)) as DI005451, — Planung 4: fixiert von
    rtrim(ltrim(a.DI005452)) as DI005452, — Letzte Planung: fixiert von
    a.DI006445, — Planungsart
    rtrim(ltrim(a.DI006515)) as DI006515, — Managereinschätzung Gesamtprojekt
    a.DI006516, — Managereinschätzungsdatum Gesamtprojekt
    a.DI006801, — %-fachlich–%-Std. 461
    a.DI006802, — Termin fachliche Verdichtung
    a.DI006926, — Aufwandsbudget
    a.DI007568, — versendet
    rtrim(ltrim(a.DI007786)) as DI007786, — Kostenstelle
    a.DI007787, — Einsparung 1. Jahr
    a.DI007788, — Einsparung 2. Jahr
    a.DI007789, — Einsparung 3. Jahr
    a.DI007790, — Einsparung 4. Jahr
    a.DI007791, — Einsparung 5. Jahr
    0 as DI007792, — Aktuelle Phase
    0 as DI007793, — Grösse
    a.DI007794, — Zielerreichung
    null as DI007795, — Klasse
    rtrim(ltrim(a.DI007796)) as DI007796, — Risiko
    rtrim(ltrim(a.DI007797)) as DI007797, — Strategiebeitrag
    rtrim(ltrim(a.DI007798)) as DI007798, — Nutzen
    a.DI001013 — Any
    FROM D25570_SYS.DT461 a
    ) q
    ON (q.DI001001 = z.DI001001)

    WHEN matched THEN UPDATE SET
    z.DI001003 = q.DI001003 , — ID übergeordnetes Projekt
    z.DI001004 = q.DI001004 , — ID übergeordneter Vorgang
    z.DI001013 = q.DI001013 — Any

    WHEN NOT matched THEN INSERT (
    DI001001, — Projekt-ID
    DI001003, — ID übergeordnetes Projekt
    DI001004, — ID übergeordneter Vorgang
    DI001013 — Any
    ) VALUES (
    q.DI001001, — Projekt-ID
    q.DI001003, — ID übergeordnetes Projekt
    q.DI001004, — ID übergeordneter Vorgang
    q.DI001013 — Any
    )
    ;

    Comment by echslea — June 15, 2015 @ 15:47 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: