DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Create User

  1. #1
    Join Date
    May 2001
    Posts
    12

    Lightbulb

    I want to create a user which should have all the rights ie(create table , alter table etc) except select , ie he should not be able to view data.

    I would be really thankfull if anybody could be able to help me

    Amit

  2. #2
    I think it's a rule: Every user can read its own tables.
    What would be the reason for deny the access?
    Ramon Caballero, DBA, rcaballe@yahoo.com

  3. #3
    Join Date
    May 2001
    Posts
    12
    I have a user User1 which is mapped to system tablespace, and has a separate schema USER1.

    I have created a user USER2, mapped to sytem schema, and
    schema User2.

    I want that user2 , should not be able to view tables of User1, but should have rest of privilege ie insert update etc

  4. #4
    user User1 has default tablespace SYSTEM and has objects grouped by schema USER1..right?
    What did you mean with "I have created a user USER2, mapped to sytem schema, and schema User2."?
    I'm guessing user User2 has also SYSTEM tablespace as the default, and you don't want him to see User1 tables, right?
    If I'm right, then you don't have to worry about, even if the tables are in the same tablespace you can't see other schema tables until the other user grant you to do it.
    If I'm wrong, then the only way to do what you want is with stored procedures created by User1.
    ie exec insertrowtable1('data1',data2');
    will insert data into User1's table1 but User2 won't be able to see the data.

    BTW. Never put new tables in tablespace SYSTEM, nor leave it as the default tablespace (even for user System default tablespace is TOOLS)
    Hope it helps
    Ramon Caballero, DBA, rcaballe@yahoo.com

  5. #5
    Join Date
    May 2001
    Posts
    12
    when i login to DBA Studio, i can see all the data in all schemas.
    How can i specify rights in DBA Studio

  6. #6
    Join Date
    Jul 2001
    Location
    Netherlands (Utrecht)
    Posts
    21
    You don't specify that kind of rights in DBA studio. You specify the right by user. So it depends on the user account what you can see or can't see in DBA studio.
    J.Jongman - DBA

  7. #7
    Join Date
    May 2001
    Posts
    12
    I have created a user, which has no roles, no privileges.
    When i logon, as a normal user, it gives an error, that it should have connect role.

    But when i connect as SYSDBA/SYSOPER, it does not give me an error, and i am able to view all the tables, in all schemas.



    [Edited by arbakhru on 07-31-2001 at 07:49 AM]

  8. #8
    Join Date
    May 2001
    Location
    Dallas Texas
    Posts
    8
    If you want the user you created to be able to connect to the database, GRANT CONNECT TO ;

    If you want that user to be able to create objects you also need to GRANT RESOURCE TO ;

    When you connect as SYSDBA you are connecting with a user having the DBA role, which contains the SELECT ANY TABLE privilege. that is why you can see all tables when connecting as a DBA.

    Grant the CONNECT to the normal user, and then connect as a normal user and you shouldn't be able to see anybody else's objects without specific grants from the other user.

  9. #9
    Join Date
    Jul 2001
    Location
    Netherlands (Utrecht)
    Posts
    21
    Oke I just tried some things in DBA studio..

    You must give an user the connect role and the select_catalog_role (minimum). I found this in the help topic of DBA Studio.

    ------------------------
    No Privilege Alert

    You do not have enough privileges to log in. Contact your
    System Administrator or Super Administrator about
    adding the SELECT_CATALOG_ROLE.

    To create the SELECT_CATALOG_ROLE on Oracle
    databases prior to Oracle8, run the script documented on
    the sc_role.sql help panel. This script will have to be run
    as SYS.
    -------------------------

    With this role you can see almost every thing accept other users data. (you can still see the table names but no data)

    (as far as I know DBA Studio is designed for DBA'ers)

    Grtx,
    J.Jongman

    J.Jongman - DBA

  10. #10
    Join Date
    May 2001
    Posts
    12
    Even if i login to sqp*plus, as sysdba, i am able to view tables from other schema, i want to revoke that right.

    a user should not be able to logon as sysdba

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