Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to grant privilega on stored procedures and a single table

Posted on 2009-02-10
8
Medium Priority
?
905 Views
Last Modified: 2013-12-18
I have a schema/user whose name is say XYZ needs to access  another user (say ABC)stored procedure ,single table alone. How i can i grant those privileges on user/schema xyz. Any ideas, syntax,code,links, resources highly appreciated. Thanks in advance
0
Comment
Question by:gudii9
8 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1200 total points
ID: 23606296
log in as ABC or as a user with "grant any object privilege" authority

grant execute on abc.your_procedure to xyz.

grant select on abc.your_table to xyz
0
 
LVL 7

Author Comment

by:gudii9
ID: 23606602
you mean to say i need to login to user ABC and execute the command
"grant any object privilege"when i execute it is showing any word and saying
ORA-00990:missing or invalid privilege
From same ABC user
I was able to execute commands:
grant execute on abc.procedure_name to xyz
but i was not able to execute:
grant select on abc.table_name to xyz (saying table or view does not exist)
How shall i grant privelege to table also

0
 
LVL 23

Assisted Solution

by:David
David earned 400 total points
ID: 23608011
Assuming that user or role xyz exists, and that your table or view does exist (select table_name from user_tables), and as user ABC, try without the owner prefix: "grant select on table_x to xyz;"
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1200 total points
ID: 23612147
are you sure you don't have a misspelled table name?

grant select on abc.table_name to xyz


that is correct syntax.

is your table name mixed or lower case? If so, you'll have to put it in double quotes

grant select on abc."table_name" to xyz
0
 
LVL 14

Assisted Solution

by:sathyagiri
sathyagiri earned 400 total points
ID: 23614280
Login as user ABC, then execute the following

GRANT SELECT ON <TABLE_NAME> TO XYZ;

GRANT EXECUTE ON <STORED_PROC NAME> TO XYZ;

Then login as user XYZ and
select * from abc.<TABLE_NAME>;
execute ABC.<SPROC_NAME>;

If you do not want to suffix the owner name , then create a synonym for the table and stored proc

CREATE SYNONYM <TABLE_NAME> FOR ABC.<TABLE_NAME>;
CREATE SYNONYM <SPROC_NAME> FOR ABC.<SPROC_NAME>;

Now you can directly access the table and sproc with out prefixing the owner name.



0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1200 total points
ID: 23614456
"you mean to say i need to login to user ABC and execute the command
"grant any object privilege"when i execute it is showing any word and saying
ORA-00990:missing or invalid privilege
"

what I meant was ABC can grant access to its own objects

Other users can also grant access to ABC's objects provided those users have authority to "grant any object privilege" that entire phrase is the name of the privilege.

If you wanted to be able to do that you would do

grant grant any object privilege to some_user

most users do not have authority to grant that privilege.

however, if you're logging in as ABC, you don't need that authority because it's implicit in being the object owner.


0
 
LVL 7

Author Closing Comment

by:gudii9
ID: 31545342
thank you very much for your help. I appreciate it.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23809864
glad we could help
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses
Course of the Month20 days, 15 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question