Grant CREATE SESSION to mass users

[richardana]

Hi All
I have a table with 500 users. I need to grant them CREATE SESSION privilege for them to connect to the database. I know there is a Dynamic SQL statement that can grant this privilege in one shot but I'am not very conversant with Dynamic SQL. Can someone help?
Select (grant create session.....
Thanks
Richard

[pando]

spool grantcreate.sql
select 'grant create session to '||username||';'
from dba_users
where username not in ('SYS, 'SYSTEM', 'DBSNMP', 'CTXSYS'); etc etc there are more users you have to exclude
spool off
@grantcreate.sql

[richardana]

Hi pandito,
I tried your script but I was given error message saying that 'USERNAME' is invalid column name.

BBC_EMPLOYEE is a table that I have created under my schema.
Here is a snapshot of the BBC_EMPLOYEE table.

The actual table has 500 users. I wish I have only a douzen users. I can do it with runtime variable or grant privilege on per user basis.

SEQ PWD LAST_NAME FIRST_NAME

BBDD ABCF SMITH JOHN
DDPP MMTT TONY LOUIS
CCDD TTRR LOVETT AMY
RTTP PPTT DOH SAMUEL
4 rows selected

Wanted to grant CREATE SESSION to them so they can connect to the database.
Please tell me what I'm doing wrong.
Thanks for your prompt response.

[santoshym]

Hi

You can modify pando's script a bit to do this

spool grantcreate.sql
select 'grant create session to '||firstname||';'
from bbc_employee;
spool off
@grantcreate.sql

You will need to put the column of your choce for firstname becuase I dont know what column you are using as userid's I have give firstname hoping that is the username

Regards

[richardana]

Hi Santosh,
Thank you and every one who responded.
I followed your exemple and it worked.
Thank you much.
PS.
By the way, from all the forums that I am part of, DBASupport is the one that responds in a very timely fashion. I mean less than hours. not to say hour.
AGAIN THANKS TO ALL
Richard

[richardana]

Hi all'
I have created mass users and loaded it in a table called BBC_EMPLOYEE, all under my schema.

When I query the table
SQL> select * from BBC_EMPLOYEE;
its shows me the list of all users and the count is also right.

When I query Select * from DBA_USERS; NONE of those mass users created show up. Why? I just gave create session privilege to users and NONE of the users was able to connect.

Can someone please advise?
Thanks
Richard

[pando]

how did you create the users?
what is the error message?
DBA_USERS shows the users in your database ok, it is always there as long as the user is created properly, event they dont have CREATE SESSION privs, in other words I can have users with login password but with zero provileges

[richardana]

Hi Pandito,
When I query the DBA_USERS there is no sign of the users I created.
I created those users with their individual password and they are in the table created
just for that purpose. After granting CREATE SESSION to all, I intentionally attempted
to log on to the database using one username and password and I received INVALID USERNAME/PASSWORD.
When i query the DBA_USERS ...no sign of those users.

[mageri]

Hi richardana

How did you create the users?
Have You really done it with:
CREATE USER xxx IDENTIFIED BY yyy DEFAULT TABLESPACE zzz TEMORARY TABLESPACE vvv QUOTA ... etc.

[richardana]

Hi all,
Here is the script I used to create all the 500 users.
SELECT ('CREATE USER '||SEQ||' IDENTIFIED BY '||PWD ||
' DEFAULT TABLESPACE '||' USERS '||
'TEMPORARY TABLESPACE' ||' TEMP '||
'/')
FROM BBC_EMPLOYEE
/
Again after querying the table I was them all listed.
500 rows selected. (output query result)
Now I query DBA_USERS... None of them listed there.
Can someone please advise which other approach to take?
Richard