Home » SQL & PL/SQL » SQL & PL/SQL » Help with Grant statements...
Help with Grant statements... [message #263652] Thu, 30 August 2007 11:20 Go to next message
brig
Messages: 14
Registered: July 2007
Junior Member
I am trying to read a table and create users. The script works fine till PROFILE MONITORING_PROFILE.
I then added the Grant statements and I am doing something wrong with single quotes or double quotes. I can't see what I have done wrong. Any help is so appreciated. Thank you.

create or replace
PROCEDURE MakeStudentsWithGrants
is

--Declare, don't select

CURSOR STDCursor IS
SELECT IROWID, STDACCOUNT, STDPASSWORD
FROM SYS.STDTABLE;

iLoopCount int:=1;
iNextRowId int:=1;
iCurrentRowId int:=0;
iRowCount int :=0;
iRowNo Number:=0;
vchACCOUNName varchar2(7;
vchPasswordNmbr varchar2(10;
iRetrnNum int;

BEGIN
OPEN STDCursor;
LOOP
FETCH STDCursor INTO iRowNo,vchACCOUNName,vchPasswordNmbr;

EXIT WHEN STDCursor%NOTFOUND;
IF iRowNo = iNextRowId
THEN
iNextRowId :=iNextRowID +1;

EXECUTE IMMEDIATE

'CREATE USER
'||vchACCOUNName||'
IDENTIFIED BY
'||vchPasswordNmbr||'
DEFAULT TABLESPACE STUDENTTABLESPACE
QUOTA 10M ON STUDENTTABLESPACE
PROFILE MONITORING_PROFILE
GRANT INSERT, SELECT, UPDATE, ALTER ON "HR"."COUNTRIES" TO '||vchACCOUNName||'
GRANT INSERT, SELECT, UPDATE, ALTER ON "HR"."DEPARTMENTS" TO '||vchACCOUNName||'
GRANT INSERT, SELECT, UPDATE, ALTER ON "HR"."EMPLOYEES" TO '||vchACCOUNName||'
GRANT INSERT, SELECT, UPDATE, ALTER ON "HR"."JOBS" TO '||vchACCOUNName||'
GRANT INSERT, SELECT, UPDATE, ALTER ON "HR"."JOB_HISTORY" TO '||vchACCOUNName||'
GRANT INSERT, SELECT, UPDATE, ALTER ON "HR"."LOCATIONS" TO '||vchACCOUNName||'
GRANT INSERT, SELECT, UPDATE, ALTER ON "HR"."REGIONS" TO '||vchACCOUNName||'
GRANT INSEFRT, SELECT, UPDATE, ALTER ON "SCOTT"."BONUS" TO '||vchACCOUNName||'
GRANT INSERT, SELECT, UPDATE, ALTER ON "SCOTT"."DEPT" TO '||vchACCOUNName||'
GRANT INSERT, SELECT, UPDATE, ALTER ON "SCOTT"."EMP" TO '||vchACCOUNName||'
GRANT INSERT, SELECT, UPDATE, ALTER ON "SCOTT"."SALGRADE" TO '||vchACCOUNName||'
GRANT "STD_ROLE" TO '||vchACCOUNName||'
ALTER USER '||vchACCOUNName||' DEFAULT ROLE ALL');


END IF;
END LOOP;--in FOR
CLOSE STDCursor;

END;--procedure



Re: Help with Grant statements... [message #263655 is a reply to message #263652] Thu, 30 August 2007 11:47 Go to previous messageGo to next message
jack1
Messages: 11
Registered: August 2007
Junior Member
read the code format . i read, so read and paste code.

thansk
JACK
Re: Help with Grant statements... [message #263661 is a reply to message #263652] Thu, 30 August 2007 12:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Help with Grant statements... [message #263665 is a reply to message #263652] Thu, 30 August 2007 12:46 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
brig wrote on Thu, 30 August 2007 12:20
I can't see what I have done wrong. Any help is so appreciated.

vchACCOUNName varchar2(7;
vchPasswordNmbr varchar2(10;



For one thing, those lines are invalid.

Quote:

FROM SYS.STDTABLE;



Very bad to put tables in SYS.


I am not quite sure what Jack1 is saying.

[Updated on: Thu, 30 August 2007 12:53]

Report message to a moderator

Re: Help with Grant statements... [message #263667 is a reply to message #263652] Thu, 30 August 2007 12:50 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
execute each grant statement with separate command
and use ; after each sql statement
Re: Help with Grant statements... [message #263674 is a reply to message #263667] Thu, 30 August 2007 13:10 Go to previous messageGo to next message
brig
Messages: 14
Registered: July 2007
Junior Member
THANK YOU, that did it. Listing each grant separately.
(I do try to code editor and paste what it gives me. I will try harder sorry )
Re: Help with Grant statements... [message #263683 is a reply to message #263674] Thu, 30 August 2007 13:49 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Read a Guide, go to Test Forum and test your skills there; it won't take long, but will enormously improve quality of your posts.
Previous Topic: Creating a tablespace
Next Topic: Query Required
Goto Forum:
  


Current Time: Thu Dec 08 06:21:36 CST 2016

Total time taken to generate the page: 0.08177 seconds