Grant on emp table not working.......

[sbhujbal]

Hello,
How I can grant object privileges to users......????

Following are my activities....

SVRMGR>connect internal;
connected....
SVRMGR>grant select on scott.emp to sunil.
(here sunil is the name of user....)
*
ORA-01031 : Insufficient privileges

What is wrong. Why I am not able to give select privileges on scott.emp table to user sunil. What is this insufficient privilege to internal user......


Sunil

[Halo]

Retry it. If you are logging in as SYS, there should be no reason for this error. Retry by logging in as SYS or SYSTEM.

[kiruba]

Hi

You login as scott and try to grant the object priviledge. This command won't fail.

[sbhujbal]

If I logged in as scott, I am able to give object privileges of scott user to another user 'sunil' but I am not able to grant same privilege by connecting as sys,system,internal or as sysdba....what is wrong....how to debug....
Sunil

[vinodkmg]

Hi sbhujbal,
U see since emp table is owned by scott, only scott can grant object privilages to other users.Even to Sys or System to Select on the object of scott.Not even sys,system,internal can do this,thought they have DBA Privilages.

Cheers,
Vinod Kumar.

[sbhujbal]

Vinod,
But I connected as connect dba/password as sysdba in server manager and executed same command
grant select on scott.emp to sunil ......and it says insufficient privileges.

DBA should have all privileges to grant one users schema object to another user..........why owner has to explicitly grant that permission to another user.....
Can you clarify how it works.....because I am thinking that DBA can grant any schema.object permission to another user ...because he is having grant any privilege permission...

Sunil

[amir_magdy]

There is two types of privs:
object privs and system privs.
user sys can grant any system privs,even with the admin option, which means that the grantee can again grant it to other users.

regarding the object privs the user sys has all privs. on them but without the ability to grant privs to other users,
the owner user should grant these privs, to other users or to the sys user with the admin option, so he can grant it to other users.

[sbhujbal]

Thanks.
I understood that "sys" cannot grant object privileges of one user to another. Only that user can grant them to another user or he has to grant to sys with admin option.

Sunil