Re: privilege question

Thanks for the reply. Will use plain text in future. Apologize for any inconvenience.
I did mention 8.1.7 in my question..

thanks again.

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:ttr67vk3ldh8be_at_corp.supernews.com...
>
> "S.A." <adenwalaNO_SPAM_at_att.com> wrote in message
> news:9rjtt5$87i3_at_kcweb01.netnews.att.com...
> I connect to my 8.1.7 instance as user "bugle" and create table named
> "bugle_table" in this schema.
>
> Then I connect as sys and issue following:
>
> GRANT SELECT ON BUGLE.BUGLE_TABLE TO <another_existing_user>;
>
> I get
> ORA-01031: insufficient privileges.
> Why is sys not able to do this? I tried the same with an existing id
having
> DBA role, and get the same error.
>
> thanks
>
> Could you please avoid posting in HTML?
> Many newsreaders can't cope with it, basically limiting your audience to
> those people using Outlook Express.
>
> This is by design of the SQL language.
> Only the owner of the object can grant privileges, unless he granted the
> privileges to someone else 'with admin option'
>
> As I rather prefer not using the 'with admin option' (when using it
security
> just becomes a myriad of privileges) I can tell you
> there is a very easy work-around to avoid this.
> As you access to sys, you can create a stored procedure in bugle's schema,
> something like 'bugle.do_ddl(sqlstr in varchar2)'
> The only thing that procedure needs to do is perform one dynamic sql
> statement, the grant you need.
> As you, like most others think you don't need to post a version and the
> answer is version specific, I will only tell you before 8i you need
dbms_sql
> to do this and in 8i and beyond execute immediate.
> As the procedure runs in the context of the owner this will have the
desired
> result.
>
> Hth,
>
> Sybrand Bakker
> Senior Oracle DBA
>
>
>
Received on Tue Oct 30 2001 - 01:35:56 CST