CODEWITHSUNDEEP
oracle-databaseplsqldatabase-administration
K.I
K.I

Reputation: 588

Oracle pl sql new user gets PROCEDURE DOESN'T EXIST

I have Oracle based application that has a database and apache with modplsql set as application server, I am fairly new to Oracle so forgive me if this is not a very smart question, but its a question I could not find an answer to.

The problem is I need to add a couple of new users to this oracle application and can't figure out how to do so, I tried both: creating them using a console and using oracle sql developer UI tool, in both cases I grant all the roles and privileges that current users have, yet when I connect to my application with new credentials I get PROCEDURE DOESN'T EXIST error. I know the problem is with roles or privileges, but can't figure out what it is.

Can someone with more experience guide me in correct direction?

Thanks in advance!

Code:

1) create user myuser identified by pass2;
2) ALTER USER "myuser" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK ;
3) GRANT "DBA","CONNECT","our_USER","RESOURCE" TO "myuser";
4) GRANT create database link  TO "myuser";
   GRANT create materialized view  TO "myuser";
   GRANT create procedure  TO "myuser";
   GRANT create public synonym  TO "myuser";
   GRANT create role  TO "myuser";
   GRANT create sequence  TO "myuser";
   GRANT create synonym  TO "myuser";
   GRANT create table  TO "myuser";
   GRANT create trigger  TO "myuser";
   GRANT create type  TO "myuser";
   GRANT create view  TO "myuser";
   GRANT create tablespace  TO "myuser";
   GRANT create session  TO "myuser";

Upvotes: 1

Views: 1762

Answers (3)

K.I
K.I

Reputation: 588

I know its been awhile, but I noticed a few people looked at this post, so I decided to post what the problem was hoping that it may help someone. Well in our case it was the problem with schemas, therefore a trigger was required liko so:

create or replace TRIGGER my_user.swithtomyschema
AFTER LOGON ON my_user.SCHEMA 
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=my_schema';
END;

I know it now so it seems simple, but back then I was new to the system and it took me days untill I figured out what was wrong with it. I hope nobody else is in the same situation, but if you are I hope you will come accross this answer and it will point you in the right dirrection. Best of luck!

And thanks to all who tried to help!

Upvotes: 0

Muwaffaq Samakeh
Muwaffaq Samakeh

Reputation: 29

First, when you create a user within the Oracle Better to create fully and give space for storage Example:

Create user "Myuser" identified by pass2
quota 15M on users Account unlock;

Than to created, and then do a modification to the structure ِAnd the user you are working on to create new users must have full full Permissions with the possibility of granting to the other users theen you give them the Permissions like this example:

GRANT DBA,CONNECT,RESOURCE TO myuser;
GRANT create database link  TO myuser;
GRANT create materialized view  TO myuser;
GRANT create procedure  TO myuser;
GRANT create public synonym  TO myuser;
GRANT create role  TO myuser;
GRANT create sequence  TO myuser;
GRANT create synonym  TO myuser;
GRANT create table  TO myuser;
GRANT create trigger  TO myuser;
GRANT create type  TO myuser;
GRANT create view  TO myuser;
GRANT create tablespace  TO myuser;
GRANT create session  TO myuser;

etc...

Upvotes: 0

Muwaffaq Samakeh
Muwaffaq Samakeh

Reputation: 29

to create new user in oracle you can do it by this statement : Create user (User_name) identified by (Pass_word) Account (lock/unlock) Qouta (user_space) on users; Exmaple:

create user Muwaffaq identified by PassWo
Account unlock quota 15m on users;

After you create the user you most give him privileges for Create session ,Create table , Create trigger ......etc Exmaple:

grant Create session to Muwaffaq;
grant Create user to Muwaffaq;
grant Create table to Muwaffaq;
grant Create Trigger to Muwaffaq;
grant Alter User to Muwaffaq;
grant Create Function to Muwaffaq;
grant Create sequnce to Muwaffaq;
grant Create procedure to Muwaffaq;

.....etc

the most important privileges that Allow you to connecting to your user is Create Session you must give it to each user have been created.

Upvotes: 2

Related Questions