A View of Creating an Oracle User - Part II

In Part I of this series, a methodology was given on how to create a user. If you did not get a chance to read Part I, please do so now before continuing with Part II, as Part II builds upon Part I and requires an understanding of how a basic user is created.

In Part I we created a database user with the bare minimum privileges so that they could connect to a database and create objects only within their own schema. This was imperative since we were creating this user under the premise that they would only be accessing their own schema objects. This is the way it should be and many third party vendors or application designers often forget the security holes that can be created if a user is able to access other objects outside of their own schema. It was also suggested in Part I that a schema owner should never be allowed to be the user that is connected from an application. It is always best to create a user that can only SELECT, UPDATE, DELETE, INSERT, or EXECUTE objects in an underlying schema. This is because if someone does miscode an application or a security hole is found, running as the schema owner allows the application or hacker to alter or even drop objects at will. You really want to protect yourself from this intrusion by adding another layer of protection between the schema objects and the application.

Therefore, if we were to begin fresh and begin designing an application and underlying database objects, we would perform at a minimum the following basic steps.

EXEC ctrl_schema_user.create_user('SCHEMA_OWNER,'OWNER','USERS','TEMP');

CREATE TABLE table_01 (col01 NUMBER);

This is where most applications stop. They continue to create more objects, tables, views, procedures, and packages. The applications will connect as the SCHEMA_OWNER and they think everything is fine. The problem as we had stated earlier, and I will restate here, is that this schema owner must be protected, almost as much as your SYS and SYSTEM user accounts. Why? Because they have supper privileges for the schema and can ALTER or DROP objects at will. What needs to happen is the creation of a new user that is only allowed basic access to the objects under the SCHEMA_OWNER.

EXEC ctrl_schema_user.create_user('SCHEMA_USER,'USER','USERS','TEMP');

Now, since we have created our users with minimal privileges this new SCHEMA_USER cannot 'see' any of the SCHEMA_OWNER objects. If we were to do even a simple DESCRIBE of the table (TABLE_01) that we created in step 2 we would get the Oracle error: ORA-04043: object table_01 does not exist. This is because with such low privileges in the database this new SCHEMA_USER cannot access any objects outside of its own schema. This is a good thing! I have worked at many software shops where all users are given the DBA role and have privileges across every schema in the database, basically removing any concept of unique ownership of objects and providing limited security if any.

What needs to happen now is to give the SCHEMA_USER permission to access the SCHEMA_OWNER objects. This can be done many different ways. Most will grant the appropriate privilege, depending on the whether the object is a table, view, trigger, procedure, etc., to the application user (SCHEMA_USER). This gives the application user the ability to perform the desired actions on the object. The issue then being that the application must reference the SCHEMA_OWNER objects with a prefix of 'SCHEMA_OWNER.'. Therefore if we logged into the database as SCHEMA_OWNER and:

GRANT INSERT,UPDATE,DELETE,SELECT ON table_01 TO schema_user;

The SCHEMA_USER would have to reference the object in applications as SCHEMA_OWNER.TABLE_01. This can be very annoying to an application developer and does not allow for portability of the application. Therefore, what typically happens is that a PUBLIC or PRIVATE synonym is created for the TABLE_01 object. In a nutshell, the PUBLIC synonym allows everyone to know about the TABLE_01 object and reference it without the prefix of SCHEMA_OWNER and a PRIVATE synonym is owned by the SCHEMA_USER and is available for him/her alone. There are pros and cons to both approaches when creating synonyms but it all boils down to whether you want keep your SCHEMA_OWNER completely hidden from all users. When you couple this with wanting true and tight security of a data model, there really isn't a choice. You will only want those users that are going to access the object to know about a particular object in the database so the preference here is to create a PRIVATE synonym under the SCHEMA_USER account that will point at the SCHEMA_OWNER objects.

From a maintenance standpoint, the granting of privileges from SCHEMA_OWNER to SCHEMA_USER and creating a synonym for SCHEMA_USER can be tedious. This is because you must first login as SCHEMA_OWNER and issue the GRANT to SCHEMA_USER and then login as SCHEMA_USER to CREATE a synonym to point at the object that was just granted to it.

To alleviate this issue I use a set of procedures that streamline the steps and in turn provide added security to the SCHEMA_OWNER account. The code follows this article. Whenever I create a user, I also compile this package into that account and GRANT EXECUTE on the package to a set of application users in the database. Moreover, whenever I want to GRANT someone a privilege I use this package instead of the command line GRANT option. This package will in turn issue the GRANT option for me and then CALL the GRANTEE package where it will create a synonym back to the object just granted. The major benefit is that I do not need to login as that SCHEMA_USER or even know the password of the SCHEMA_USER--thus creating an added security layer. Keep in mind that I have trimmed down this code, much like the one in Part I of this series, and have taken out some specific logic that handles error trapping and determination of the type of GRANT to give my SCHEMA_USER account. In addition, some systems dictate that the packages compiled into grantors and grantees differ. Plus, this code assumes only one type of object, a TABLE, and you should change this to include all objects that you need to grant privileges to. Additionally I will typically have a control table that maps object to user type or a specific user to determine those objects so that they can actually be granted. This allows me to not just run off the USER_OBJECTS view and grant everything to a particular user. In addition, you will note that the GRANT_PRIVILEGE procedure is overloaded that allows you to cycle through all objects in the USER_OBJECTS view or pass in a single object.

Now if we wanted to GRANT privileges to our SCHEMA_OWNER.TABLE_01 to SCHEMA_USER we just issue one of the following calls. The first will only issue a grant and create a synonym for the single object TABLE_01 while the second call will cycle through all of the objects in USER_OBJECTS.f

In this day of heightened security awareness, we should all be concerned with the holes we have opened up in our databases. It is also just good practice to separate application users from schema owners. It allows us to move applications around environments without tying them to a specific user account. With a little forethought and customization of the package given you will soon be on your way to providing a more secure and flexible environment.

CREATE OR REPLACE PACKAGE CTRL_SCHEMA_ADMIN
AS
/*
 *  NAME    : CTRL_SCHEMA_ADMIN
 *  PURPOSE : Package Header
 */
FUNCTION MY_SYNONYM
        ( iObjectName     IN VARCHAR2) RETURN BOOLEAN;
PROCEDURE GRANT_PRIVILEGE
        ( iOwner          IN VARCHAR2,
          iToOwner        IN VARCHAR2);
PROCEDURE GRANT_PRIVILEGE
        ( iOwner          IN VARCHAR2,
          iToOwner        IN VARCHAR2,
          iObject_Type    IN VARCHAR2,
          iObject_Name    IN VARCHAR2);
PROCEDURE CREATE_SYNONYM
        ( iOwner          IN VARCHAR2,
          iObject_Name    IN VARCHAR2);
END CTRL_SCHEMA_ADMIN;
/
/*
 *  NAME    : CTRL_SCHEMA_ADMIN
 *  PURPOSE : Package Body
 */
CREATE OR REPLACE PACKAGE BODY CTRL_SCHEMA_ADMIN
AS
vDynSQL      LONG;
/*
 *  NAME    : MY_SYNONYM
 *  PURPOSE : Function to determine if a synonym already exists.
 *            Returns TRUE if synonym is found in user_synonyms view.
 */
FUNCTION MY_SYNONYM
( iObjectName VARCHAR2)
RETURN BOOLEAN IS
v_objectname        VARCHAR2(30) := NULL;
BEGIN
  SELECT synonym_name INTO v_objectname
    FROM user_synonyms
   WHERE synonym_name        = UPPER(iObjectName);
  RETURN UPPER(v_objectname) = UPPER(iObjectName);
EXCEPTION
    WHEN OTHERS THEN
        RETURN FALSE;
END MY_SYNONYM;
/*
 *  NAME    : GRANT_PRIVILEGE
 *  PURPOSE : Cycle through all the user owned objects and call
 *            the procedure responsible for granting the privilege.
 *
 *  NOTE    : objects are taken from user_objects but are not defined within the 
 *            users recyclebin.
 */
PROCEDURE GRANT_PRIVILEGE
( iOwner                  IN VARCHAR2,
  iToOwner                IN VARCHAR2)
AS
    TYPE refCursor   IS REF CURSOR;
    c0   refCursor;
    TYPE object_recTYPE IS RECORD (
         OBJECT_TYPE           VARCHAR2(30),
         OBJECT_NAME           VARCHAR2(30));
    object_rec object_recTYPE;
BEGIN
    OPEN c0 FOR 
      'SELECT object_type, object_name||
      '  FROM user_objects||
      ' WHERE object_name NOT IN '||
      '       (SELECT object_name FROM user_recyclebin||
      '         WHERE user_objects.object_type = user_recyclebin.type||
      '           AND user_objects.object_name = user_recyclebin.object_name)'||
      '   AND object_name != ''CTRL_SCHEMA_ADMIN||
      ' ORDER BY object_type,object_name;
    LOOP
        FETCH c0 INTO object_rec;
        EXIT WHEN c0%NOTFOUND;
        GRANT_PRIVILEGE(iOwner, iToOwner, object_rec.object_type, object_rec.object_name);
    END LOOP;
    CLOSE c0;
END GRANT_PRIVILEGE;
/*
 *  NAME    : GRANT_PRIVILEGE
 *  PURPOSE : This is an overloaded procedure.
 *            If GRANT_PRIVILEGE is called with owners, object type, and object name
 *            a single grant will be issued through this procedure. 
 */
PROCEDURE GRANT_PRIVILEGE
( iOwner                  IN VARCHAR2,
  iToOwner                IN VARCHAR2,
  iObject_Type            IN VARCHAR2,
  iObject_Name            IN VARCHAR2)
AS
BEGIN
  /*
   * Expand this CASE statement to include the object types and privileges you need to grant.
   */
  CASE
  WHEN iObject_Type = 'TABLE'     THEN
    EXECUTE IMMEDIATE 'GRANT SELECT,INSERT,UPDATE,DELETE ON '||iOwner||'.'||iObject_Name||' TO '||iToOwner;
    /*
     * This is a call to the user being granted privileges to. It allows the remote procedure to 
     * be run as the grantee and create a synonym back for the privilege just granted.
     */
dbms_output.put_line('.');
dbms_output.put_line('.');
dbms_output.put_line('.');
dbms_output.put_line('.');
    EXECUTE IMMEDIATE 'DECLARE BEGIN '||
               iToOwner||'.CTRL_SCHEMA_ADMIN.CREATE_SYNONYM('''||iOwner||''','''||iObject_Name||'''); END;';
  ELSE  vDynSQL := vDynSQL;
  END CASE;
END GRANT_PRIVILEGE;
/*
 *  NAME    : CREATE_SYNONYM
 *  PURPOSE : Used in the remote call from GRANT_PRIVILEGE to the owner being granted the privilege.
 *            When called this procedure will create a synonym to point back at the object just 
 *            given privileges to.
 */
PROCEDURE CREATE_SYNONYM
( iOwner                  IN VARCHAR2,
  iObject_Name            IN VARCHAR2)
AS
BEGIN
    IF NOT MY_SYNONYM(iObject_Name) THEN
      EXECUTE IMMEDIATE 'CREATE SYNONYM '||iObject_Name||' FOR '||iOwner||'.'||iObject_Name;
    END IF;
END CREATE_SYNONYM;
END CTRL_SCHEMA_ADMIN;
/

» See All Articles by Columnist James Koopmann