Hi Team,
My environment details are as below.
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
I have use case to grant schema users to connect via some other users 'alter user abc grant connect through xyz'
Also, i have the same schema users id exist in one of a table, i thought of creating a procedure and via cursor loop to grant it so that i don't have to grant it manually for every users.
CREATE OR REPLACE PROCEDURE XX_GRANT_USERS_RIGHT
IS
V_USER_ID VARCHAR2 (3);
BEGIN
FOR R IN ( SELECT USER_ID
FROM USER_DETAIL
WHERE ACCESS_LEVEL > 0)
LOOP
V_USER_ID := R.USER_ID;
alter user V_USER_ID grant connect through rms;
END LOOP;
END;
/
If i remove my alter statement then it compiles otherwise i received a compilation issue as below.
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
( begin case declare end exit for goto if loop mod null
pragma raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
Appreciate your help folks, i am new to the community.
Jay