how to create a user?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: how to create a user?

  1. #1
    Join Date
    Jul 2000
    Posts
    70

    Question

    How do I create a new user in Oracle who has only read rights to a certain database?
    An sql example would help.
    Thanks.

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    I guess its may be little big task to create and run scripts with 'SELECT' Privileges on all of the tables if your database is too big.

    If its not big enough, could spend 5 to 10 minutes and create a role READONLY_USER and give all the select tables privileges to that role and assign that role to the user you wanted.

    Not sure, your intention was little more simplified task or this is what you wanted....

  3. #3
    Join Date
    Jun 2000
    Location
    Conway,AR,USA
    Posts
    29
    Step 1.
    Get all the tables the user wants read-only access.
    Step 2.
    Create a role and assign read only access for the tables above to the role
    Step 3.
    Create an user and assign the role created in step 2 to the user
    Soumya
    still learning

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    Don't forget to grant CREATE SESSION privilege to the user or the role.

  5. #5
    Join Date
    Jul 2000
    Posts
    70
    Ok, whats the sql statement that would allow me to create a user to have full access to a certain database, but only to that database. No other tables would be accessable by that user.
    Thanks.

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    1) Create user XYZ with 'CREATE SESSION' privilege
    <font face=courier>
    create user XYZ profile DEFAULT identified by *****
    default tablespace USERS
    temporary tablespace TEMP
    account unlock;

    grant 'CREATE SESSION' to XYZ ;
    </font>
    2) Create role READONLY
    <font face=courier>
    create role READONLY ;
    </font>
    3) Run the following script
    <font face=courier>
    set echo off
    set feedback off
    set heading off
    spool temp.sql
    select 'GRANT SELECT ON '||owner||'.'||object_name||' to READONLY ;'
    from dba_objects
    --from user_objects
    --from all_objects
    where object_type='TABLE'
    and owner not in ('SYS','SYSTEM')
    /
    spool off
    --@temp.sql
    /

    set echo on
    set feedback on
    set heading on
    </font>
    4) grant created role to user XYZ
    <font face=courier>
    grant READONLY to XYZ;
    </font>

    ***Uncomment to make spooled script to run.
    ***Modify it per your requirement including/excluding owners.
    ***You can USE ALL_OBJECTS/USER_OBJECTS views instead of DBA_OBJECTS whichever is appropriate for your situation.
    *** OWNER is not valid column in user_objects view

    [Edited by sreddy on 01-26-2001 at 02:21 PM]

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