1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to write a query that generates the Create USer command

Discussion in 'SQL PL/SQL' started by DARIUSD7, Aug 9, 2014.

  1. DARIUSD7

    DARIUSD7 Guest

    I have an assignment that is asking me to Write a query that generates the CREATE USER commands for all the managers, writers, and editors, The Create user command must include the appropriate profile. All the users have a password of TEMPPASS and the password expires when the user is created

    Each employee needs a user name and password for the database. The employee's name is the first name plus the first three letters of the last name.

    I created three profiles .. Managers, Writers, and Editors.

    What i don't understand is that is is saying to write a query that generate the Create USER command. I know how to create a user...ie Create User <username> identified by <???>...
    but this is suggesting to do it in a query.. I'm not quite sure how that is done. Can anyone help me with this please.. Thanks..
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have never selected static text in a query? It's fairly simple:


    Code (SQL):

    SQL> SELECT 'Bob' FROM dual;


    'BO
    ---
    Bob


    SQL>

    The question now becomes from which table are you selecting values? Provide that information and more assistance can be given.
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    You can adapt and try something like this:

    Code (SQL):
    SELECT
    'create user '
    || REPLACE(first_name,' ','')
    || substr(last_name, 1,3)
    ||' identified by TEMPPASS expire;'
    FROM hr.employees;
    1) hr.employees is my employees table from where I get the last name and first name (replace by your employees table)
    2) Used "replace" just in case you have white spaces in your name, for ex., "Mary Jane".

    You can spool to a file and execute the file in SQLPLUS to create your users in batch.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Here is a working example based on a table named user_data:


    Code (SQL):

    SQL> CREATE TABLE user_data(
      2  username        varchar2(40),
      3  userprof        varchar2(20),
      4  db_user         varchar2(1));


    TABLE created.


    SQL>
    SQL> INSERT ALL
      2  INTO user_data
      3  VALUES('MerfBerfman','Manager','Y')
      4  INTO user_data
      5  VALUES('BerfMerfman','Writer','Y')
      6  INTO user_data
      7  VALUES('NerfGerfman','Editor','Y')
      8  SELECT * FROM dual;


    3 ROWS created.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> SELECT DISTINCT 'create profile '||userprof||'s limit password_verify_function null;'
      2  FROM user_data
      3
    SQL> spool cr_publish_profiles.SQL
    SQL> /


    'CREATEPROFILE'||USERPROF||'SLIMITPASSWORD_VERIFY_FUNCTIONNULL;'
    -------------------------------------------------------------------------
    CREATE profile Managers LIMIT password_verify_function NULL;
    CREATE profile Editors LIMIT password_verify_function NULL;
    CREATE profile Writers LIMIT password_verify_function NULL;


    SQL> spool off
    SQL>
    SQL> @cr_publish_profiles
    SQL>
    SQL> CREATE profile Managers LIMIT password_verify_function NULL;


    Profile created.


    SQL> CREATE profile Editors LIMIT password_verify_function NULL;


    Profile created.


    SQL> CREATE profile Writers LIMIT password_verify_function NULL;


    Profile created.


    SQL>
    SQL>
    SQL> SELECT 'create user '||username||' identified by temppass profile '||userprof||'s;'
      2  FROM user_data
      3  WHERE db_user = 'Y'
      4
    SQL>
    SQL> spool cr_publish_users.SQL
    SQL> /


    'CREATEUSER'||USERNAME||'IDENTIFIEDBYTEMPPASSPROFILE'||USERPROF||'S;'
    --------------------------------------------------------------------------------
    CREATE USER MerfBerfman IDENTIFIED BY temppass profile Managers;
    CREATE USER BerfMerfman IDENTIFIED BY temppass profile Writers;
    CREATE USER NerfGerfman IDENTIFIED BY temppass profile Editors;


    SQL> spool off

    SQL>
    SQL> @cr_publish_users
    SQL>
    SQL> CREATE USER MerfBerfman IDENTIFIED BY temppass profile Managers;


    USER created.


    SQL> CREATE USER BerfMerfman IDENTIFIED BY temppass profile Writers;


    USER created.


    SQL> CREATE USER NerfGerfman IDENTIFIED BY temppass profile Editors;


    USER created.


    SQL>
    SQL>
    SQL>
    SQL> SELECT 'drop user '||username||' cascade;'
      2  FROM user_data
      3  WHERE db_user='Y'
      4
    SQL> spool drop_publish_users.SQL
    SQL> /


    'DROPUSER'||USERNAME||'CASCADE;'
    -----------------------------------------------------------
    DROP USER MerfBerfman cascade;
    DROP USER BerfMerfman cascade;
    DROP USER NerfGerfman cascade;


    SQL> spool off
    SQL>
    SQL> @drop_publish_users
    SQL>
    SQL> DROP USER MerfBerfman cascade;


    USER dropped.


    SQL> DROP USER BerfMerfman cascade;


    USER dropped.


    SQL> DROP USER NerfGerfman cascade;


    USER dropped.


    SQL>
     

    It's pretty simple, really.