Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Aug 2007
    Posts
    10

    Unanswered: Creating User in Oracle 8

    Hi,
    I have a problem for a user I created. This user is only allowed to perform select statements or view tables only. However, I cannot seem to view anything using the user. This is what I did.

    1) Create the user.
    SQL> CREATE USER smith
    2> IDENTIFIED BY smithy
    3> DEFAULT TABLESACE tools
    4> TEMPORARY TABLESPACE temp;


    2) Check to see if user exist.
    SQL>select * from dba_users;
    User exists.

    3) Grant some privileges.
    SQL> grant SELECT ANY TABLE, SELECT ANY SEQUENCE to smith;
    SQL> grant create session to smith;


    When I do a simple SELECT statement,
    SQL>SELECT * FROM tabs;
    it returns 0 rows.

    What am I doing wrong? Is there any steps that I forgot to include? Any help would be appreciated. Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You didn't create any table so far, so how can you expect to see something that doesn't exist?

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by jijou
    SELECT * FROM tabs;
    From the manual:

    "TABS is a synonym for USER_TABLES."
    "USER_TABLES describes all relational tables owned by the current user"

    As you have not created any tables with that user, he does not own any tables, thus USER_TABLES is empty.

    ALL_TABLES will show all tables "accessible to the current user", so you should use that instead of TABS

  4. #4
    Join Date
    Aug 2007
    Posts
    10
    Hi shammat,

    Thanks for clearing that up for me!
    I am able to view the tables in my database.

  5. #5
    Join Date
    Aug 2007
    Posts
    10
    Hi all,

    I have another small problem.

    I have several hundred tables owned by the user rick.
    How do I grant SELECT privileges for user smith for all the tables owned by rick? I only want smith to be able to view the tables, and unable to INSERT, UPDATE, etc.
    Both of these users have the same DEFAULT_TABLESPACE

    Any help would be appreciated.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    CONNECT rick/his_password;

    GRANT SELECT ON table1 TO smith;
    GRANT SELECT ON table2 TO smith;
    etc.

    All tables, one by one.

    If you don't want to type 100 lines of more-or-less the same, you could spool result of such a script and execute it when looged as tables' owner:
    Code:
    SPOOL grant_mike.sql
    
    SELECT 'GRANT SELECT ON ' || table_name || ' TO smith;'
    FROM USER_TABLES;
    
    SPOOL off;
    
    @grant_mike.sql

  7. #7
    Join Date
    Aug 2007
    Posts
    10
    Hi Littlefoot,

    I tried exactly as you advised, but it still didn't work

    I just tried it on one table to see if it works, but it still returned the same error:
    ORA-00942: table or view does not exist

    Any idea why it does not work?
    Last edited by jijou; 08-22-07 at 21:40.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Because the table doesn't exist.

    What happens if you remove SPOOL commands from my example? Can you select ANYTHING from this query?

    Could you copy-paste the whole SQL*Plus session so that we could see it (like this):
    Code:
    C:\>sqlplus scott/tiger
    
    SQL*Plus: Release 10.2.0.1.0 - Production on ╚et Kol 23 06:28:34 2007
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> SELECT 'GRANT SELECT ON ' || table_name || ' TO smith;'
      2  FROM USER_TABLES;
    
    'GRANTSELECTON'||TABLE_NAME||'TOSMITH;'
    --------------------------------------------------------
    GRANT SELECT ON BONUS TO smith;
    GRANT SELECT ON EMP TO smith;
    GRANT SELECT ON DEPT TO smith;
    GRANT SELECT ON SALGRADE TO smith;
    GRANT SELECT ON T_MASTER TO smith;
    GRANT SELECT ON T_DETAIL TO smith;
    
    6 rows selected.
    
    SQL>

  9. #9
    Join Date
    Aug 2007
    Posts
    10
    Here you go:

    Code:
    $ sqlplus rick/ricky
    
    SQL*Plus: Release 8.0.5.0.0 - Production on Thu Aug 23 14:15:40 2007
    
    (c) Copyright 1998 Oracle Corporation.  All rights reserved.
    
    
    Connected to:
    Oracle8 Enterprise Edition Release 8.0.5.2.1 64bit - Production
    With the Partitioning and Objects options
    PL/SQL Release 8.0.5.2.0 - Production
    
    SQL> SELECT 'GRANT SELECT ON ' || table_name || ' TO smith;'
      2  FROM USER_TABLES;
    
    'GRANTSELECTON'||TABLE_NAME||'TOSMITH;'
    ---------------------------------------------------------
    GRANT SELECT ON ABM_RESIDUE_COMBINE TO smith;
    GRANT SELECT ON EPC_COLLECTION TO smith;
    GRANT SELECT ON EPC_EVENT TO smith;
    GRANT SELECT ON EPC_EVENT_ITEM TO smith;
    GRANT SELECT ON EPC_FACILITY TO smith;
    GRANT SELECT ON EPC_FACILITY_REGISTRATION TO smith;
    GRANT SELECT ON EPC_ITEM TO smith;
    GRANT SELECT ON EPC_PROCESS TO smith;
    GRANT SELECT ON EVT_CARRIER_CONFIGURATION TO smith;
    GRANT SELECT ON EVT_DEST_PROFILE TO smith;
    GRANT SELECT ON EVT_HISTORY TO smith;
    
    'GRANTSELECTON'||TABLE_NAME||'TOsmith;'
    ---------------------------------------------------------
    GRANT SELECT ON EVT_INSTANCE TO smith;
    GRANT SELECT ON EVT_MAIL_CONFIGURATION TO smith;
    GRANT SELECT ON EVT_MONITOR_NODE TO smith;
    GRANT SELECT ON EVT_NOTIFY_STATUS TO smith;
    GRANT SELECT ON EVT_OPERATORS TO smith;
    GRANT SELECT ON EVT_OPERATORS_ADDITIONAL TO smith;
    
    ..... WAY TOO MANY TO LIST OUT .....
    
    GRANT SELECT ON TMP_OLD_ABM TO smith;
    GRANT SELECT ON TMP_OLD_ABM2 TO smith;
    GRANT SELECT ON TMP_PACK_MATHOD TO smith;
    GRANT SELECT ON TMP_RESIDUE_COMBINE TO smith;
    GRANT SELECT ON TMP_SCREEN_ACCESS TO smith;
    GRANT SELECT ON TMP_SHIP TO smith;
    GRANT SELECT ON TMP_TEST TO smith;
    
    'GRANTSELECTON'||TABLE_NAME||'TOsmith;'
    ---------------------------------------------------------
    GRANT SELECT ON TMP_TESTER_HANDLER TO smith;
    GRANT SELECT ON TMP_TESTER_HANDLER_DEL TO smith;
    GRANT SELECT ON TMP_TESTER_TAB1 TO smith;
    GRANT SELECT ON TMP_TESTER_TAB_ABM TO smith;
    GRANT SELECT ON TMP_WIP_LOTISSUED TO smith;
    GRANT SELECT ON TMP_WS_CUST_HANDLER TO smith;
    GRANT SELECT ON TMP_WS_TEST_TIME TO smith;
    
    480 rows selected.
    
    SQL> exit
    Disconnected from Oracle8 Enterprise Edition Release 8.0.5.2.1 64bit - Production
    With the Partitioning and Objects options
    PL/SQL Release 8.0.5.2.0 - Production
    $
    $
    $ sqlplus smith/smithy
    
    SQL*Plus: Release 8.0.5.0.0 - Production on Thu Aug 23 14:16:27 2007
    
    (c) Copyright 1998 Oracle Corporation.  All rights reserved.
    
    
    Connected to:
    Oracle8 Enterprise Edition Release 8.0.5.2.1 64bit - Production
    With the Partitioning and Objects options
    PL/SQL Release 8.0.5.2.0 - Production
    
    SQL> select * from TMP_OLD_ABM;
    select * from TMP_OLD_ABM
                  *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    
    SQL>
    By the way, I did a SELECT statement on TMP_OLD_ABM table using rick user and there are data in there.
    Last edited by jijou; 08-23-07 at 03:31.

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Huh ... are you sure you read Concepts and SQL Reference books from the Oracle Documentation page?

    This is what you did:
    - connect as rick
    - spooled ~500 GRANT SELECT ON some_table TO smith; statements
    - connect as smith
    - wanted to SELECT FROM some_table, but it didn't work.

    How on Earth would it work?
    Did 'smith' create this table in his own schema? No, he didn't.
    Did 'rick' actually GRANT privileges on his tables to 'scotty'? No, he didn't.

    This is what you should have done:
    - connect as rick
    - spool ~500 GRANT SELECT ON some_table TO smith; statements
    - EXECUTE this spooled_file.sql which would GRANT SELECT privileges on rick's tables to 'smith'
    - connect as smith
    - SELECT FROM rick.some_table. You'll need to reference owner, or create a synonym in smith's schema (so you wouldn't need to use owner's name).

    Capisci?

  11. #11
    Join Date
    Mar 2007
    Posts
    623
    - SELECT FROM rick.some_table. You'll need to reference owner, or create a synonym in smith's schema (so you wouldn't need to use owner's name).
    Third possibility (hope it works even in version 8.0, as the oldest documentation I was able to find was for 8.1.7) would be issuing
    ALTER SESSION SET CURRENT_SCHEMA = rick;
    Then you may reference to rick's objects without reference or need to create synonyms, however you will not be able to access smith's objects directly.

  12. #12
    Join Date
    Aug 2007
    Posts
    10
    Hi guys,

    Thanks again for your valuable inputs.

  13. #13
    Join Date
    Nov 2008
    Posts
    3
    hi,
    i have a problem in creating a new user.
    can you tell me how to create a new user.
    i tried this...
    sql>create user vik
    2 identified by lion
    3 temporary tablespace temp
    4 default tablespace users;

    but it's saying insufficient privilege.
    pls help me.
    thank you.

  14. #14
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    User which you have used to run the CREATE USER statement doesn't have required privileges, so it is unable to create a new user.

    How did you connect to the database? Which username did you use? Although SYS isn't the right choice, it IS a privileged user and will be able to create a new user.

  15. #15
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your user smith has been granted select any table so privileges are not the problem. If you want to select from rick, use the schema name

    select * from rick.the_table;


    Sorry flyboy, I just noticed your post.
    Last edited by beilstwh; 11-05-08 at 14:42.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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