Oracle 10g: A Simple Security Approach – Part 1

by: JP Vijaykumar

A detailed discussion on the
setup and administration of database access and security is beyond the scope of
this document.

This document examines
simplifying the setup and administration of database security in an ever
changing / volatile environment.

The majority of tasks an
Oracle DBA does in maintaining the Security and Database Access are:

0100
User account management

0200
Special requirements

Creation and maintenance of
database access and security is becoming a complex and demanding task day in
and day out. A lack of proper planning and approach will put the DBA’s life into
fire fighting mode.

0100 User account management

The creation and re-creation
of tables in Development/ Integration environments is an ongoing process.

The creation of new schemas
and tables in Production environment is continuous, with new code / modules
migration from Development / Integration environments.

In either case, not all of
the users in the database can access the newly created / re-created objects.

Users granted only the DBA
role or SELECT_ANY TABLE system level privilege can access the newly created /
re-created tables in a database without any re-granting of privileges.

For security reasons, if we cannot
grant the SELECT_ANY TABLE system level privilege or the DBA role to the users
in the database, imagine the trouble of frequently granting the necessary
privileges on the newly created / re-created tables to users /roles.

Tracking all the newly
created/ re-created tables and granting permissions on these tables to
different users / roles is a full time DBA job. To address this issue and
simplify the process of granting privileges, create three roles for each
schema, to manage the different table level permissions required by the users. (Currently,
the existing schemas in our database are – TOM, DICK and HARRY). Alternatively,
we can reduce or increase the number of roles in the database, depending on the
operational requirements and ease.

The details of roles and
their granted table level privileges are as follows:

Create the roles for each schema.

Create role tom_select;

Create role tom_sinsert;

Create role tom_siud;

Create role dick_select;

Create role dick_sinsert;

Create role dick_siud;

Create role harry_select;

Create role harry_sinsert;

Create role harry_siud;

Grant create session to these roles:

Grant create session to tom_select;

Grant create session to tom_sinsert;

Grant create session to tom_siud;

Grant create session to dick_select;

Grant create session to dick_sinsert;

Grant create session to dick_siud;

Grant create session to harry_select;

Grant create session to harry_sinsert;

Grant create session to harry_siud;

This approach is good and
maintainable with only few schemas in the database. With more schemas in the
database, the number of roles to be created will become a huge list.

To further simplify the
approach, you can have three roles for the entire database, like db_select, db_sinsert
and db_siud. Grant the necessary permissions on all of the schema tables in the
database to these three roles.

Another approach is to have
multiple schemas grouped into different groups, depending on their application,
sensitivity, criticality. Create group1_select, group1_sinsert and group1_siud roles
for each group.

Whenever a new user is created,
grant the necessary roles to the user.

Create a procedure to grant
different levels of permission to different roles on the newly created / re-created tables. Schedule
the procedure to run every day at 18:00 hrs. By scheduling the procedure to run
everyday at 18:00 hrs, the users can access all the tables, created /
re-created the day before.

declare

begin

for t1 in (select owner, table_name from all_tables

             where owner in (‘TOM’,’DICK’,’HARRY’)) loop

execute immediate ‘grant select on ‘||t1.owner||’.’||t1.table_name||’ to ‘||t1.owner||’_SELECT’;

execute immediate ‘grant select, insert on ‘||t1.owner||’.’||t1.table_name|| ‘ to ‘||t1.owner||’_SINSERT’;

execute immediate ‘grant select, insert, update, delete on ‘||t1.owner||’.’||t1.table_name||  ‘  to ‘||t1.owner||’_SIUD’;

end loop;

end;

This procedure grants the
required permissions on the existing tables in the database to different roles
as per the security setup.

This procedure works fine if
each schema in the database has three roles, however, the procedure needs to be
modified if the roles setup is different, i.e. three roles for the entire
database or all of the schemas are put into different groups and three roles
per group are created.

At any time, the table level
permissions on all the schema objects can be granted/re-granted to different
roles by executing the procedure.

Grant the roles to users at
the time of the users’ creation.

Imagine the number of scripts
I would have to execute in the database if I didn’t have a setup like this and
I had to grant various table level permissions on all created/ re-created
schema tables to all the users.

If required, create a
procedure to grant various roles to users.

declare

begin

for u1 in (select username from all_users

  where username in upper(”)) loop /* Here supply the username */

for p1 in (select username from all_users

                where username in (‘TOM’,’DICK’,’HARRY’)) loop

/* uncomment necessary grant of roles and comment unnecessary grant of roles in this script */

–execute immediate ‘grant ‘||p1.username||’_select to ‘||u1.username;

–execute immediate ‘grant ‘||p1.username||’_sinsert to ‘||u1.username;

execute immediate ‘grant ‘||p1.username||’_siud to ‘||u1.username;

end loop;

end loop;

end;

Wherever specific privileges
are needed, grant the special privileges to specified users separately.

For password management,
create a default profile with required limits on password management and

assign it to the users in the
database.

A sample profile:

Create profile apps_developer limit

 Failed_login_attempts 3

 Password_lock_time 3

 Password_life_time 30

 Password_grace_time 3

 Password_reuse_time 150

 Password_reuse_max 5 ;

If only one user was created
in the database, this single user owns all of the schema objects. Moreover, if
all of the users are connecting to the database as a single schema owner, then
where is the need for further simplification?

In Part II, I will be dealing with some of the data models for accomplishing column level and row level data security. Please refer to Oracle’s Metalink for a detailed discussion on Virtual Private Database setup.

I will be elaborating on how to grant TRUNCATE permissions on third party tables to users–what are the traps a DBA should avoid while granting TRUNCATE permissions on third party tables to users and what are best practices.