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 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
1.
EXEC ctrl_schema_admin.grant_privilege('SCHEMA_OWNER,'SCHEMA_USER,'TABLE','TABLE_01');
2.
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;
/
»
See All Articles by Columnist James Koopmann