Creating oracle user and giving him grants using shell script

Shell Programming and Scripting
1

Hi ,
I want to write a shell script that can create oracle database user and grants permission to this user.

Thanks & Regards,
Deepak

2 
$ echo "create user user1 identified by password1;
grant privilege to user1;
exit
" | sqlplus user2/password2@SID

tyler_durden

3

Hi durden_tyler,
Thank you for replying, but I am sorry to say that I am not able to understand and execute what you have posted. Could you please explain me in detail.

I want to execute following command using shell script

create user <Username> identified by <Password>;

grant create session, create table, create procedure,create sequence, create view, create trigger,create synonym, create materialized view,
query rewrite, create any directory, create type,dba, aq_administrator_role to <Username>;

4

Hi.

It prints some SQL to standard output. This output is read by SQL*Plus as input, and works just fine.

Another option:

sqlplus / as sysdba << !
grant create session, create table, create procedure,
      create sequence, create view, create trigger,
      create synonym, create materialized view, query rewrite,
      create any directory, create type,dba, aq_administrator_role
to username identified by password;
!


$ ./CreateUser
...
Grant succeeded.

$ sqlplus username/password

SQL*Plus: Release 10.2.0.3.0 - Production on Sat Oct 10 14:41:33 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options

SQL> show user
USER is "USERNAME"

---
Having read your post again, and appreciating that your difficulty might be more with the shell itself, and not with the database side:

Copy and paste this (or durden_tyler's) script into a file:

sqlplus / as sysdba << !
grant create session, create table, create procedure,
      create sequence, create view, create trigger,
      create synonym, create materialized view, query rewrite,
      create any directory, create type,dba, aq_administrator_role
to username identified by password;
!

modifying it to suit your requirements.

Assume you called the file CreateUser, run the command to make the script executable for you:

chmod u+x CreateUser

And then run it:

./CreateUser
5

Thanks scottn, for such a nice explanation.... I am able to create user and grants as well....