----Original Message----
From: MacGregor, Ian A.
Sent: Tuesday, August 07, 2007 11:34 AM
To: Heidenreich, Karen A.
Subject: RE: Development Accounts and Passwords
Developer1 can change only change developer1's password. Developer2 can change only developer2's password. Owner1 can change only owner1's password.
Ian
----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: Johnson, Tony S.
Sent: Monday, August 06, 2007 3:06 PM
To: Heidenreich, Karen A.
Subject: FW: Development Accounts and Passwords
Know anything about proxy logins?
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
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