We help IT Professionals succeed at work.

how to grant privilega on stored procedures and a single table

gudii9
gudii9 asked
on
Medium Priority
923 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
Comment
Watch Question

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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

David VanZandtSr Software Engineer
CERTIFIED EXPERT
Commented:
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;"
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
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
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.



Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
"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.


Author

Commented:
thank you very much for your help. I appreciate it.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
glad we could help
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.