Stricter Oracle security

We are considering tightening up our OneWorld Xe Oracle database security by
removing table privileges from public and granting the privileges to end
users via a number of roles.

There will be two roles per OneWorld environment, one role with full
privileges on all the environment tables, "<ENV>_FULL_ROLE" and another with
read-only privileges on the environment tables "<ENV>_READONLY_ROLE".
(The latter role will allow additional interfaces to the database external
to OneWorld application).

So we're looking to get a few queries answered:
1. Does anyone know if table privileges are granted to all database user
accounts via the PUBLIC user group only? i.e. table privileges are not
granted to OneWorld end user database accounts via other roles or methods?

2. After revoking table privileges from public and granting them to OneWorld
end user database accounts via environment roles (<ENV>_FULL_ROLE ) do we
also need grant these roles to other OneWorld schema owners such as
"SYS7333", "OBJ7333" etc?

3. Similarly, should each environment have privileges on other environment
tables?
E.g. We granted table privileges on the "TESTDTA" environment to end user
database accounts via a role "TESTDTA_FULL_ROLE". Should this role be
granted to the user "PRODDTA" and other schema owners?

4. Granting table privileges to database user accounts via roles has
limitations. Some privileges cannot be granted via roles. By default, the
following privileges are granted on each table to public; SELECT, INSERT,
UPDATE, DELETE, ALTER, REFERENCES, INDEX.
However the privileges REFERENCES and INDEX cannot be granted on tables to
database user accounts via roles.
Granting these privileges directly to database user accounts is not a
preferable.
Do OneWorld end users really require these two privileges? Is it reasonable
to request that end users requiring these privileges connect as the
environment schema owner (e.g. "TESTDTA") to execute these privileges?

Any tips, pointers on any of the above would be greatly appreciated.

PS: If anyone has a copy of the scripts (below) referred to in the Knowledge
Garden document "Implementing Stricter Oracle Security on a OneWorld
Database" March 2002 I would really appreciate if you would please send them
on to me backchannel

CREATE_NEW_ROLES.BAT
CREATE_NEW_ROLES.SQL
UNDO_ALL_PUBLIC.BAT
UNDO_GRANTS.BAT
UNDO_GRANTS.SQL


OneWorld Xe B7333 SP15.2 - Oracle 8.1.7 - W2K
-------------------------
Seán O' Neill
Organon (Ireland) Ltd.
[subscribed: digest mode]


--------------------------------------------------------------------
This message, including attached files, may contain confidential
information and is intended only for the use by the individual
and/or the entity to which it is addressed. Any unauthorized use,
dissemination of, or copying of the information contained herein is
not allowed and may lead to irreparable harm and damage for which
you may be held liable. If you receive this message in error or if
it is intended for someone else please notify the sender by
returning this e-mail immediately and delete the message.
--------------------------------------------------------------------

Hello Sean,

attached you find the document you asked for.

Hello Sean,

sorry for the wrong attachment - I thought you were locking for the documentation.
Maybe you can ask someone from TechSupport EMEA to forward he scripts to you (and then to me... would be great). The scripts should be there:
\EMEA\EMEA\Customer Support\UK Tech\Archive\OneWorld Topics\Oracle.
Search for the subject: 'Call #5077628'

Regards

Here are the scripts mentioned in that document. I haven't tried them, so please be careful with them.

Hi Sean. in response to your questions:

1. OneWorld does not grant any privileges/permissions by default. The JDE database user accounts (or a JDE role you create and assign to those accounts) will need to have permissions granted to them in order to access the tables after you revoke public access.

2. You really don't need to give the schema owners any special privileges or roles (but you SHOULD change thgeir passwords). The only times these accounts are used are when a table or index is created/re-created (OMW for example).

3. "Environment" is the wrong name to use. Rather think "Path Code". At any rate as stated above PD7333 and PRODDTA do not need access to other schemas.

4. It sounds like you have created separate database accounts for every user . . . is that what you did? You really don't need to do this (since you're only now getting around to securing database access your security requirements can't be as stringent as some). Rather have one database account (call it jde_user) that is assigned to all interactive users. The jde_user account has jde_full_role assigned to it. You should have another database account for the system processes (typically jde but you can call it something else). Assign the jde_full_role to the system processes database account also.

Now for every table in all the database schemas execute the following 2 commands:
REVOKE ALL PRIVILEGES ON <SCHEMA>.<TABLENAME> FROM PUBLIC;
GRANT ALL PRIVILEGES ON <SCHEMA>.<TABLENAME> TO jde_full_role;

I know - thats a lot of typing. See attachment for a SQL+ script that will generate the REVOKE and GRANT statements for every OW table.

If I were you I would carefully consider the need for a universal "READONLY_ROLE". I would wait and create specific views that grant access to non-JDE accounts on a as needed basis. Assuming you want this access for reporting purposes.

Hope this helps.