Re: privilege question

"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 Mon Oct 29 2001 - 11:47:46 CST