9 Replies Latest reply: Apr 9, 2001 2:44 PM by 3004 RSS

    Alter User Command in PL/SQL

    3004
      Hi,

      This may sound like a stupid Q, but I cannot find any documentation on this, so I'm asking the experts...

      I want to create an after insert trigger (as SYSTEM) on a SYSTEM-owned table which will update a user's password. Between the Begin/End section I just have an ALTER USER new.username IDENTIFIED BY VALUES 'new.password'; but the trigger isn't being created. No error message either.

      Is ALTER USER allowed in a trigger PL/SQL block or not? I know transaction control statements are not. TIA for any light you can shed on this.
        • 1. Alter User Command in PL/SQL
          3004
          Brad,

          DDL does an implicit commit. You must create the trigger as an autonomous transaction for this to work. should like something like:

          create table some_table
          (username varchar2(30),
          password varchar2(30)
          )
          /

          CREATE or replace TRIGGER some_trigger
          after INSERT ON some_table FOR EACH ROW
          DECLARE
          PRAGMA AUTONOMOUS_TRANSACTION;
          BEGIN
          execute immediate 'alter user ' | | :new.username
          | | 'identified by ' | | :new.password;

          END;
          /
          null
          • 2. Alter User Command in PL/SQL
            3004
            Thanks A LOT, David! I'll give it a try today.
            • 3. Alter User Command in PL/SQL
              3004
              Hi David,

              Well, I should have mentioned this is a 7.3.4 database. It will be moving to 8.1.6 this summer, but for now, no.

              I'm wondering if I can call DBMS_SQL.EXECUTE from the trigger to do something similar?

              Thanks again.

              --Brad
              null
              • 4. Alter User Command in PL/SQL
                3004
                Hi,

                I seem to be getting closer to what I'm looking for, but still no kewpie-doll...

                The following trigger compiles without error,
                but nothing seems to happen when I insert a row into the table (syncpass). No error messages either in dba_errors or user_errors.

                --Brad
                -------------------------------------------
                /* This System-owned TRIGGER in repl instance */

                CREATE OR REPLACE TRIGGER syncpass_ins
                AFTER INSERT ON syncpass
                FOR EACH ROW
                DECLARE
                v_cursor integer;
                v_exec integer;
                v_username varchar2(30);
                v_password varchar2(30);
                v_sql_string varchar2(200);
                BEGIN
                v_cursor := DBMS_SQL.OPEN_CURSOR;
                v_username := ':p_username';
                v_password := ''':p_password''';
                v_sql_string := 'ALTER USER '| |v_username| |' IDENTIFIED BY VALUES '| |v_password;
                DBMS_OUTPUT.PUT_LINE(v_sql_string);
                DBMS_SQL.PARSE(v_cursor,v_sql_string,DBMS_SQL.NATIVE);
                DBMS_SQL.BIND_VARIABLE(v_cursor,'p_username',:new.t_username);
                DBMS_SQL.BIND_VARIABLE(v_cursor,'p_password',:new.t_password);
                v_exec := DBMS_SQL.EXECUTE(v_cursor);
                DBMS_SQL.CLOSE_CURSOR(v_cursor);
                EXCEPTION
                when others then
                DBMS_SQL.CLOSE_CURSOR(v_cursor);
                END;
                /
                null
                • 5. Alter User Command in PL/SQL
                  3004
                  The problem is the DDL does a commit. You cannot do this unless it is autonomous transaction. So you are hosed till you upgrade your DB.

                  Alternatively, you can try scheduling this as a DBMS_JOB to run a second or two later. that should fix it. Have the trigger post the job.
                  • 6. Alter User Command in PL/SQL
                    3004
                    <BLOCKQUOTE><font size="1" face="Verdana, Arial">quote:</font><HR>Originally posted by David Knox (dknox@us.oracle.com):

                    ...Alternatively, you can try scheduling this as a DBMS_JOB to run a second or two later. that should fix it. Have the trigger post the job.
                    <HR></BLOCKQUOTE>

                    Does the implied commit restriction apply to executing a stored procedure? Can my trigger call a stored procedure which in turn does the ALTER USER?

                    Also, I always thought an ALTER USER command was DML, not DDL. Is this restricted as well in a 7.3.4 trigger? Thanks for all your help.
                    null
                    • 7. Alter User Command in PL/SQL
                      3004
                      alter user is not DML. The procedure called by the trigger cannot commit either. Your trigger should submit the procedure as a job to run asychnronously. Then the procedure executes within a different transaction (obvious implications), but it will be able to do the alter user.
                      • 8. Alter User Command in PL/SQL
                        3004
                        <BLOCKQUOTE><font size="1" face="Verdana, Arial">quote:</font><HR>Originally posted by David Knox (dknox@us.oracle.com):
                        alter user is not DML. The procedure called by the trigger cannot commit either. Your trigger should submit the procedure as a job to run asychnronously. Then the procedure executes within a different transaction (obvious implications), but it will be able to do the alter user.<HR></BLOCKQUOTE>

                        I see what you mean now. Sorry for being a knucklehead. One final Q (I promise!)...
                        Since DBMS_JOB.RUN() is considered part of the current session, is that also restricted in performing DDL and in the stored procedure, do I need to include a "commit;" for the ALTER USER or not?

                        Sincerely appreciate your help with this.

                        --Brad
                        null
                        • 9. Alter User Command in PL/SQL
                          3004
                          Brad,

                          correct package, but wrong procedure. You need:
                          DBMS_JOB.SUBMIT (
                          job OUT BINARY_INTEGER,
                          what IN VARCHAR2,
                          next_date IN DATE DEFAULT sysdate,
                          interval IN VARCHAR2 DEFAULT 'null',
                          no_parse IN BOOLEAN DEFAULT FALSE,
                          instance IN BINARY_INTEGER DEFAULT any_instance,
                          force IN BOOLEAN DEFAULT FALSE);


                          and set the next_date to a couple of seconds from now.