A View of Creating an Oracle User - Part I - Page 2June 2, 2005 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. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
