| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Be carefull with public synonyms
Sometime ago I did what Julio suggests but ran into problems. Export and
import didnt run? I had no idea why, and it took a lot of work to discover
that the user had a table named system. So now I had a public synonym system
for <user>.system!! Not a good thing:)
I ended up making private synonyms for each new user instead.
Actually I made a procedure CreateUser that creates a new user with
everything needed, runnning through all objects, making grants( to a role )
and priv synonyms.
The beauty of the procedure is that the schemaowner, with all the powerfull
privs, doesnt have to run it. He just needs to grant execute on it to
somebody.
Peter
ptl_at_edbgruppen.dk
Julio Negueruela skrev i meddelelsen <3743F657.A5EFA662_at_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
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
 |
 |