A View Of Creating An Oracle User

A View of Creating an Oracle User James Koopmann, [email protected]

Before any schema objects can be created, you must first create a user that will own these objects. This somewhat simple procedure is often overlooked and it can open wide holes in security and portability. How much thought do you put into creating an Oracle user? Have you ever looked within your database after you have installed a database tool or software solution? Many times, we given these requirements at face value and do not feel we can or should question the procedures and requirements of software vendors or our own development teams. After all, they surely would not request authorizations that they do not require. In the past, I have installed many software products that use Oracle on the back end. I have even worked for a few software companies in the past couple of years. The scenario is often the same. Most developers are not database experts, they build solutions in a vacuum, they never get database staff to look at their designs from the beginning and we are all too concerned with just getting a project done. The end result is an open database model where database privileges are the last thing on anyone's minds. This mindset often aggravates many system and database administrators because they see privileges granted to these database users that go against all best practices and open many security holes. One of these holes is allowing every other user to see and modify all of the information within the just installed schema. This is usually done by granting object privileges to PUBLIC and creating PUBLIC synonyms. By doing this, everything is totally open and your data is vulnerable and accessible to everyone. I will address this in the next part in this two part series. However, the most common hole is where the new user created in the database has privileges that allow that user to alter and see other schema's data structures and information. This in fact is the easiest to remedy of the two solutions. It does not matter if you develop applications for mass distribution or internally for your company. You should have the same concerns when creating a database user. You cannot just grant DBA privileges to everyone and not expect problems down the road. For instance, I have seen many development shops that were reduced to one database instance after having separate development, test, and QA environments. They thought that they could just EXPort and IMPort under a different schema owner and all would be fine. After merging all three environments into one, they soon were shocked to realize that everyone had privileges to see and modify everyone else's objects. Know when development, test, and QA was done in the single environment there was some confusion and uneasiness about the true objects that were being used. As a side note, in this day of mergers and our global economy, every company should be concerned with the possibility of either being purchased at some time or having to merge database resources. Not having a security mechanism in place that protects your data can and will reduce your abilities for operating in these environments. So what can we do? Actually, it is quite simple. Take control of the creation of database users from the beginning. Create a procedure for your database user creation, document it, and use it instead of creating users by hand. Below is a procedure I have used in different situations where I was DBA for different software vendors. I have stripped out some code for simplicity here. I have stripped out most or the error trapping, and a driver table that contained the actual set of valid users (for software installation) and the privileges they should be granted. Instead, I have just put in the most basic of

grants for a user that are typically granted to these users. Just keep in mind you do not want to grant system wide privileges to these users or else you will begin opening up those security holes. I hope that you will see the simplicity and singular nature of the process. The purpose being that when a user is created, that user can create or modify only database structures of its own. In addition, I have added comments and kept some of my interesting coding habits.

CREATE OR REPLACE PACKAGE CTRL_SCHEMA_USER AS /* * NAME : CTRL_SCHEMA_USER * PURPOSE : Package Header */ PROCEDURE NewLine ( ioString IN OUT LONG, iString IN VARCHAR2); PROCEDURE ExecDynSQL ( vDynSQL IN LONG); FUNCTION IS_USER ( iUser IN VARCHAR2) RETURN BOOLEAN; FUNCTION IS_TABLESPACE ( iTSName IN VARCHAR2, iTSType IN VARCHAR2) RETURN BOOLEAN; PROCEDURE GET_FILEPATH ( ofilepath OUT VARCHAR2); PROCEDURE CREATE_USER ( iOwner IN VARCHAR2, iPassword IN VARCHAR2, iTSName IN VARCHAR2, iTempTSName IN VARCHAR2); PROCEDURE CREATE_USER ( iOwner IN VARCHAR2); PROCEDURE CREATE_USER_PRIVS ( iOwner IN VARCHAR2, iTSName IN VARCHAR2, iTempTSName IN VARCHAR2); END CTRL_SCHEMA_USER; / /* * NAME : CTRL_SCHEMA_USER * PURPOSE : Package Body */ CREATE OR REPLACE PACKAGE BODY CTRL_SCHEMA_USER AS vDynSQL LONG; filepath VARCHAR2(513);

/* * NAME : NewLine * PURPOSE : This procedure is used to build a SQL statement to execute. * It appends a new line of code and a new line character each time called. * This is just easier for me instead of concatenating many lines of code * and trying to maintain some format to it by putting carriage returns inline. * */ PROCEDURE NewLine ( ioString IN OUT LONG, iString VARCHAR2) AS BEGIN ioString := ioString||iString||CHR(10); END NewLine; /* * NAME : ExecDynSQL * PURPOSE : Execute some Dynamic SQL. * Having one central location to execute SQL allows you to have * one place to error trap as well. */ PROCEDURE ExecDynSQL ( vDynSQL IN LONG) AS BEGIN EXECUTE IMMEDIATE vDynSQL; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20000, 'ExecDynSQL:'|| 'SQLCODE :'||SQLCODE|| ' SQLERRM :'||SQLERRM); END ExecDynSQL; /* * NAME : IS_USER * PURPOSE : Check to see if the user already exists in the database. * Returns TRUE if user is found in dba_users view. */ FUNCTION IS_USER ( iUser VARCHAR2) RETURN BOOLEAN IS v_username VARCHAR2(30); BEGIN SELECT username INTO v_username FROM dba_users WHERE UPPER(username) = UPPER(iUser); RETURN UPPER(v_username) = UPPER(iUser); EXCEPTION WHEN OTHERS THEN RETURN FALSE;

END IS_USER; /* * NAME : IS_TABLESPACE * PURPOSE : Check to see if tablespace already exists in the database. * Returns TRUE if tablespace is found. */ FUNCTION IS_TABLESPACE ( iTSName VARCHAR2, iTSType VARCHAR2) RETURN BOOLEAN IS v_tablespace_name VARCHAR2(30) := NULL; BEGIN SELECT tablespace_name INTO v_tablespace_name FROM dba_tablespaces WHERE UPPER(tablespace_name) = UPPER(iTSName) AND UPPER(contents) = UPPER(iTSType); RETURN UPPER(v_tablespace_name) = UPPER(iTSName); EXCEPTION WHEN OTHERS THEN RETURN FALSE; END IS_TABLESPACE; /* * NAME : GET_FILEPATH * PURPOSE : Get a valid file path to create tablespaces. */ PROCEDURE GET_FILEPATH ( ofilepath OUT VARCHAR2) AS BEGIN SELECT DISTINCT decode(instr(name,':',1),0, substr(name,1,instr(name,'/',-1)), substr(name,1,instr(name,'\',-1))) INTO ofilepath FROM v$datafile WHERE rownum = 1; /*'*/ END GET_FILEPATH; /* * NAME : CREATE_USER * PURPOSE : Call this procedure if you want to create a user with a * predefined default tablespace and default temporary tablespace. */ PROCEDURE CREATE_USER ( iOwner IN VARCHAR2) AS BEGIN GET_FILEPATH(filepath); /* If tablespace does not exist then create one */ IF NOT IS_TABLESPACE('DFLT_USER_TS','PERMANENT') THEN ExecDynSQL('CREATE TABLESPACE DFLT_USER_TS DATAFILE '''||

filepath||'DFLTUSERTS01.DBF'' SIZE 1024M'); END IF; /* If tablespace does not exist then create one */ IF NOT IS_TABLESPACE('TMPY_USER_TS','TEMPORARY') THEN ExecDynSQL('CREATE TEMPORARY TABLESPACE TMPY_USER_TS TEMPFILE '''|| filepath||'TMPYUSERTS01.DBF'' SIZE 300M'); END IF; CREATE_USER(iOwner, iOwner, 'DFLT_USER_TS', 'TMPY_USER_TS'); END CREATE_USER; /* * NAME : CREATE_USER * PURPOSE : This is an overloaded procedure. * If CREATE_USER is called with a supplied password and tablespaces * this procedure will be called instead of the prior CREATE_USER * procedure. This allows for some logic on the front end to query * the database for valid tablespaces or to accept user entered tablespaces. */ PROCEDURE CREATE_USER ( iOwner IN VARCHAR2, iPassword IN VARCHAR2, iTSName IN VARCHAR2, iTempTSName IN VARCHAR2) AS BEGIN GET_FILEPATH(filepath); /* If tablespace does not exist then create one */ IF NOT IS_TABLESPACE(iTSName,'PERMANENT') THEN ExecDynSQL('CREATE TABLESPACE '||iTSName||' DATAFILE '''|| filepath||iTSName||'01.DBF'' SIZE 1024M'); END IF; /* If tablespace does not exist then create one */ IF NOT IS_TABLESPACE(iTempTSName,'TEMPORARY') THEN ExecDynSQL('CREATE TEMPORARY TABLESPACE '||iTempTSName||' TEMPFILE '''|| filepath||iTempTSName||'01.DBF'' SIZE 300M'); END IF; /* If the user does not already exist then create the user */ IF NOT IS_USER(iOwner) THEN vDynSQL := ''; NewLine(vDynSQL,'CREATE USER '||iOwner||' IDENTIFIED BY "'||iPassword||'"'); NewLine(vDynSQL,' DEFAULT TABLESPACE ' || iTSName); NewLine(vDynSQL,' TEMPORARY TABLESPACE ' || iTempTSName); NewLine(vDynSQL,' PROFILE DEFAULT'); ExecDynSQL(vDynSQL); END IF; CREATE_USER_PRIVS(iOwner, iTSName, iTempTSName); END CREATE_USER; /* * NAME : CREATE_USER_PRIVS * PURPOSE : This does basic grants for a schema user

* This is the most important part of this procedure. * Please note that the privileges granted only apply to the grantee's * own schema. There are no global or system wide privileges given. Also * there are no WITH ADMIN OPTION clauses given so this user can not * affect any other schema but its own. * There are a few V$ views that I normally grant to users only because * I feel they are important enough when debugging code or providing the * applications to understand where it is in the execution process. */ PROCEDURE CREATE_USER_PRIVS ( iOwner IN VARCHAR2, iTSName IN VARCHAR2, iTempTSName IN VARCHAR2) AS BEGIN ExecDynSQL('ALTER USER '||iOwner||' QUOTA UNLIMITED ON '||iTSName); ExecDynSQL('ALTER USER '||iOwner||' QUOTA UNLIMITED ON '||iTempTSName); ExecDynSQL('GRANT CREATE CLUSTER TO '||iOwner); ExecDynSQL('GRANT CREATE DATABASE LINK TO '||iOwner); ExecDynSQL('GRANT CREATE DIMENSION TO '||iOwner); ExecDynSQL('GRANT CREATE INDEXTYPE TO '||iOwner); ExecDynSQL('GRANT CREATE JOB TO '||iOwner); ExecDynSQL('GRANT CREATE MATERIALIZED VIEW TO '||iOwner); ExecDynSQL('GRANT CREATE OPERATOR TO '||iOwner); ExecDynSQL('GRANT CREATE PROCEDURE TO '||iOwner); ExecDynSQL('GRANT CREATE SEQUENCE TO '||iOwner); ExecDynSQL('GRANT ALTER SESSION TO '||iOwner); ExecDynSQL('GRANT CREATE SESSION TO '||iOwner); ExecDynSQL('GRANT CREATE SYNONYM TO '||iOwner); ExecDynSQL('GRANT CREATE TABLE TO '||iOwner); ExecDynSQL('GRANT CREATE TRIGGER TO '||iOwner); ExecDynSQL('GRANT CREATE TYPE TO '||iOwner); ExecDynSQL('GRANT CREATE VIEW TO '||iOwner); ExecDynSQL('GRANT SELECT ON DUAL TO '||iOwner); ExecDynSQL('GRANT SELECT ON V_$SESSION TO '||iOwner); ExecDynSQL('GRANT SELECT ON V_$PROCESS TO '||iOwner); ExecDynSQL('GRANT SELECT ON V_$MYSTAT TO '||iOwner); ExecDynSQL('GRANT SELECT ON V_$TIMER TO '||iOwner); END CREATE_USER_PRIVS; END CTRL_SCHEMA_USER ; / Now if you use this procedure or modify it a bit for your own environment you will have a consistent creation of users in your database that will not be questioned by auditors. If you are a vendor, you clients will actually praise you for not taking advantage of database privileges and opening up holes in their databases. Just keep in mind that users created in the database are for accessing objects for an application, not for running the database.

Often times, we do require some interaction between schemas or ownership within our databases. In the next article, I will address a clean way through some procedure calls that will allow you to manage this in a controlled fashion that still adheres to database security best practices. 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. 1. Create the schema owner. This could be done by using the procedure call that was introduced in Part I of this series. The call might be like this. EXEC ctrl_schema_user.create_user('SCHEMA_OWNER','OWNER','USERS','TEMP'); 2. Then through this schema owner, we could go ahead and create all of our objects. For the sake of an example, we will create a table named TABLE_01. 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. 3. Therefore, to create a new user we could use the same procedure to create our 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's 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's 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's 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 1. 2.

EXEC ctrl_schema_admin.grant_privilege('SCHEMA_OWNER','SCHEMA_USER','TABLE','TABLE_01'); EXEC ctrl_schema_admin.grant_privilege('SCHEMA_OWNER','SCHEMA_USER');

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; /