Home » RDBMS Server » Security » How to check grant privs own by user (Oracle 11R1, Unix)
How to check grant privs own by user [message #513577] Tue, 28 June 2011 05:30 Go to next message
Database admin
Messages: 312
Registered: September 2006
Location: india
Senior Member

Hi,

When i try to grant all privilege to a object i get error

SQL> GRANT ALL PRIVILEGES ON tab1 TO PUBLIC;
GRANT ALL PRIVILEGES ON tab1 TO PUBLIC
*
ERROR at line 1:
ORA-01929: no privileges to GRANT

how to check the user has privilege to use grant privilege or grant
all privileges to object and what privileges exist in ALL PRIVILEGES.

Your advice is valuable.


Thanks
Re: How to check grant privs own by user [message #513583 is a reply to message #513577] Tue, 28 June 2011 05:46 Go to previous messageGo to next message
cookiemonster
Messages: 10841
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you tried looking at all_tab_privs?
Re: How to check grant privs own by user [message #513587 is a reply to message #513583] Tue, 28 June 2011 06:00 Go to previous messageGo to next message
Database admin
Messages: 312
Registered: September 2006
Location: india
Senior Member

Cookie,

That is not my question.

My question here is what role/privilege a user must have to use 'grant all privs' on object ?

I hope my question is clear now.

Thanks
Re: How to check grant privs own by user [message #513588 is a reply to message #513587] Tue, 28 June 2011 06:02 Go to previous messageGo to next message
cookiemonster
Messages: 10841
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to have the privileges granted to you with grant option.
Re: How to check grant privs own by user [message #513597 is a reply to message #513587] Tue, 28 June 2011 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 58489
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I hope my question is clear now.

Why don't you read the documentation, it is clearly and explicitly explain in it.
Same link as usual.

Regards
Michel
Re: How to check grant privs own by user [message #514183 is a reply to message #513577] Sat, 02 July 2011 05:50 Go to previous message
x-oracle
Messages: 326
Registered: April 2011
Location: gujarat
Senior Member
balaji if i think correct you want to give all priv on tab1 table to public

the correct syntax is here


SQL> grant all on tab1 to public;

Grant succeeded.

SQL>

and balaji if tab1 is your table so you have all privilage you can grant and if tab1 is not in your schema so a user give you a priv with grant option so you have rights to give priv on this table

look this example here sh1 table in scott user so scott has full priv on his sh1 table so he give object to hr user with grant option after that hr has full priv on scott sh1 table to give priv on sh1 object to any user look below this exaple

=================================================================

SQL> conn scott/tiger
Connected.
SQL>
SQL>
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
RJ1 TABLE
TEST TABLE
SH1 TABLE
DP TABLE
FGA_TEST TABLE

9 rows selected.

SQL> grant all on sh1 to hr with grant option;

Grant succeeded.

SQL> conn hr/hr
Connected.
SQL>
SQL>
SQL> grant all on scott.sh1 to public;

Grant succeeded.

SQL>

[Updated on: Sat, 02 July 2011 06:05]

Report message to a moderator

Previous Topic: delete oracle wallet
Next Topic: View sysdba tables
Goto Forum:
  


Current Time: Tue Jul 22 16:57:04 CDT 2014

Total time taken to generate the page: 0.17301 seconds