how lock expired user using procedure in aws oracle rds

0

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

asked 4 months ago684 views
1 Answer
1

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.

profile pictureAWS
EXPERT
answered 4 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions