In Oracle environments, one effective method to enhance security is to define separate users for applications and separate them from Object Owners. This approach not only limits the access level but also prevents potential damage from various attacks. In this article, we will discuss how to implement this method and grant the necessary permissions to the new user.
Before proceeding, I would like to thank my esteemed mentor, Engineer Teimouri, for his invaluable guidance on this topic.
Creating a New User for the Application
First, we need to create a new user that the application will connect to. For example, we will create a user named vahid_app:
CREATE USER vahid_app IDENTIFIED BY strong_password;
GRANT CREATE SESSION TO vahid_app;
Creating a Script to Grant Permissions
The script that grants the necessary permissions to the vahid_app user should include SELECT, INSERT, UPDATE, DELETE, and EXECUTE privileges. We can create a PL/SQL block that dynamically grants these permissions to the user.
To test this setup, we will create a few objects in the vahid user, who is considered the owner.
create table t1 (f1 number, f2 date);
create view v1 as select * from t1;
CREATE SEQUENCE S1 INCREMENT BY 1 START WITH 1 MAXVALUE 99999999999999 MINVALUE 1;
create or replace procedure p1
as
begin
insert into t1 values (s1.nextval,sysdate);
end;
/
Now, let's run the access grant script.
BEGIN
FOR rec IN (SELECT object_name, object_type
FROM dba_objects
WHERE owner = 'VAHID') LOOP
IF rec.object_type = 'TABLE' THEN
EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON VAHID.' || rec.object_name || ' TO VAHID_APP';
ELSIF rec.object_type in ('VIEW','SEQUENCE') THEN
EXECUTE IMMEDIATE 'GRANT SELECT ON VAHID.' || rec.object_name || ' TO VAHID_APP';
ELSIF rec.object_type IN ('PACKAGE', 'PROCEDURE', 'FUNCTION') THEN
EXECUTE IMMEDIATE 'GRANT EXECUTE ON VAHID.' || rec.object_name || ' TO VAHID_APP';
END IF;
END LOOP;
END;
To test if the permissions were correctly granted, run the following query:
Creating an AFTER LOGON Trigger
The most important step to connect to the new user without changing the applications is to create an AFTER LOGON trigger that sets the CURRENT_SCHEMA to the owner schema after the user logs in. This prevents the need to prefix the owner name to the objects when calling them.
CREATE OR REPLACE TRIGGER TRG_SET_SCHEMA
AFTER LOGON ON VAHID_APP.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=VAHID';
END;
/ Now, let's test it:
SELECT * FROM t1;EXEC p1;
The above operations should execute successfully. Now, let's see what happens with the following command:
TRUNCATE TABLE t1;ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges"
This error is the expected behavior, demonstrating the improved security we aimed for.