Oracle Passwords, Note 1 (Proxy logins)

Version 1 by Tony Johnson
on Jan 23, 2008 09:30.

 
compared with
Current by Tony Johnson
on Jan 23, 2008 09:42.

 
Key
These lines were removed. This word was removed.
These lines were added. This word was added.

View page history


There are 11 changes. View first change.

  
  
 -----Original Message-----
 From: MacGregor, Ian A.
Sent: Friday, June 22, 2007 8:28 AM
 To: Ponton, Richard; Chan, Andrea; Larrieu, Heather; Cowles, Robert D.; Crane, George R.; Samineni, Venkata; Hutcherson, Jackie R.; Shab, Theodore; Johnson, Tony S.; Heidenreich, Karen A.; Rock, Judith E.; Denys, Ernest; Hee, Charlotte; Chestnut, Ronald P.; Pierre, Jean-Raymond; Gordon, Michael
 Subject: RE: Oracle password meeting notes 6/19/2007
  Sent: Tuesday, August 07, 2007 11:34 AM
 To: Heidenreich, Karen A.
 Subject: RE: Development Accounts and Passwords
  
Oracle has introduced an external password store to deal with this problem. Passwords are stored in an SSL protected wallet. The file simply has /@database identifier. I have tried this on some Oracle servers themselves and it worked.
  Developer1 can change only change developer1's password. Developer2 can change only developer2's password. Owner1 can change only owner1's password.
  
There are issues such as how we get passwords into the wallet. How to handle databases which need more than one such password. I think hoiwever these can be worked around. See
  Ian
  
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#sthref484
  -----Original Message-----
 From: Heidenreich, Karen A.
 Sent: Tuesday, August 07, 2007 10:45 AM
 To: MacGregor, Ian A.
 Subject: RE: Development Accounts and Passwords
  
Hi Ian,
 Tony passed this on to me. My first question:
 Can developer1 and developer2 change the passwords to the accounts? Or does owner1 have control over them?
 K
  
 -----Original Message-----
From: Ponton, Richard
 Sent: Tuesday, June 19, 2007 4:19 PM
 To: Chan, Andrea; Larrieu, Heather; Cowles, Robert D.; Crane, George R.; MacGregor, Ian A.; Samineni, Venkata; Hutcherson, Jackie R.; Ponton, Richard; Shab, Theodore; Johnson, Tony S.; Heidenreich, Karen A.; Rock, Judith E.; Denys, Ernest; Hee, Charlotte; Chestnut, Ronald P.; Pierre, Jean-Raymond; Gordon, Michael
 Subject: Oracle password meeting notes 6/19/2007
  From: Johnson, Tony S.
 Sent: Monday, August 06, 2007 3:06 PM
 To: Heidenreich, Karen A.
 Subject: FW: Development Accounts and Passwords
  
These are my notes on Heather and Bob's words. They may have revisions.
  Know anything about proxy logins?
  
Command Decision:
  
 We do not expire anything on July 2nd.
 Strongly suggest users comply by July 2nd.
 By mid August, come up with documentation on What accounts don't follow the policy.
 Where they're used.
 How the passwords are stored.
 Who has access to those accounts.
 What other steps have we taken to mitigate the risks of a 3rd party gaining access to that account's password.
 Does the account have access to any Business Sensitive or PII data.
  
 Require a plan for correction by 1st quarter 2008
  
 Moving forward, we have to come up with a good way of managing passwords
  
 encrypted
 not in plain text
  Tony
  
-----Original Message-----
 From: MacGregor, Ian A.
 Sent: Monday, August 06, 2007 1:28 PM
 To: Johnson, Tony S.
 Cc: Flath, Daniel; Chan, Andrea; Shab, Theodore; Crane, George R.; Rothacker, Frank
 Subject: Development Accounts and Passwords
  
 See also:
 https://slacspace.slac.stanford.edu/sites/appdevproj/SLACSoftware/Service%20Account%20Details/Meeting%20notes%202007-06-09.aspx
  Tony, you mentioned the problem of distributing passwords to your developers. In Oracle 10g, you can use proxy logins to get around this problem.
  
 Here's an example
  
 I create a user to own the objects
  
 QL> create user owner1 identified by "greenfast1*"
  2 default tablespace users
  3 quota unlimited on users;
  
 User created.
  
 I give that user the ability to create objects
  
 SQL> grant slac_resource to owner1;
  
 I create a developer user
  
 SQL> create user developer1 identified by "redslow^2"
  2 default tablespace users
  3 /
  
 User created.
  
 I create a second developer user
  
 SQL> create user developer2 identified by "bluespeed.3"
  2 default tablespace users
  3 /
  
 User created.
  
 I grant the developer users the ability to connect to the database;
  
 SQL> grant connect to developer1
  2 /
  
 Grant succeeded.
  
 SQL> grant connect to developer2
  2 /
  
 Grant succeeded.
  
 Now for the magic
  
 QL> alter user owner1 grant connect through developer1 with role
 QL> slac_resource;
  
 User altered.
  
 alter user owner1 grant connect through developer2 with role slac_resource;
  
 User Altered
  
 oracle@oracle-dev $ sqlplus developer1
  
 SQL*Plus: Release 10.2.0.3.0 - Production on Mon Aug 6 13:11:17 2007
  
 Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
  
 Enter password:
  
 Connected to:
 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engin e options
  
 SQL> connect developer1[owner1]/redslow^2
 Connected.
 SQL> create table yzyxyz(yz varchar(1));
  
 Table created.
  
 SQL> connect /
 Connected.
 SQL> select owner from dba_tables where table_name = 'YZYXYZ';
  
 OWNER
 ------------------------------
 OWNER1
  
 Note whom the table is owned by
  
  
 oracle@oracle-dev $ sqlplus
  
 SQL*Plus: Release 10.2.0.3.0 - Production on Mon Aug 6 13:16:12 2007
  
 Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
  
 Enter user-name: developer2/bluespeed.3
  
 Connected to:
 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
  
 SQL> create table ababab (ab varchar2(10));
 create table ababab (ab varchar2(10))
 *
 ERROR at line 1:
 ORA-01031: insufficient privileges
  
  
 SQL> connect developer2[owner1]/bluespeed.3
 Connected.
 SQL> create table ababab (ab varchar2(10));
  
 Table created.
  
 SQL> connect /
 Connected.
 SQL> select owner from dba_tables where table_name = 'ABABAB'
  2 /
  
 OWNER
 ------------------------------
 OWNER1
  
 Again note the table owner.
 -------------------------------------------------------------------------
  
 QL> drop user owner1 cascade;
  
 User dropped.
  
 SQL> drop user developer1;
  
 User dropped.
  
 SQL> drop user developer2;
  
 User dropped.
  
 Proxy logins were really created to deal with the problem of auditing transactions when the connection is via service account. Examples of that are all Java-based. What is presented above is an interesting additional capability.
  
  
 Ian