Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Alter user statement in MS Access

    I originally posted this in the Access forum but was told that the Oracle forum could possibly help me with this.

    I am trying to write a routine that will update a user's ID in the Oracle dba_users table. I have a table in Access that I spin through, using the user ID and the password in this table in the "Alter User" SQL statement.

    The problem is that I keep getting a message saying there is a syntax problem with the Alter statement. I don't know if I can even run this type of SQL from Access.

    Code:
    Public Sub AlterUser()
    
    ' This will spin through each and every record in the tbl_Users table and will update
    ' the dba_users table in Oracle with the newly created passwords.
    
    'Defining variables
    Dim cnOra As ADODB.Connection
    Dim rsOra As ADODB.Recordset
    
    Set cnOra = New ADODB.Connection
    Set rsOra = New ADODB.Recordset
    
    Dim db As Database
    Dim SQLstr As String
    Dim rst As DAO.Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tbl_Users")
    
    cnOra.Open "driver={Oracle in OraHome92};" & "dbq=NATT1;" & "uid=tmmgr;pwd=manutm1"
    
    rst.MoveFirst
    
    'Run the alter user query for all users
    Do Until rst.EOF
        
        SQLstr = "alter user " & rst!ManuUserName & " identified by " & rst!Password1 & ";"
        
        db.Execute SQLstr
        
        rst.MoveNext
    Loop
    
    rsOra.Close
    cnOra.Close
    Set rsOra = Nothing
    
    End Sub

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    ABsolutly forbidden. You may not alter the userid in the dba_view. That id number is the link to tie all the user information together. NEVER alter the data in any system table.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't speak Access, but - your code doesn't match your words.

    Words: write a routine that will update a user's ID in the Oracle dba_users table.

    Code: "alter user " & rst!ManuUserName & " identified by " & rst!Password1 & ";"

    This ALTER USER statement will only change its password, nothing more. Correct Oracle SQL syntax is

    ALTER USER user_name IDENTIFIED BY password;

    Now, this code seems to be OK, but - as I've already said - I don't know Access and have no idea whether this concatenation works or not. Perhaps there's a problem with Access code, not Oracle ALTER USER statement?

    Can you print out final "SQLstr" value, before executing it with "db.Execute" command? So that you could see whether it really looks OK or not.

    Finally, I absolutely agree with Bill - never EVER mess up with data in SYSTEM owned tables.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If what you are doing is to perform mass updates of passwords, then your command will not work. Oracle doesn't allow alter commands (or any other DDL commands) to be issued in sql. If you want to do mass password changes then I would suggest using execute immediate in an oracle procedure. Also you can only change the password for another account if you are connected as a DBA user. And be VERY careful that you do NOT change any of the oracle system accounts (SYS,SYSTEM) or you could find yourself locked out.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •