Re: Roles & Privileges

Thomas Kyte wrote:
>
> A copy of this was sent to "Mark Wallace"
> <mwallace*NOSPAM*@*NOSPAM*carr-sheppards.co.uk>
> (if that email address didn't require changing)
> On Tue, 22 May 2001 18:11:03 +0100, you wrote:
>
> >I am at the start of setting up a new system. What I want to do is have all
> >the application tables, indexes, packages etc, etc... in a central
> >application schema. For example called 'Applic'
> >This schema has its own tablespace (ApplicData_ts) and temporary tablespace
> >(ApplicTemp_ts).
> >
> >I have now created a couple of users (usera, userb etc...) also using the
> >same tablespaces.
> >The users must have no rights to create any of there own database objects.
> >But must have rights to select, update, execute etc, etc... from the
> >applications tables.
> >
> >How do setup a user to automatically have access to all 'Applic' schemas
> >object without having to specify each object individualy as in,
> >grant select on tablea to usera
> >grant select on tableb to usera
> >grant update on tablea to usera
> >etc .etc .etc..... for all DB Objects that the application uses.
> >
> >Do I grant the privileges to a role and then the role to the users. But
> >still have to grant each privilege individualy to the role?
> >Is there a better way of doing this.
> >
> >Any suggestions or comments appreciated.
> >
> >Thanks
> >Mark
> >
>
> see
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:646423863863
> for a method that uses a DDL event trigger to schedule a job to grant select on
> newly created objects in a schema. So, if you want USERA to have select on any
> table in some schema -- even newly created ones -- this supplies an approach.
>

Tom,

That does it. 'SELECT ANY TABLE' is being revoked at my sites. I no longer have the 'too many tables are being added too frequently' excuse.

The 3 chapter preview edition of your book 'Professional Oracle Programming' absolutley rocked - I can't wait for the full text. (forced my copy from IOUG at managers just for the first chapter - very high conveyed knowledge density).
I get the feeling that I could read a dozen (or more) books - or read your (completed) text - and walk away with the same amount of useful material (with the exception of Practical Oracle 8i - that has been most useful). Its just plain - compelling.

This seems like a book that you *had* to write - just to vent a portion of the knowledge in your skull.

tell the editor to just 'ship it'.

Paul Received on Wed May 23 2001 - 00:43:59 CDT