OraFAQ Forum: Server Administration » Unable to grant SELECT priv to ROLE

Home » RDBMS Server » Server Administration » Unable to grant SELECT priv to ROLE (Oracle8i Enterprise Edition Release 8.1.7.4.0 - IBM/AIX RISC System/6000:)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Unable to grant SELECT priv to ROLE [message #485151]

Mon, 06 December 2010 07:47

kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI

Senior Member

dear sir/Madam,
i have created one user SVC_LAMR
and created one ROLE - SVC_LAMR_ROLE.
i have attachd this role to abv user SVC_LAMR.

i need to grant SELECT privilege on some tables ( from some other schema TPAOWNER ) to this role.so that this user SVC_LAMR can access those tables lying in TPAOWNER schema.

for e.g.

SQL> show user
USER is "SYS"

SQL> grant SELECT on TPAOWNER.USER_APPLICATION to SVC_LAMR_ROLE;
grant SELECT on TPAOWNER.USER_APPLICATION to SVC_LAMR_ROLE
                         *
ERROR at line 1:
ORA-01031: insufficient privileges

please guide me resolving this issue.

But, if i connect this user: TPAOWNER, and give SELECT privilge directly to this role, it is accepting.

i.e.

SQL> conn tpaowner/*******
Connected.
SQL> grant select on USER_APPLICATION to SVC_LAMR_ROLE;

Grant succeeded.

SQL> grant SELECT on USERS to SVC_LAMR_ROLE;

Grant succeeded.

please guide me.

CM: moved the [code] tags so that they only include code

[Updated on: Mon, 06 December 2010 07:51] by Moderator

Report message to a moderator

Re: Unable to grant SELECT priv to ROLE [message #485152 is a reply to message #485151]

Mon, 06 December 2010 07:59

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Did you login as sysdba?

Edit:
Actually, does not matter.
It is the intended behavior for Oracle versions less than 9.2.
You need to explicitly grant the privilege as object owner
or
have granted the privilege to SYS/SYSTEM with admin option.

[Updated on: Mon, 06 December 2010 08:07]

Report message to a moderator

Re: Unable to grant SELECT priv to ROLE [message #485155 is a reply to message #485152]

Mon, 06 December 2010 08:41

kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI

Senior Member

Sir,
Thank you for your reply.

one more clarification:

we need to give these grants thro scripts...so, the corresponding table owner password to be specified in the script which is not possible i think so. let me check over here.

but, 2nd way, granting the same to sys/system with admin option, i tried, but getting below error:

SQL> conn tpaowner/*****
Connected.
SQL> grant select on user_application to SYS with admin option;
grant select on user_application to SYS with admin option
                                             *
ERROR at line 1:
ORA-00993: missing GRANT keyword


SQL> grant select on users to system with admin option;
grant select on users to system with admin option
                                     *
ERROR at line 1:
ORA-00993: missing GRANT keyword

could you pl adv on this 2nd one ?

Thank you. [/code]

[Updated on: Mon, 06 December 2010 12:10] by Moderator

Report message to a moderator