By
Granville Bonyata
Summit Software Design,
Inc.
The security problem: A user needs the ability
to change data through the application, so
the DBA grants the user database privileges
to enter, update and delete data. Since your
users have database privileges that allow them
to enter and update data, how do you prevent
them from bypassing the application business
rules and changing data through SQL*Plus or
some other 3rd party tool?
Fortunately, the answer is simple. We don't
need to prevent the user community from having
access to these tools. Instead, Oracle's non-default
roles feature gives you a method to allow data
to be changed in one application, while preventing
any changes from outside that application.
The premise behind this is that the user is
granted a role (or roles) that allows queries.
This role is a default role that the user always
has upon logging in. The user is also granted
a role that allows data to be changed. This
role is NOT a default role. Instead, this role
has to be explicitly enabled for the session.
Even this would not stop a savvy user from
changing data, since they could simply learn
the command to enable a role. The final piece
to lock-down your data is to protect the role
so that a password is required to enable the
role.
There are three separate sets of steps to
enact this.
- Set up your roles, with at least one query
only role, and at least one role that allows
data to be changed and is password protected.
- When creating a user the query role is
created as a default role, the update/insert
role as a non-default role.
- Every time a user logs in to your application,
enable the non-default 'power' role that
allows data updates.
The code to implement this is:
Step 1 - Create the roles:
CREATE ROLE my_query_only_role;
CREATE ROLE my_update_role IDENTIFIED
BY my_role_password;
GRANT select ON table-name TO my_query_only_role;
GRANT insert, update, delete, select ON table-name
TO my_update_role;
Step 2 - Grant the roles:
After creating user 'the_user':
GRANT my_query_only_role TO the_user;
GRANT my_update_role TO the_user;
ALTER USER the_user DEFAULT ROLE ALL
EXCEPT my_update_role;
Step 3 - Enable the role when logging in to
the application:
Immediately after logging in to the application
call an Oracle built-in package:
dbms_session.set_role('connect, resource,
my_query_only_role,
my_update_role
identified by my_role_password');
Now the user can use any of the very useful
3rd party tools without any concern on your
part for the integrity of your data.
For more information on making your Oracle
application secure, please e-mail
or call Summit Software Design at 727-823-1000.
|
