how lock expired user using procedure in aws oracle rds

Question

Hello,

Attempting to lock expired users in AWS Oracle RDS using a created procedure faces an "insufficient privileges" error. The procedure, designed to execute ALTER USER commands, encounters privilege issues despite being created by a master user. The code provided showcases the procedure encountering the error, along with the error details

SHARING PROCEDURE AS BELWO :
  CREATE OR REPLACE PROCEDURE lock_expired_users AS
BEGIN
  FOR user_rec IN (SELECT username FROM dba_users WHERE expiry_date < SYSDATE AND PROFILE='USER_ACCT') LOOP
    EXECUTE IMMEDIATE 'ALTER USER ' || user_rec.username || ' account lock password expire';
	DBMS_OUTPUT.PUT_LINE('ALTER USER ' || user_rec.username || ' account lock password expire');
  END LOOP;
END;
/

ERROR :
ORA-01031: insufficient privileges
ORA-06512: at "ECDBINSTALL.LOCK_EXPIRED_USERS", line 5
ORA-06512: at "ECDBINSTALL.LOCK_EXPIRED_USERS", line 5
ORA-06512: at line 1

Answer

Hello Rajdeep,  
Logged in as ADMIN, I am able to create this procedure in my RDS instance. See below:

SQL> 	l

1  CREATE OR REPLACE PROCEDURE lock_expired_users AS  
2  BEGIN  
3     FOR user_rec IN (SELECT username FROM dba_users WHERE expiry_date < SYSDATE AND PROFILE='USER_ACCT')  
4     LOOP  
5          EXECUTE IMMEDIATE 'ALTER USER ' || user_rec.username || ' account lock password expire';  
6          DBMS_OUTPUT.PUT_LINE('ALTER USER ' || user_rec.username || ' account lock password expire');  
7     END LOOP;  
8* END;  
SQL> /

Procedure created.

You may want to check your session privileges (by running select * from session_privs) and see what privilege is lacking. Maybe if it is not immediately clear, you could run the same sql (select * from session_privs) from an ADMIN user and compare the output with that from your other user that is trying to create this procedure.

Hope this helps.

how lock expired user using procedure in aws oracle rds

Relevant content