How to protect CREATE PUBLIC SYNONYM against privilege escalation

This post is also available in: Português

During my last presentation on GUOB Tech Tour 2016 - Oracle Technology Tour LA - Brazil, I demonstrate how we could easily use the CREATE PUBLIC SYNONYM privilege to escalate and get DBA privilege.

In this article, I will give you a package that I've created and I use in my systems to allow users to create their own public synonyms without compromising the security.

So, let's begin.

How does it work?

This package works allowing the user to only create synonyms to his own objects or dropping public synonyms that are pointing to one of his objects.

Give the necessary grants to the package owner

First of all, this package should be created on a user with the following minimal privileges given directly (not via ROLE) :

• SELECT on DBA_SYNONYMS
• CREATE PUBLIC SYNONYM
• DROP PUBLIC SYNONYM

1. Create the package

CREATE OR REPLACE PACKAGE MANAGE_PUBLIC_SYNONYM AS
  -- Created by Rodrigo Jorge - www.dbarj.com.br --
  PROCEDURE CREATE_SYNONYM(SYNONYM_NAME IN VARCHAR2, OBJECT_NAME IN VARCHAR2);
  PROCEDURE DROP_SYNONYM(SYNONYM_NAME IN VARCHAR2);
END;
/

2 Create the package body

CREATE OR REPLACE PACKAGE BODY MANAGE_PUBLIC_SYNONYM AS
  -- Created by Rodrigo Jorge - www.dbarj.com.br --
  FUNCTION CHECK_EXISTS(SYN_NAME IN VARCHAR2) RETURN BOOLEAN IS
    OUT_RESULT NUMBER;
  BEGIN
    SELECT 1
    INTO   OUT_RESULT
    FROM   DBA_SYNONYMS
    WHERE  OWNER = 'PUBLIC'
    AND    SYNONYM_NAME = SYN_NAME;
    RETURN TRUE;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN FALSE;
  END;

  FUNCTION GET_PUBLIC_SYN_OWNER(SYN_NAME IN VARCHAR2) RETURN VARCHAR2 IS
    OUT_RESULT VARCHAR2(30);
  BEGIN
    SELECT TABLE_OWNER
    INTO   OUT_RESULT
    FROM   DBA_SYNONYMS
    WHERE  OWNER = 'PUBLIC'
    AND    SYNONYM_NAME = SYN_NAME;
    RETURN OUT_RESULT;
  END;

  PROCEDURE RAISE_ERROR(IN_CODE IN NUMBER) IS
  BEGIN
    CASE IN_CODE
      WHEN -20001 THEN
        RAISE_APPLICATION_ERROR(IN_CODE, 'Synonym already exists.');
      WHEN -20002 THEN
        RAISE_APPLICATION_ERROR(IN_CODE, 'Synonym does not exist.');
      WHEN -20003 THEN
        RAISE_APPLICATION_ERROR(IN_CODE, 'Synonym is not yours.');
      ELSE
        RAISE_APPLICATION_ERROR(-20999, 'Generic error.');
    END CASE;
  END;

  PROCEDURE CREATE_SYNONYM(SYNONYM_NAME IN VARCHAR2, OBJECT_NAME IN VARCHAR2) IS
    SESS_USER VARCHAR2(30);
  BEGIN
    IF CHECK_EXISTS(SYNONYM_NAME) = TRUE
    THEN
      RAISE_ERROR(-20001);
    END IF;
    SESS_USER := SYS_CONTEXT('USERENV', 'SESSION_USER');
    EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM ' || DBMS_ASSERT.ENQUOTE_NAME(SYNONYM_NAME, FALSE) || ' FOR ' || DBMS_ASSERT.ENQUOTE_NAME(SESS_USER, FALSE) || '.' || DBMS_ASSERT.ENQUOTE_NAME(OBJECT_NAME, FALSE);
  END;

  PROCEDURE DROP_SYNONYM(SYNONYM_NAME IN VARCHAR2) IS
    OBJ_OWNER VARCHAR2(30);
    SESS_USER VARCHAR2(30);
  BEGIN
    IF CHECK_EXISTS(SYNONYM_NAME) = FALSE
    THEN
      RAISE_ERROR(-20002);
    END IF;
    OBJ_OWNER := GET_PUBLIC_SYN_OWNER(SYNONYM_NAME);
    SESS_USER := SYS_CONTEXT('USERENV', 'SESSION_USER');
    IF OBJ_OWNER <> SESS_USER
    THEN
      RAISE_ERROR(-20003);
    END IF;
    EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || DBMS_ASSERT.ENQUOTE_NAME(SYNONYM_NAME, FALSE);
  END;

END;
/

3 Grant Privilege to this package to the users that need Public Synonyms

Example:

GRANT EXECUTE ON MANAGE_PUBLIC_SYNONYM TO SCOTT;

Optionally, you can also create a synonym to avoid typing the owner of the package every time:

CREATE SYNONYM SCOTT.MANAGE_PUBLIC_SYNONYM FOR MANAGE_PUBLIC_SYNONYM;

And that's it.

How to use?

To create a public synonym

BEGIN
  MANAGE_PUBLIC_SYNONYM.CREATE_SYNONYM('EMP','EMP');
END;
/

To drop a public synonym

BEGIN
  MANAGE_PUBLIC_SYNONYM.DROP_SYNONYM('EMP');
END;
/

And remember: you will not be able to touch on another user's public synonym.

0