I've successfully moved my Oracle 11g database to Oracle 23AI. All data, functions, procedures, and packages I've developed over the years have been successfully compiled. All user accounts have been recreated and Connect, Create Session granted to them (along with a role that I defined). I did that with this code:
create or replace PROCEDURE CREATEUSERS AS
BEGIN
FOR userdata IN (select username,password from users)
LOOP
secureprocs.createaccount(userdata.username,userdata.password);
END LOOP;
END CREATEUSERS;
SecureProcs Code:
PROCEDURE CreateAccount(pUsername IN VARCHAR2, pPassword IN VARCHAR2) IS
vSql VARCHAR2(4000);
UserName VARCHAR2(4000);
BEGIN
UserName := USER();
vSql := 'Create USER "' || UPPER(pUserName) || '" Identified by "' || UPPER(pPassword) || '" DEFAULT TABLESPACE "USERS"';
EXECUTE IMMEDIATE vSql;
COMMIT;
vSql := 'GRANT "CONNECT" TO "' || UPPER(pUserName) || '"';
EXECUTE IMMEDIATE vSql;
vSql := 'GRANT "SIGNUP" TO "' || UPPER(pUserName) || '"';
COMMIT;
vSql := 'GRANT “CREATE SESSION TO ” UPPER(pUserName) || '"';
EXECUTE IMMEDIATE vSql;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20667,'An Error occured in the CreateAccount Procedure, the error is: ' || SQLERRM);
END;
I executed the procedure successfully and all users were created. Now I'm moving my asp.net applications to the server and testing them. For some reason, a user cannot log on, the error returned is “ORA-01017: invalid credential or not authorized; logon denied”. So I checked the user privileges by executing the following PL/SQL:
select * from USER_ROLE_PRIVS where USERNAME='<username>';
select * from USER_TAB_PRIVS where Grantee = '<username>';
select * from USER_SYS_PRIVS where USERNAME = '<username>';
No rows are returned for each statement which indicates to me that the user is properly set up. But when I scroll down in the object browser on the left of the Sql Developer screen and expand users and select the offending username, editing the user shows a check mark for Connect, Create Session and the role I defined to give access to all the tables, functions, procedures, and packages the user would need to navigate the app. Keep in mind that this app is fully functional on the Oracle 11g database and is in production now, so there must be something about the Oracle 23AI database I don't understand because the user HAS been granted all the privileges but they apparently don't if one queries the database. I'm very confused, what is going on here?