Dear All,
Good Day!!!!
Need your help to restrict the user access at database level.
1) There is user "TEST" with below privileges.
GRANT EXECUTE ANY PROGRAM TO TEST;
GRANT SELECT ANY TABLE TO TEST;
GRANT SELECT ANY TRANSACTION TO TEST;
GRANT SELECT ANY SEQUENCE TO TEST;
GRANT EXECUTE ANY PROCEDURE TO TEST;
GRANT EXECUTE ANY OPERATOR TO TEST;
GRANT CREATE SESSION TO TEST;
GRANT DEBUG CONNECT SESSION TO TEST;
GRANT SELECT ANY DICTIONARY TO TEST;
GRANT EXECUTE ANY LIBRARY TO TEST;
GRANT EXECUTE ANY TYPE TO TEST;
GRANT DEBUG ANY PROCEDURE TO TEST;
2) Sample procedure with update statement under master schema "NONMED"
CREATE OR REPLACE PROCEDURE NONMED.PROCEDURE_NAME (
P_TRX_NO NUMBER,
P_REG VARCHAR2)
IS
V_1 VARCHAR2 (200);
V_2 VARCHAR2 (200);
BEGIN
Other queries to get the values for the mentioned variables
--------- Update statement --------------
UPDATE NONMED.TABLE_NAME
SET COLUMN_1 = V_1,
COLUMN_2 = V_2
WHERE COLUMN_3 = P_TRX_NO;
COMMIT;
END;
Now, here the user "TEST" can able to execute procedure successfully without getting any insufficient privilege message as the procedure contains update statement.
3) Executing procedure.
BEGIN
NONMED.PROCEDURE_NAME (value1,value2);
END;
And when looking for the details in NONMED.TABLE_NAME the last updated record was shown under name "TEST"
How we can restrict INSERT, UPDATE, DELETE operations upon executing procedures without revoking EXECUTE PROCEDURE form the user "TEST".