[OAUGNet]-Re: Read-only database account

["Mcpeak, Matthew" <vxsmimmcp@SUBARU.com>]

I don't know of any "best practices", because the whole idea of giving direct SQL access to you production database is contrary to Oracle's best practices for securing your e-Business Suite.  Your database listener is supposed to deny all SQL*Net connections except from known hosts (like the middle tier).

That said, I have been asked by clients to implement this type of access in the past.  Here is how I've done it.

1) Grant SELECT ANY TABLE to the read-only account
2) Configure SQL*Plus (or TOAD or what have you) to issue a ALTER SESSION SET current_schema=apps; at the start of every connection.
3) Use the DBMS fine grained auditing to prevent (and log) attempts to access information that they should not see (e.g., SSNs, credit card numbers, and encrypted passwords, which can be decrypted).

I like this approach over the common alternative ( explicit grants and synonyms ) for the following reasons:

1) It is much easier to maintain
2) The security model is based on a small list of DENY policies instead of a huge list of ALLOW policies.  This makes it much easier to maintain and understand.

Hope this helps.  (I'd welcome suggestions / improvements from interested readers.. )

Thanks,
Matt

mcpeakm@tempus-consulting-group.com


-----Original Message-----
From: OAUG Net listserver [mailto:OAUGNet@oaug.com] On Behalf Of Shingireddy, Ahalya
Sent: Friday, June 20, 2008 4:21 PM
To: OAUG Net listserver
Subject: [OAUGNet]-Read-only database account

What are the best practices done for the read-only database accounts in production while giving access to super-user/developers? This came up as a part of our audit process.

I am looking for a script to create a read-only account for apps, which can access (select only) from all apps tables without specifying the schema owner.  Can anybody please share the script if you already have one?

Thanks for your suggestion.
Ahalya


#############################################################
This message is sent to you because you are subscribed to the mailing list <OAUGNet@oaug.com>.
To unsubscribe, E-mail to: <OAUGNet-off@oaug.com> To switch to the FEED mode, send any message to <OAUGNet-feed@oaug.com> To switch to the DIGEST mode, E-mail to <OAUGNet-digest@oaug.com> To switch to the INDEX mode, E-mail to <OAUGNet-index@oaug.com> Send administrative queries to  <OAUGNet-request@oaug.com>



#############################################################
This message is sent to you because you are subscribed to the mailing list <OAUGNet@oaug.com>.
To unsubscribe, E-mail to: <OAUGNet-off@oaug.com>
To switch to the FEED mode, send any message to <OAUGNet-feed@oaug.com>
To switch to the DIGEST mode, E-mail to <OAUGNet-digest@oaug.com>
To switch to the INDEX mode, E-mail to <OAUGNet-index@oaug.com>
Send administrative queries to  <OAUGNet-request@oaug.com>