Andrew Fraser DBA

Public Database Links security fix using views

Public database links are a well known security problem – all database users with the most minimal privileges (create session) are able to use the public database link, even including deleting data across the link.

Use this to see any public database links going outwards from a database:

col host form a30
col db_link form a30
SELECT INITCAP (host ) host , username , db_link FROM dba_db_links WHERE owner = 'PUBLIC' ORDER BY 1 , 2 ;

And this to see what damage they can do on the remote databases:

SELECT privilege FROM session_privs@dblink ORDER BY 1 ;
SELECT table_name , privilege FROM user_tab_privs@dblink WHERE privilege != 'SELECT' ORDER BY 1 , 2 ;
SELECT t.table_name , t.privilege
  FROM user_tab_privs@dblink t
  JOIN user_role_privs@dblink r
    ON r.granted_role = t.grantee
 WHERE t.privilege != 'SELECT'
 ORDER BY 1 , 2 ;

The hard part is getting the public database links out without painful rewrites of the application. One relatively quick and easy way of doing this is to use views, including updateable views, of the remote objects in the local database. Privilege grants and synonyms can be given out on those views as if they were local tables, and the database link can then be one central private database link (good) rather than a public database link (bad).

conn schema/pass@remote
CREATE TABLE t1 ( idpk NUMBER , mytext VARCHAR2(50) ) ;

conn dba/pass@local
CREATE USER user1 identified by pass ;
GRANT CREATE SESSION TO user1 ;
CREATE USER user2 IDENTIFIED BY pass ;
GRANT CREATE SESSION TO user2 ;
CREATE ROLE role1 ;
GRANT role1 TO user1 ;
CREATE DATABASE LINK private_remote CONNECT TO schema IDENTIFIED BY pass USING 'remote' ;
CREATE VIEW t1 AS SELECT * FROM t1@private_remote ;
GRANT SELECT , INSERT , UPDATE , DELETE ON t1 TO role1 ;
CREATE PUBLIC SYNONYM t1 FOR t1 ; -- alternatively specify owner  below

/* Works ok for users who have the role */
conn user1/pass@local
SELECT * FROM t1 ;
INSERT INTO t1 ( idpk , mytext ) VALUES ( 1 , 'Hello World' ) ;
UPDATE t1 SET idpk = 2 ;

/* Those without the role are prevented from doing things they are not meant to */
conn user2/pass@local
SELECT * FROM t1 ;  -- ORA-00942 table or view does not exist

Similarly remote procedure/package/function calls could be wrapped within a stored pl/sql placeholder object locally:

CREATE OR REPLACE PROCEDURE myproc
AS
BEGIN
   myproc@private_remote ;
END ;
/
GRANT EXECUTE ON myproc TO role1 ;

Using a public database link authenticated by current user is another option to fix – although may mean creating large numbers of database user accounts on the remote database.