Oracle 11g statements to create new user and grant privileges?

Question

I want to create a user/schema in oracle 11g and grant all privileges to the user. How can I do this with a simple script. I looked at the following links but I am not sure which one to use or if these statements are the best way.

http://ss64.com/ora/grant.html

Can you suggest how I may do this in the simplest possible way and securely ?

Answer 1

To create a new user you use the "create user" command. So a typical create user command would be :

create user test identified by test default tablespace mytbsp.

Of course you need to replace the values for the user, password and tablespace with different values. However I'd recommend that you have a look at Oracle's documentation http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm.

The next step is to grant the user the corresponding rights. To give a user all the rights is a very bad approach as you would also give him dba privileges. What you instead is to give him connect privileges and the permissions to his default tablespace. Also it is better to use roles instead of granting the rights directly. So if you have to grant the rights again you only need to grant the role. First step is to create the role:

GRANT CREATE session, CREATE table, CREATE view, 
      CREATE procedure,CREATE synonym,
      ALTER table, ALTER view, ALTER procedure,ALTER synonym,
      DROP table, DROP view, DROP procedure,DROP synonym
      TO MyRole;

This statement is not complete you might require additional rights (index maintenance for instance), but have a look at the online oracle documentation.

After that you grant the role to the newly created user.

GRANT myrole to test;

Answer 2

Create the user:

create user user_name identified by password ;

Grant the privileges:

grant all privilege to user_name;

If you want to view the number of privileges:

select * from system_privilege_map where neme like '%PRIV%';

If you want to view privileges assigned to the users:

select count (*) , grantee
from dba_sys_privs 
where grantee in ('user1','user2')
group by grantee ;