July 2003

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

July 2003
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

NYOUG Technical Journal Archives

The New York Oracle Users Group has developed an online collection of technical papers that have been published in their newsletter, “The Technical Journal.” Here is a quick sampling from the NYOUG Technical Journal archives: Forums at DBAsupport.com

  • Applying a Blockcentric Approach to Oracle Tuning
    In this paper, Dan Fink presents a new approach to Oracle application tuning that “shifts the focus away from rows and towards blocks.” See what Dan has to say at http://www.nyoug.org/200303fink.pdf.
  • Cats, Dogs, and ORA-01555
    Tim Gorman takes a look at the sometimes tenuous relationships between DBAs and developers, starting off with some differing perspectives on the “snapshot too old” error. Read this entertaining paper from Tim at http://www.nyoug.org/200303gorman.pdf.

Other topics include:

  • Digging Deeper: Segment Level Statistics in Oracle 9iR2
  • Getting Started with Oracle 9i’s XML DB Functionality
  • Query Tuning Using Advanced Hints

You can browse the full archives at http://www.nyoug.org/techjournal.htm.

 

At DBAsupport.com, they call their discussion forums “The Knowledge Center for Oracle Professionals.” You’ll find sections called Oracle9i Central, Oracle8i Central, Scripts, and Oracle News. Some of the latest articles on the home page of DBAsupport.com include: Upcoming Oracle User Group Conferences

  • Automating ETL using Oracle Warehouse Builder
  • It All Depends on the CONTEXT: Using the SYS_CONTEXT Function
  • Understanding Oracle’s Locally Managed Tablespaces
  • Using Oracle Locks to Manage Data Concurrency and Consistency
  • Returning Rows Through a Table Function in Oracle

Check out the site at http://forums.dbasupport.com.

 

Two information-packed user group conferences will take place in California next month, and you can meet the Database Specialists team at each of them. At both conferences, senior DBAs Roger Schrag and Terry Sutton will be leading a two-hour technical session on Oracle performance tuning using the wait event interface. This is an advanced session with real-life experiences and practical techniques. Don’t miss it! Ask DB Specialists: Third Party Grants

 

  • The Los Angeles Oracle Users Group (LAOUG) meets on Thursday, August 14 in Norwalk, California.
  • The Northern California Oracle Users Group (NoCOUG) meets Thursday, August 21, 2003 in San Ramon, California. Check out NoCOUG’s website at http://www.nocoug.org.

Both events will be packed with technical sessions, vendor exhibits, and more! Be sure to drop by the Database Specialists booth in the vendor exhibit area and say hello!

 

Thanks to Alan M. for submitting last month’s question on system change numbers. We’re sending Alan a free copy of our popular Oracle Wait Events reference poster. You’re invited to submit your Oracle-related questions to us at askdbspecialists07@dbspecialists.com. Be sure to include your name and telephone number with your question. If your question gets published in The Specialist, you’ll receive a free copy of our reference poster on Oracle Wait Events. So, stay tuned for future issues of The Specialist!

This month’s question comes from Scott E. in Boston, Massachusetts: They say in Oracle 9i you can finally do “third party grants.” This is where a DBA logs in as user A and grants a privilege on user B’s table to user C. Oracle 8i and earlier do not support third party grants, so you would need to log in as user B to grant a privilege on a table owned by user B. Are there any tricks a DBA can use in Oracle 8i to accomplish a third party grant?

Ian Jones of the Database Specialists team responds: Yes, there are a few techniques to choose from. But first, let’s briefly mention the enhancement added in Oracle 9i Release 2: There is a new system privilege called “grant any object privilege” that is granted to the DBA role and directly to users SYS, MDSYS, WKSYS and CTXSYS. This new privilege enables third party grants in Oracle 9iR2. E.g.:

CONNECT B/password
CREATE TABLE B.t1 (c1 VARCHAR2(10));
CONNECT A/password
GRANT SELECT ON B.t1 TO C;

On a system prior to Oracle 9iR2 the GRANT statement gives the error “ORA-01031: insufficient privileges.” For databases prior to Oracle 9iR2 we will walk through three workarounds to issuing third party grants.

Option 1: Use DBMS_SYS_SQL.parse_as_user to issue grants
This option requires execute privilege on the undocumented DBMS_SYS_SQL package which by default is very restricted. However, if we are willing to allow access to this package it can be used to perform third party grants. E.g.:

CONNECT / AS SYSDBA
DECLARE 
  l_grant_sql      VARCHAR2(250);
  l_cursor_id      INTEGER;
  l_user_id        INTEGER;
BEGIN
  l_grant_sql := 'GRANT SELECT ON B.t1 TO C';
  SELECT user_id INTO l_user_id 
  FROM dba_users WHERE username = 'B';
  l_cursor_id := SYS.dbms_sys_sql.open_cursor ();
  SYS.dbms_sys_sql.parse_as_user 
    (l_cursor_id, l_grant_sql, dbms_sql.native, l_user_id);
  SYS.dbms_sys_sql.close_cursor (l_cursor_id);
END; 
/

Option 2: Temporarily become user B to issue grants on B’s tables
This option works around the restriction by temporarily becoming the schema that owns the objects i.e. schema B, and issuing the grants from that schema. This is achieved by:

  1. Storing away the existing encrypted password for schema B from dba_users
  2. Changing the password of schema B to an arbitrary known password
  3. Connecting to schema B and quickly changing the password back to its original encrypted value
  4. Issusing the grants

Two important things to watch out for are to ensure that you correctly perform step a, and that you minimize the duration of steps b and c (users trying to connect to schema B using the original password will fail between steps b and c). E.g.:

CONNECT A/password
SELECT username, password FROM dba_users
WHERE  username = 'B'; 
   USERNAME PASSWORD
   -------- -----------------
   B        EB11FDB815CB0D37
ALTER USER B IDENTIFIED BY [new_password];
CONNECT B/[new_password]
ALTER USER B IDENTIFIED BY VALUES 'EB11FDB815CB0D37'; 
GRANT SELECT ON B.t1 TO C;

Option 3: Create and run a stored procedure to issue grants on B’s tables
This option avoids the need for privilege on DBMS_SYS_SQL or changing schema B’s password, but is only available if schema A has the “create any procedure” system privilege. Depending upon the situation this may be the safest option of the three presented here. E.g.:

CONNECT A/password
CREATE PROCEDURE B.temp_grant AS
BEGIN
  EXECUTE IMMEDIATE 'GRANT SELECT ON B.t1 TO C';
END;
/
EXECUTE B.temp_grant

Leave a Reply

Your email address will not be published. Required fields are marked *