I envision the following:
I want to create a number of users in a script. Due to the plethora of grants to provide I'd like to split each indivisual user into a separate .sql file, like this:
|-- User1.sql
|-- User2.sql
|-- User3.sql
|-- User4.sql
All these files have in common that I first drop the corresponding user account if it already exists and then recreate that user account and add individual rights to it.
So, I'd like to create a procedure that's supposed to drop and create a user. This procedure is supposed to be stored in some kind of "main" script and called by each of the User scripts.
The Main SQL file is supposed to look like this:
DECLARE
PROCEDURE CREATE_USER
( USERNAME IN VARCHAR2(200)
, TABLESPACE IN VARCHAR2(200)
)
BEGIN
DROP USER ...
CREATE USER ...
END CREATE_USER;
@User1.sql
@User2.sql
@User3.sql
@User4.sql
Each of the User files is then supposed call the procedure and add grants to the user:
DEFINE USERNAME = 'User1'
CREATE_USER(&USERNAME);
GRANT ... TO &USERNAME;
GRANT ... TO &USERNAME;
The folder structure would look something like this then:
|-- Main.sql
|-- User1.sql
|-- User2.sql
|-- User3.sql
|-- User4.sql
Can this be done?