Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: (newbie) How to get around table name qualification?

Re: (newbie) How to get around table name qualification?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 20 Feb 2007 06:16:30 -0800
Message-ID: <1171980989.987909.195150@k78g2000cwa.googlegroups.com>


On Feb 20, 3:12 am, "Jeroen ter Hofstede" <jeroen.ter.hofst..._at_wanadoo.nl> wrote:
> On 19 feb, 16:28, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
>
> > The use of Public synonyms would "map" the objects for all usernames
> > that connect to the database; however, only those users who have been
> > granted object privileges will be able to access the tables.
>
> > create table table_a
> > create public synonym table_a
> > grant select on table_a to rolename
> > grant rolename to userA, userB, userC.
>
> > would allow userA, userB, userC but not userD to select rows from
> > creator.table_a using the name table_a
>
> Okay, I think that would be a possible solution. The code (that knows
> all the tables) could perform the
> create table table_a
> create public synonym table_a
> grant select on table_a to rolename
> and we could have the DBA perform the
> grant rolename to userA, userB, userC.
>
> In this way, the code would not have to know the user names. This
> assumes that it can use a predefined role with standard access rights
> (select, update, insert, delete); such a role does not seem to exist.
>
> It starts to look like I'll have to write a batch of custom code to
> address Oracle, and have the user provide a number of extra settings.
> Well, so be it.
>
> > For online documentation see http://tahiti.oracle.com
>
> Unfortunately, one needs at least an idea for what one is looking to
> be able to find anything in that enormous pile - I lack too much basic
> Oracle knowledge for that, and I don't have the time to just start
> reading from the beginning.
>
> Anyway, thanks for your help.

Scan the table of contents in the Concepts and DBA Administration Guides.

Much of what a DBA needs to know is contained in these two manuals.

Then the SQL manual can provide the full options and information on the use of DDL to create and manage objects.

The Oracle version# Reference manual provides a place to look up all database parameters, rdbms dictionary view definitions, and basic info on the dynamic performance views, v$.

If you are more of a developer then see the Application Developers Guide - Fundamentals. This manual covers the topics in Concepts and DBA Administration of most interest to a developer.

The most flexible way to manage security is via roles. CREATE ROLE rolename [IDENTIFIED BY password]. Just have objects for specific applications or even better job functions issue the appropriate grant to the role. Then the role can be granted to users OR set by an application at run time.

HTH -- Mark D Powell -- Received on Tue Feb 20 2007 - 08:16:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US