Re: Oracle Security Best Practices

From: Rachel Carmichael <wisernet100_at_yahoo.com>
Date: Thu, 07 Aug 2003 12:04:30 -0800
Message-ID: <F001.005C9822.20030807120430@fatcity.com>

if this is 9i, anyone with DBA privs can grant access...

grant select, update, delete, insert on <table_owner>.<table_name> to <grantee>;

we have a hosting company that manages the staging and production databases. I do not hard-code passwords into scripts when I pass them to the hosting company. They do not give me the password to SYSTEM. But I can and do code (at the beginning of every script):

prompt please enter the passwords and connect string as password_at_connect_string
prompt
accept sysconn prompt "system password and connect string: " set echo on term on verify off
connect system/&sysconn

in my scripts and then do the grants and whatever else I need as system

if you aren't on 9i... well option 1 below was documented (with a script so you don't have to write the encrypted password down!) in Kevin Loney's 7.3 version of the DBA Handbook. I don't know (latest versions are at home) if he kept it in the later versions of the book or not)

• Jose Luis Delgado <joseluis_delgado_at_yahoo.com> wrote:
  > --- SSILVA9_at_BKB.com.br wrote:
  > >
  > > We are having problems creating the objects
  > > and granting the
  > > access. We figured out that our problem is granting
  > > ...
  > > because the only one that
  > > can grant access to the object is the owner,
  > > correct?
  > >
  > > How do you (as DBA) create objects and give
  > > its permissions? With
  > > the owner's login?
  > >
  > Sandro...
  > you have at least two choices:
  >
  > 1.-
  > a) select encrypted password from dba_users
  > write it on your notebook.
  > b) alter the user password to whatever you want.
  > c) connect as the user
  > d) perform the grant
  > e) alter the password back to the original value
  > using
  > alter user xxx identified by values 'old_password'
  > where old_password = the one that you wrote on
  > the notebook
  >
  > 2.- create a procedure to do the grant...
  > a) create or replace procedure
  > whatever_user.grant(priv varchar2) is
  > begin
  > execute immediate priv;
  > end;
  >
  > b) exec whatever_user.grant('grant select on emp to
  > Sandro');
  >
  >
  > HTH
  >
  > JL
  >
  >
  > __________________________________
  > Do you Yahoo!?
  > Yahoo! SiteBuilder - Free, easy-to-use web site design software
  > http://sitebuilder.yahoo.com
  > --
  > Please see the official ORACLE-L FAQ: http://www.orafaq.net
  > --
  > Author: Jose Luis Delgado
  > INET: joseluis_delgado_at_yahoo.com
  >
  > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
  > San Diego, California -- Mailing list and web hosting services
  > ---------------------------------------------------------------------
  > To REMOVE yourself from this mailing list, send an E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB ORACLE-L
  > (or the name of mailing list you want to be removed from). You may
  > also send the HELP command for other information (like subscribing).
  

  ---

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Aug 07 2003 - 15:04:30 CDT