How to create a user in Oracle 11g and grant permissions

oracle oracle11g access-control
Andy5 picture Andy5 · Feb 25, 2012 · Viewed 544.2k times · Source

Can someone advise me on how to create a user in Oracle 11g and only grant that user the ability only to execute one particular stored procedure and the tables in that procedure.

I am not really sure how to do this!

Answer

cagcowboy picture cagcowboy · Feb 25, 2012

Connect as SYSTEM.

CREATE USER username IDENTIFIED BY apassword;

GRANT CONNECT TO username;

GRANT EXECUTE on schema.procedure TO username;

You may also need to:

GRANT SELECT [, INSERT] [, UPDATE] [, DELETE] on schema.table TO username;

to whichever tables the procedure uses.

Related questions

ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

We have an application running locally where we're experiencing the following error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor I've tested the connection using TNSPing which resolved correctly and I tried SQLPlus to try …

Read More
How do I do top 1 in Oracle?

How do I do the following? select top 1 Fname from MyTbl In Oracle 11g?

Read More
ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client

I'm trying to connect to a schema on 11g (v11.2.0.1.0) from a PC with 9i (v9.2.0.1) client. It seems to connect fine to some schemas, but not this one - it comes back with a ORA-01017 Invalid Username/Password error …

Read More