Grant CREATE SESSION to mass users
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Grant CREATE SESSION to mass users

  1. #1
    Join Date
    Jul 2001
    Posts
    45
    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  3. #3
    Join Date
    Jul 2001
    Posts
    45

    Grant create session to mass users

    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.

  4. #4
    Join Date
    Mar 2001
    Posts
    635
    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

    Santosh

  5. #5
    Join Date
    Jul 2001
    Posts
    45
    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

  6. #6
    Join Date
    Jul 2001
    Posts
    45

    Grant create session to mass users

    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

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  8. #8
    Join Date
    Jul 2001
    Posts
    45
    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.

  9. #9
    Join Date
    Sep 2001
    Location
    SWEDEN
    Posts
    70
    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.

  10. #10
    Join Date
    Jul 2001
    Posts
    45
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width