by: JP Vijaykumar
A detailed discussion on the
setup and administration of database access and security is beyond the scope of
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:
User account management
Creating users, roles,
table level / system level privileges to users / roles
Drop / Lock users. Password management.
Protecting sensitive /
confidential data using column level / row level security
by implementing Virtual Private Database / or custom built
0202 Granting Truncate privileges on tables in others’
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:
|TOM_SELECT||SELECT PERMISSION ON ALL TABLES IN TOM’S SCHEMA|
|TOM_SINSERT||SELECT/INSERT PERMISSIONS ON ALL TABLES IN TOM’S SCHEMA|
|TOM_SIUD||SELECT/INSERT/UPDATE/DELET PERMISSIONS ON ALL TABLES IN TOM’S SCHEMA|
|DICK_SELECT||SELECT PERMISSION ON ALL TABLES IN DICK’S SCHEMA|
|DICK_SINSERT||SELECT/INSERT PERMISSIONS ON ALL TABLES IN DICK’S SCHEMA|
|DICK_SIUD||SELECT/INSERT/UPDATE/DELET PERMISSIONS ON ALL TABLES IN DICK’S SCHEMA|
|HARRY_SELECT||SELECT PERMISSION ON ALL TABLES IN HARRY’S SCHEMA|
|HARRY_SINSERT||SELECT/INSERT PERMISSIONS ON ALL TABLES IN HARRY’S SCHEMA|
|HARRY_SIUD||SELECT/INSERT/UPDATE/DELET PERMISSIONS ON ALL TABLES IN HARRY’S SCHEMA|
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.
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’;
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.
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;
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
A sample profile:
Create profile apps_developer limit
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.