5 Replies Latest reply: Oct 4, 2012 1:03 PM by rp0428 RSS

    Creation of user

    914567
      Already there is a user 'XYZ' for data 'ABC'
      he created some tables in that database.

      Now my requirement is i want to create a new user for the same database
      and he also can visible the tables which 'XYZ' has created
      how t create the user.
        • 1. Re: Creation of user
          Aman....
          911564 wrote:
          Already there is a user 'XYZ' for data 'ABC'
          he created some tables in that database.

          Now my requirement is i want to create a new user for the same database
          and he also can visible the tables which 'XYZ' has created
          how t create the user.
          Start reading docs, its all in there,
          http://docs.oracle.com/cd/E11882_01/server.112/e10897/users_secure.htm#ADMQS0742

          Aman....
          • 2. Re: Creation of user
            Osama_Mustafa
            also read
            http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm
            • 3. Re: Creation of user
              vlethakula
              Assuming your new username would be TEST

              set pages 500
              set lines 200
              set long 100000
              select replace(dbms_metadata.get_ddl( 'USER', 'XYZ' ),'XYZ','TEST') from dual ----->This generates create user script


              2)If you want to give insert,update,delete,select on XYZ tables to new user:

              select ' grant select,insert,update,delete on ' || owner||'.'|| table_name || ' to test;' from dba_tables where owner='XYZ'; ---->This generates grant privileges run them separately
              • 4. Re: Creation of user
                EdStevens
                911564 wrote:
                Already there is a user 'XYZ' for data 'ABC'
                he created some tables in that database.

                Now my requirement is i want to create a new user for the same database
                and he also can visible the tables which 'XYZ' has created
                how t create the user.
                You allow users to do things by GRANTing them privileges

                You allow them to see specific tables by granting SELECT on that specific table.


                I leave the details as an exercise for the student ....

                Learning how to look things up in the documentation is time well spent investing in your career. To that end, you should drop everything else you are doing and do the following:

                Go to tahiti.oracle.com.

                Drill down to your product and version.

                <b><i><u>BOOKMARK THAT LOCATION</u></i></b>

                Spend a few minutes just getting familiar with what is available here. Take special note of the "books" and "search" tabs. Under the "books" tab (for 10.x) or the "Master Book List" link (for 11.x) you will find the complete documentation library.

                Spend a few minutes just getting familiar with what <b><i><u>kind</u></i></b> of documentation is available there by simply browsing the titles under the "Books" tab.

                Open the Reference Manual and spend a few minutes looking through the table of contents to get familiar with what <b><i><u>kind</u></i></b> of information is available there.

                Do the same with the SQL Reference Manual.

                Do the same with the Utilities manual.

                You don't have to read the above in depth. They are <b><i><u>reference</b></i></u> manuals. Just get familiar with <b><i><u>what</b></i></u> is there to <b><i><u>be</b></i></u> referenced. Ninety percent of the questions asked on this forum can be answered in less than 5 minutes by simply searching one of the above manuals.

                Then set yourself a plan to dig deeper.
                - Read a chapter a day from the Concepts Manual.
                - Take a look in your alert log. One of the first things listed at startup is the initialization parms with non-default values. Read up on each one of them (listed in your alert log) in the Reference Manual.
                - Take a look at your listener.ora, tnsnames.ora, and sqlnet.ora files. Go to the Network Administrators manual and read up on everything you see in those files.
                - When you have finished reading the Concepts Manual, do it again.

                Give a man a fish and he eats for a day. Teach a man to fish and he eats for a lifetime.
                • 5. Re: Creation of user
                  rp0428
                  >
                  select replace(dbms_metadata.get_ddl( 'USER', 'XYZ' ),'XYZ','TEST') from dual ----->This generates create user script
                  >
                  Yes it does - and that user will have the same password, default role, default tablespace and other attributes as the original user.

                  Not what you want to do. OP doesn't want to clone the user just create a new one.