Re: Admin option to Sys Account on all objects - How?

badri wrote:
>
> Hi:
>
> I am using sys account to grant priveleges from one
> schema to another. ORACLE gives me the error "Insufficient
> Privileges". I have to login as the owner of the object to
> grant privileges to another schema/role. Does the sys account
> not come with defult admin option on all objects? How would I
> grant admin option to sys without doing it on an object-by-object
> basis from each account?
>
> thanks a lot,
>
> -BadriAs I know you need to connect as the user to do object grants. You could
always generate simple scripts like this.

define grantee_name = 'Somebody'
set pagesize 0
spool tmp.sql
select 'GRANT .... on '||object_name || ' to &grantee_name;'   from user_objects
 where object_type not in ('SYNONYM', 'INDEX') /* Grants not required */ /
spool off
start tmp

You could do object_type based grants using DECODE on object_type eg. DECODE(object_type, 'SEQUENCE', 'SELECT', 'TABLE', 'SELECT, INSERT', ...)

Hope this helps.

Bala

--
Balakrishna Avula						28 Ashby 
Court
Alpha-Tek Associates Ltd.					Hemel 
Hempstead
Technical Consultant(on contract to Oracle UK)			HERTS HP2 
7QL
100610,52 on Compuserve						UK

http://ourworld.compuserve.com/homepages/BAvula/homepage.htm on WWW