Transcript A Locksmith's Guide to SoD

A Locksmith’s Approach to
Separation of Duties (SoD)
Presented by: Rebecca Bond
a.k.a.
DB2Locksmith
[email protected]
Phone: 434-322-0070
What is SoD?
According to the SANS Institute of Technology:
“Separation of duties is a classic security principle to
manage conflict of interest, the appearance of
conflict of interest, and fraud. It restricts the amount
of power held by any one individual. It puts a barrier
in place to prevent fraud that may be perpetrated by
one individual. Fraud will still occur if there is
collusion. To properly identify separation of duties
issues, you will first need to create an information
flow diagram for every function within each area of
the organization. ”
Why Are DBAs Involved Now?
DBAs should have always been involved in
enforcing appropriate SoD, but the ability to
do so had been limited, not just in DB2, but in
most database products.
With breaches increasing, fraud on the rise and
concerns about data loss, implementing SoD
for databases is a logical step toward
enhancing security.
Separation of Duties (SoD)
 Supports the security concept of Least Privilege
 Separation of Duties serves to lessen risk by
dividing the work and re-assigning some of the
“power”
 For example, do your DBAs really need to query
user data to do their job? If not, why continue to
allow them to have access?
 What about security tasks? Should these all reside
within the control of one individual or group?
 With DB2 9.7, SoD can be used to reduce these
risks and make sense of the term “Least Privilege”
History
BEFORE:
• SYSADM authority included the ability to configure, start, stop,
prune or extract any audit data as well as the ability to access and
manipulate data for any database in that instance.
• DBADM authority, the highest database authority, conferred the
ability to access data.
• Even though it might not have been wise or necessary, many shops
allowed DBAs to hold SYSADM (which also implicitly conferred
DBADM) just to make sure anyone on the DBA team could do
anything they might possibly need to do in the day-to-day
performance of their tasks.
In Rides the SECADM
• Beginning in DB2 9.1, a new security
functional job designation was introduced,
that of the Security Administrator
(SECADM) for the database.
• With DB2 9.7, the SECADM tasks are
intensified and Separation of Duties
abilities are specifically introduced.
Current Day
• The SYSADMs no longer implicitly get DBADM authority,
which means they don’t automatically get data access.
• DBADM, has changed significantly in a manner that
tracks well to SoD security concepts and practices.
• New options in the “grant DBADM…” statement can be
specified to prevent those who hold DBADM authority
from also having the ability to access database user
data in user defined tables (DATAACCESS) or perform
grants and revokes (ACCESSCTRL).
• The specific SoD benefit, depending on how the DBADM
authority is granted in a DB2 9.7 database, is that holders
of that authority do not necessarily acquire the ability to
access user data or to perform grants/revokes.
With DB2 9.7, the SECADM
functional role has been
separated from the DBADM
functional role and the two no
longer need to overlap in
order to complete security tasks.
• There are also some new and re-defined authorities.
Some were carved out from DBADM authority.
Whether new or re-vamped, all of them lend
themselves to the Separation of Duties approach to
task delegation including:
• DATAACCESS conveys:
– LOAD authority
– SELECT, INSERT, UPDATE, DELETE privileges on tables, views,
MQTs and nicknames
– EXECUTE on packages and routines (except audit routines)
• ACCESSCTRL authority
– Gives the ability to grant/revoke database privileges
• EXPLAIN authority
– Allows the holder to EXPLAIN, PREPARE and DESCRIBE SQL
statements. (Note: This authority does not include explicit ability
to execute the SQL.)
• WLMADM authority
– Allows management of workload objects for the database.
• SQLADM authority
– Ability to manage event monitors
– Ability to manage package and optimization caches
– Also conveys EXPLAIN authority (see above)
• DATAACCESS and ACCESSCTRL authorities
will be granted to ids that currently hold
DBADM.
• DBADM, DATAACCESS, and ACCESSCTRL
are granted to the SYSADM GROUP.
• If migrating from a version of DB2 prior to
9.1, or a database that does not have a
SECADM authority assigned, then, by default,
the SECADM authority for the migrated
database will be granted to the user id
performing the migration.
Limiting the DBAs
Do you really want DBADMS to have it all?
Who holds DBADM? It’s a good idea to check. You might
be surprised.
Determine what authority “must” be held by each specific
DBA and assign only that and nothing more.
> > > Reducing authority does not imply mistrust < < <
Don’t “over” grant. It’s not wise and not necessary
Grant ACCESSCTRL and DATAACCESS authority separately
Only Grant DBADM …..
without ACCESSCTRL and DATAACCESS
Do DBAs Really Need It All?
In the past, DBADM authority was granted to
DBAs because there was no other appropriate
High Level Authority.
With DB2 9.7, the SECADM can Grant DBADM
without also granting
DATAACCESS or ACCESSCTRL
Who is that DBADM Dude?
#1) What are the AUTHIDs on the
database and what are their
AUTHIDTYPES?
SELECT char(AUTHID,35) as AUTHID, CASE
AUTHIDTYPE when 'U' then 'USER' when
'G' then 'GROUP' when 'R' then
'ROLE' ELSE 'ERROR' END as
AUTHIDTYPE from
SYSIBMADM.AUTHORIZATIONIDS
#2)
What authorizations have been granted
to those ids?
For Example….To see what authorizations the
USER, LOCKSMITH, has been granted, you
could run:
SELECT AUTHORITY, D_USER, D_GROUP, D_PUBLIC,
ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE
FROM TABLE
(SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID
('LOCKSMITH', 'U') ) AS T ORDER BY
AUTHORITY
#3) What ids have had DBADM
granted to them via Direct
Grants?
SELECT DISTINCT GRANTEE,
GRANTEETYPE FROM SYSCAT.DBAUTH
WHERE DBADMAUTH = 'Y'
•
•
•
•
•
•
•
•
•
•
DBADMs can:
Create, alter, and drop non-security DB objects
Read log files
Work with event monitors (create, alter, drop)
Query tablespace states
Update log history files
Quiesce a tablespace
Reorganize tables
Runstats on catalog
SQLADM authority and WLMADM authority are
subsets of the DBADM authority. (WLMADM
authority has the ability to grant USAGE on
workloads.)
DB2 9.7 DBADM
• Cannot be granted to PUBLIC
• Can only be granted or revoked by
the SECADM
• Can be granted to a user, a group, or
a role.
Grant it Right Write Rite
In DB2 9.7, the GRANT DBADM statement is more
robust
$> db2 “grant DBADM on database to
db2locksmith” statement still works, and it
implicitly includes DATAACCESS and ACCESSCTRL
authority.
But thinking about Least Privilege, I can instead use:
$> db2 "grant dbadm without accessctrl without
dataaccess on database to db2locksmith”
I want to take some authority back
$> db2 revoke dbadm on database from locksmith
$> db2 "SELECT char(AUTHORITY,35) AUTHORITY, D_USER
FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID
('LOCKSMITH', 'U') ) AS T
where AUTHORITY in ('ACCESSCTRL','DATAACCESS','DBADM') ORDER
BY AUTHORITY"
AUTHORITY
----------------------------------ACCESSCTRL
DATAACCESS
DBADM
D_USER
-----Y
Y
N
$> db2 "REVOKE ACCESSCTRL, DATAACCESS ON DATABASE FROM
locksmith"
My authority or yours?
Some GREAT Aids for achieving Least Privilege:
SQLADM
A Subset of DBADM
Lets others Monitor & Tune SQL Statements
This Authority includes EXPLAIN authority
Gives authority to do all tasks surrounding tuning,
even runstats/reorgs
WLMADM
Allows the holder to create, alter, drop, comment on, and
grant and revoke access to workload manager objects.
Also provides execute auth for the system defined
Workload Management routines.
Can be granted by the SECADM or someone who holds
ACCESSCTRL authority.
Can be granted to a user, a group, a role, or to PUBLIC
(I would not suggest you grant to Public)
Explain it
What you can say to Developers and Testers:
“EXPLAIN yourself”
The EXPLAIN authority level provides administrative
authority to explain query plans without gaining
access to data.
EXPLAIN is also granted as part of the SQLADM
authority
Locksmith’s Corp
The Locksmith Says:
Starting with SYSADM
• Assigned via Group Membership
• One of the DBAs (DBA1) will need to hold
SYSADM.
• db2 "update dbm cfg using SYSADM_GROUP
SGURU_Grp” (DBA1 is a member of that
group).
Note: Windows -- LocalSystem account is
considered a SYSADM if DBM sysadm_group is
not specified.
db2 "select char(grantee,30) grantee from
syscat.dbauth where securityadmauth = 'Y'"
GRANTEE
-----------------------------LOCKSMITH
A BUSY SECADM
DBA2 is a top DBA. She does not perform grants
or revokes and does not need to query data.
She needs:
• Create, alter, and drop non-security DB objects
• Read log files
• To work with event monitors (create, alter, drop)
• Query tablespace states
• Update log history files
• Quiesce a tablespace
• Reorganize tables & Runstats on Catalog
DBA2’s Authority
db2 connect to mydb user locksmith
db2 create role topdba
db2 grant dbadm without dataaccess without
accessctrl on database to role topdba
db2 grant role topdba to DBA2
Busy DBAs
To keep development going, some DBAs need to
have the ability to grant/revoke. They also
need to be able to do all SQL tuning tasks and
peform reorgs/runstats.
db2 create role appdb
db2 grant accessctrl, sqladm on database to role
appdb
Busy Developers
Developers probably should not be
working in production, but even Dev and
Test environments should have security
controls. If nothing else, enforcing LEAST
PRIVILEGE can save some unintended
consequences…like updates to an
incorrect table.
Protect Developers & Yourself!
db2 create role devusr
db2 create role devtbls
db2 create role devexpln
What now?
db2 grant connect on database to role devusr
db2 grant select, insert on locksmith.employee to role
devtbls
db2 grant select on locksmith.sales to role devtbls
db2 grant select on syscat.tables to role devtbls
db2 grant explain on database to role devexpln
db2 grant role devusr, devtbls, devexpln to Fred
db2 grant role devusr, devtbls to Rebecca
Possibilities ?
As you can see, you can get very granular with
your approach separation of duties.
Using Roles, you could do things like:
Create Separate Roles for:
Explain
Accessctrl
Dataaccess
And then grant only what is needed
Over Granting
Time for the Breakup!
There is so much granularity available, there is
really no need to over grant.
And the ability to use Roles means that you have
a Plug and Play Approach
Roles can be Granted to form a Role Hierarchy
Revoking is so much easier when you use Roles
as well.
The Benefit
The ability to use these new features of DB2 9.7,
especially when coupled with the use of Roles
that were introduced in DB2 9.5, allows a
better match between job responsibilities and
database access.
The granularity is significant and allows fine
grained control.
Use it to your advantage.
A Locksmith’s Approach to Separation of Duties (SoD)
Rebecca Bond, CISSP
A.K.A. DB2Locksmith
434-DB2-0070
[email protected]