 |
|
Oracle
Reporting Privileges
Security
on Roles
Oracle Security Tips by
Burleson Consulting |
This is an excerpt from the
bestselling book "Oracle
Privacy Security Auditing", a complete Oracle security reference
with working Oracle security scripts.
Reporting Privileges on Roles
The following query shows the privileges
granted to users and other roles.
*
role_privs.sql
--**********************************************
--
-- Copyright © 2003 by Rampant TechPress Inc.
--
-- Free for non-commercial use.
--
For commercial licensing, e-mail
info@rampant.cc
--
-- *********************************************
select
grantee
role_name,
privilege
privilege,
admin_option admin_grantable
from
dba_sys_privs
where
grantee in
(
select
role
from
dba_roles
)
UNION ALL
select
grantee
role_name,
privilege
privilege,
grantable
admin_grantable
from
dba_tab_privs
where
grantee in
(
select
role
from
dba_roles
)
/
The view dba_role_privs shows the roles granted
to users and other roles. The following query determines the roles
granted to different users and other roles.
break on
grantee skip 1
select
grantee,
granted_role,
admin_option,
default_role
from
dba_role_privs
where
grantee not in ('SYS','SYSTEM')
order by
1,2
/
Here is a
sample output:
GRANTEE
GRANTED_ROLE
ADM DEF
-------------------- ------------------------------ --- ---
DBA
DELETE_CATALOG_ROLE
YES YES
EXECUTE_CATALOG_ROLE
YES YES
EXP_FULL_DATABASE
NO YES
GATHER_SYSTEM_STATISTICS NO
YES
IMP_FULL_DATABASE
NO YES
JAVA_ADMIN
NO YES
JAVA_DEPLOY NO YES
PLUSTRACE
YES YES
SELECT_CATALOG_ROLE
YES YES
DBSNMP
CONNECT
NO YES
DPAUL
DWREADER
NO YES
DSHERMAN
DWREADER
NO YES
DWOWNER
PLUSTRACE
NO YES
EXECUTE_CATALOG_ROLE HS_ADMIN_ROLE
NO YES
EXP_FULL_DATABASE EXECUTE_CATALOG_ROLE
NO YES
SELECT_CATALOG_ROLE
NO YES
IMP_FULL_DATABASE EXECUTE_CATALOG_ROLE
NO YES
SELECT_CATALOG_ROLE
NO YES
JAVASYSPRIV
JAVAUSERPRIV
NO YES
LOGSTDBY_ADMINISTRAT CONNECT
NO YES
OR
RESOURCE
NO YES
Note the grantee column lists other roles too.
For the sake of simplicity, remember only three
views:
* dba_sys_privs - For system privileges granted
to various users and roles.
* dba_tab_privs For object privileges and
roles granted to users and roles.
* dba_role_privs For roles granted to users
and other roles.
In all these views, the GRANTEE is the user or
role that received the privilege and the GRANTOR is the user who
granted it. The OWNER column indicates the owner of the object that
was granted.
Oracle Built-In Roles
Oracle provides several roles that are built
into the database. Some of them are DBA, RESOURCE, and CONNECT. Most
DBAs use them to make their tasks easier and simpler, but each of
them is a security nightmare.
Let's examine RESOURCE. This is generally given
to schema owners. Did you know that it has UNLIMITED TABLESPACE
system privilege, making it able to create any table anywhere in the
database including the SYSTEM tablespace? Obviously, this is not
what you want. You would want to restrict the tablespaces to
specific users only.
Similarly the role CONNECT, by default, has the
CREATE TABLE/SEQUENCE/SYNONYM and a few more options. The name
CONNECT somehow conveys the impression of the ability to connect
only, not anything else. As you can see, however, the ability is
much more than that. Another privilege, ALTER SESSION system
privilege, allows the grantee to issue sql_trace = TRUE in their
session. This can have far reaching consequences.
Therefore, it is not prudent to use built-in
roles. Rather, identify the privileges users will need, put them in
the appropriate roles which you have created, and use them to
control authorization.
* If possible, try not to use the Oracle
built-in roles like RESOURCE and CONNECT. Create your own roles.
Object Masquerading
We briefly discussed this topic in the
introduction. Although this is not used much, object masquerading
can be a very effective tool to discourage malicious users from
getting what they want.
Think of an example if a burglar breaks into
your house to rob the family safe, would he or she look inside the
laundry closet? Probably not. The thief will look for a box-like
object that may appear similar to the thiefs image of the safe. If
the safe was designed to look like, say, a dirty clothes hamper,
with some actual dirty underwear on the top, the thief would
probably never be able to guess that, and thus the safe will be
indeed exactly that - safe. (Now everybody knows where the safe is
in my house! However, I assure you, there is nothing of value
inside, just a few stones my wife collects!)
A similar concept can be applied to database
objects, too. A hacker will look for a table named credit_card or
something similar. If the credit card numbers are stored in a table
named process_data, the hacker will most likely ignore it. However,
all the legitimate users will know exactly where to find the data.
This technique not only applies to table names, but to column names
as well, e.g. the credit card numbers are represented by a column
name PROC_DATA_VALUE, or something similar.
This technique of hiding an object behind a
meaningless name is adapted from the concept of camouflage in
everyday life. Although it may be a little complex to use, in a
small development group this can easily be implemented. The added
advantage of this object masquerading as a form of security is that
it protects against the prying eyes of internal employees who have
no authorization to look into the database, but are not necessarily
hackers.
HIPAA rules specify that the database be
accessible only by authorized users. All users without any
authorization to the database, whether they are benign, like a
curious internal employee, or a jumpy teenage hacker trying to prove
his coming of age to his girlfriend, or malignant hackers trying to
steal credit card and health information with the intent to profit
from it, should find access to the database very difficult. Object
masquerading will help to prevent disclosure of protected
information to these types of intruders.
* If possible, use a misleading name for tables
and columns containing sensitive data to fool intruders. This
follows the principle of "Security by Obscurity"!
Profile-Based Security
We discussed the use of profiles in the
password section. However, in addition to the password enforcement
functions, profiles can be used to enforce the HIPAA security
requirements very effectively. The law says that you have to make
adequate arrangements to ensure that malicious persons do not abuse
connections to the database.
The following parameters can be controlled by
profile.
sessions_per_user Hackers typically break
into the database using a username, not by stealing the SYS
password. Internal employees also break into the database using a
user id they already know. In the case of lax users, the password
may be stolen and the hackers may use their password.
What if we limit the number of sessions a user
can have active at any point in time? We know beforehand how many
sessions a specific user needs. Typically, a senior claim
analyst uses a session to do his or her work, and perhaps opens up
another to answer a question from a junior analyst. A maximum of two
sessions, then, is adequate for a senior analyst, but only one is
adequate for a junior analyst. Profiles are used to limit the number
of concurrent sessions for a specific username. This parameter
specifies that limit.
connect_time The other technique that hackers
employ is using the database session of other legitimate users after
their regular work. However, if there is a limit on the maximum
amount of time a user session can stay connected to the database,
the sessions are automatically disconnected.
HIPAA rules do not mandate this, but they do
recommend using some sort of mechanism to limit the time so that a
malicious intruder has fewer resources. This parameter in the
profile enforces that limit. Expressed in seconds, it limits the
maximum time a user can stay connected to the database. After this
limit expires, the sessions are automatically disconnected.
In this case, let's assume the senior claim
analyst connects at 8 in the morning, goes to lunch at 12 noon,
comes back at 1 and works till 5. Therefore, she works for only 4
hours at a stretch. Given another hour for some extra work, 5 hours
should be the maximum time for the senior claim analyst to work, and
that should be the limit. Expressed in seconds, it is 5 times 60
times 60, i.e. 180000.
idle_time Hackers typically wait for a
legitimate user to be connected but idle. Then they hijack the
session to carry out their task. Idle time while connected is one of
the most dangerous periods for handling security. This
parameter limits the maximum time a session can stay connected
without doing anything. By reducing the period of time an inactive
session stays connected, the probability of that session being a
victim of abuse is reduced.
The profile also has other parameters, but only
these are relevant for security.
Based on our decision, we can create the
profile to enforce it. Since we already have the profile, we will
merely update it with the new parameters.
alter profile
senior_claim_analyst limit
Connect_time 180000
Sessions_per_user 2
Idle_time
1800
/
Voila! You have immediately achieved a very
important requirement of HIPAA, to limit user activity to discourage
the malicious intruder.
We have seen three separate discussions on
profile-based security the password function, the password
management guidelines, and the database usage. Let's put them
together to see a complete profile definition.
*
create_policy.sql
--**********************************************
--
-- Copyright © 2003 by Rampant TechPress Inc.
--
-- Free for non-commercial use.
-- For commercial licensing, e-mail
info@rampant.cc
--
-- *********************************************
CREATE
PROFILE SENIOR_CLAIM_ANALYST LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 30
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME 1/2/24
PASSWORD_GRACE_TIME 5
PASSWORD_VERIFY_FUNCTION PASSWORD_CHECK
CONNECT_TIME
180000
SESSIONS_PER_USER 2
IDLE_TIME
1800
/
If the profile is already created, you can add
or change the parameters by using the ALTER PROFILE command. In the
same manner, identify and classify all your users to several
profiles and assign resource limits to each profile.
* Use Profiles to enforce connection limits to
the database.
 |
This is an excerpt
from the book "Oracle
Privacy Security Auditing". You can buy it direct from the
publisher for 30%-off and get instant access to the code depot
of Oracle security and auditing scripts. |
