Re: Give user index grants Oracle 8/9i

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 18 Nov 2006 05:09:48 -0800
Message-ID: <1163855388.532236.50040@h54g2000cwb.googlegroups.com>

Sean wrote:
> After digging around some, it appears ADO recognizes the primary key on
> the table for non-owner users if you address the table with the
> <OWNER>.<TABLE> specification in your query. If you use the synonym
> specification of just <SYNONYM> for the table, the primary key
> information isn't recognized. Why is this?

I am familiar with ADO. The primary keys still apply, even though it may appear that those primary keys are not visible (see below for a SQL statement). As mentioned in my previous post, the DBA_n views are accessible by the database administrators, the ALL_n views are accessible by all users and show those objects for which the user may access, and the USER_n views show the objects owned by a user.

The database administrators may use a SQL statement like this to view the indexes and the tables and columns which are indexed: SELECT

  DI.OWNER,
  DI.INDEX_NAME,
  DI.INDEX_TYPE,
  DI.TABLE_OWNER,
  DI.TABLE_NAME,
  DIC.COLUMN_NAME,

  DIC.COLUMN_POSITION,
  DIC.DESCEND,

  DI.TABLE_TYPE,
  DI.UNIQUENESS,
  DI.COMPRESSION,
  DI.PREFIX_LENGTH,
  DI.TABLESPACE_NAME

FROM
  DBA_INDEXES DI,
  DBA_IND_COLUMNS DIC
WHERE
  DI.OWNER=DIC.INDEX_OWNER
  AND DI.INDEX_NAME=DIC.INDEX_NAME
ORDER BY

  DI.OWNER,
  DI.TABLE_OWNER,
  DI.TABLE_NAME,

  DIC.TABLE_NAME,
  DIC.COLUMN_POSITION; Change DBA_ to ALL_ for non-DBA users, like this: SELECT

  DI.OWNER,
  DI.INDEX_NAME,
  DI.INDEX_TYPE,
  DI.TABLE_OWNER,
  DI.TABLE_NAME,
  DIC.COLUMN_NAME,

  DIC.COLUMN_POSITION,
  DIC.DESCEND,

  DI.TABLE_TYPE,
  DI.UNIQUENESS,
  DI.COMPRESSION,
  DI.PREFIX_LENGTH,
  DI.TABLESPACE_NAME

FROM
  ALL_INDEXES DI,
  ALL_IND_COLUMNS DIC
WHERE
  DI.OWNER=DIC.INDEX_OWNER
  AND DI.INDEX_NAME=DIC.INDEX_NAME
ORDER BY

  DI.OWNER,
  DI.TABLE_OWNER,
  DI.TABLE_NAME,

  DIC.TABLE_NAME,
  DIC.COLUMN_POSITION; Looking at constraints (change DBA_ to ALL_ for non-DBA users): SELECT

  DC.OWNER,
  DC.CONSTRAINT_NAME,
  DC.CONSTRAINT_TYPE,
  DC.TABLE_NAME,

  DCC.COLUMN_NAME,
  DCC.POSITION,

  DC.R_OWNER,
  DC.R_CONSTRAINT_NAME,
  DC.DELETE_RULE,
  DC.STATUS,
  DC.DEFERRABLE,
  DC.DEFERRED,
  DC.VALIDATED,
  DC.LAST_CHANGE

FROM
  DBA_CONSTRAINTS DC,
  DBA_CONS_COLUMNS DCC
WHERE
  DC.OWNER=DCC.OWNER
  AND DC.TABLE_NAME=DCC.TABLE_NAME
  AND DC.CONSTRAINT_NAME=DCC.CONSTRAINT_NAME ORDER BY

  DC.OWNER,
  DC.TABLE_NAME,
  DC.CONSTRAINT_NAME,

  DCC.POSITION; In your code where you work with the results of the SQL statement, you will not use the <OWNER>.<TABLE> or <OWNER>.<TABLE>.<COLUMN> syntax to refer to a column, only the column name is referenced. With the above:

snpData("DC.CONSTRAINT_NAME")  would generate an error, while
snpData("CONSTRAINT_NAME")    and
snpData(1)   and
snpData("CONSTRAINT_NAME").Value    and
snpData(1).Value   all return the column value without error

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Nov 18 2006 - 07:09:48 CST