Oracle Schema Security Against Authorize Access

Few time back our dev team reported that application started crash and the reason was missing table.

As it was a legacy application and dev knew schema credentials. One of the dev dropped a table and application started throwing error.

This was alarming and I decided to find suitable way to overcome such situation in future.we can simply  create a new user and assign required privileges to new user.But as we can't change application , it was difficult to implement it. we have to avoid solution where we have to provide schema qualification to access objects.

Initially I though about but synonyms but it seems additional overhead to maintain then I found current_schema option from Oracle documentation.

Here is the step by step guide for you to implement and test that solution.

--Connect as sys
1 --Create UserA
CREATE USER USERA IDENTIFIED BY usera;

2 --Grant required privileges to userA
  GRANT CONNECT TO USERA;
  GRANT RESOURCE TO USERA;

3 -Connect as userA
sqlplus usera/usera

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 11 20:36:04 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>                             

4 --Create table
  CREATE TABLE USERA_TEST
    ( Id NUMBER, Name VARCHAR2(20)
    );
   
5 --Insert test values USERA.USERA_TEST
INSERT INTO usera_test
SELECT 1,'Val1' FROM dual
UNION ALL
SELECT 2,'Val2' FROM dual
UNION ALL
SELECT 3,'Val3' FROM dual;
COMMIT;
   
6- -Create stored proc
CREATE PROCEDURE usera_sp
AS
BEGIN
  dbms_output.put_line('This is USERA stored Proc');
END;
/

7 -- disconnect USERA

8 --  Connect as sys and process following

9 --Creating role
CREATE ROLE ROLE_USERA_PRIVS;

10 --Create UserB
CREATE USER USERB IDENTIFIED BY userb;

GRANT CONNECT TO USERB;
 
11 --Create Grant Script
Spool ON
spool /path/usera_Privs.sql -- path can be any of your preference

SELECT
  CASE
    WHEN object_type ='TABLE'
    THEN 'Grant select on USERA.'||Object_Name||' to ROLE_USERA_PRIVS;'
    WHEN object_type IN ('PROCEDURE','FUNCTION')
    THEN 'Grant Execute on USERA.'||Object_Name||' to ROLE_USERA_PRIVS;'
    WHEN object_type IN ('SEQUENCE')
    THEN 'Grant SELECT on USERA.'||Object_Name||' to ROLE_USERA_PRIVS;'
    WHEN object_type IN ('MATERIALIZED VIEW','VIEW')
    THEN 'Grant select on USERA.'||Object_Name||' to ROLE_USERA_PRIVS;'
  END AS script
FROM dba_objects
WHERE owner='USERA' ;

spool OFF

12 -- Execute script usera_Privs.sql

13 -- Grant role to UserA and disconnect sys
Grant ROLE_USERA_PRIVS to USERB;

14 -- Connect as USERB
--Now if you will connect to USERB and execute following you will have error "ORA-00942: table or view does not exist"
SQL> select * from usera_test;
select * from usera_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist 

However schema qualification will work
SQL> SELECT * FROM USERA.usera_test;

        ID NAME
---------- --------------------
         1 Val1
         2 Val2
         3 Val3
       
15 -- Disconnect USERB

16 -- Connect as sys.
17 -- Create Trigger to change current_schema to required one. So that our application/user need not to mention schema qualification

SQL>  CREATE OR REPLACE TRIGGER sys.afterlogon_myapp AFTER logon ON DATABASE
  DECLARE
  BEGIN
    -- Trigger Used to Set Correct Schema based on Role
    IF (dbms_session.is_role_enabled('ROLE_USERA_PRIVS')) THEN
      EXECUTE immediate 'alter session set current_schema=USERA';
    END IF;
  END;
  /

18 -- Disconnect sys

19 -- connect USERB 
sqlplus userb/userb

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 11 20:26:09 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
-- execute sql without schema qualification
SQL> select * from usera_test;

        ID NAME
---------- --------------------
         1 Val1
         2 Val2
         3 Val3
-- execute stored proc without schema qualification       
SQL> set serveroutput on
SQL> execute usera_sp
This is USERA stored Proc

PL/SQL procedure successfully completed.
                                          
Enjoy !