Re: How to Handle new users in this senerio...

From: Julio Negueruela <julio.negueruela_at_si.unirioja.es>
Date: Thu, 20 May 1999 13:47:35 +0200
Message-ID: <3743F657.A5EFA662@si.unirioja.es>

tim_mcconechy_at_my-dejanews.com escribió:
> =

> Hi...
> I have the following scenario...
> A user sysadm owns everything in a schema.
> tables,Sequences,triggers,procedures...
> =

> A new user is added and needs access to all of sysadm's objects.
> =

> Code says "select * from users" for example.
> Not:
> "select * from sysadm.users"
> =

> My thought was..
> create public synonyms on all objects;
> grant select,update,delete on all objects to public...
> =

> But isn't there an easier way???

Hi, Tim, why don't you create a role and grant these privileges to him? For example, iy you've got the role all_privilege You can do as follows: create the role allaccess
Make a script wich gives access on different objects to the role. It should be like this:
set pagesize 0
set... (set everithing you so in the spool only columns returned will appear)
REM grant privileges on tables
spool grant_tables.sql
select 'grant select, udpate, delete, insert on '||table_name||' to allaccess;'
 from user_tables
/
spool off
REM grant privileges on views
spool grant_views.sql
select 'grant select on '||object_name||' to allaccess;'  from user_objects
where object_type = 'VIEW'
/
REM grant privileges on packages, prodecures, functions spool grant_pack.sql
select 'grant execute on'||object_name||' to allaccess;'  from user_objects
where object_type in ('PACKAGE','FUNCTION','PROCEDURE') /
spool off
and so on sequences, etc.
At then end, you can create public synonyms for every object. spool create_public_synonym.sql
select 'create public synonym '||object_name||' for '||object_name||';'  from user_objects
/
spool off
Then you cane execute the above scripts:

@grant_tables.sql
@grant_views.sql
@grant_pack.sql

...
@create_public_synonym

and finally, grant the role created to the specified user. grant allaccess to newuser;

All these steps must be executed from the owner account.

Hope this help.
-- =

Julio Negueruela
DBA Servicio Informático

Universidad de La Rioja      -      Spain
Telf: 941-299179     Fax: 941- 299180

mailto:julio.negueruela_at_si.unirioja.es Received on Thu May 20 1999 - 06:47:35 CDT