6 Replies Latest reply: Dec 16, 2011 2:56 PM by Udo RSS

    How to set a user the following privileges ?

    734946
      Hi ,

      I am new here ,

      I just installed Oracle Express, created a user :

      SQL> create user kcptd identified by password;

      Can you please help me doing this :

      Make sure Oracle user has following privileges:
      * DEFAULT TABLESPACE set to <Users Tablespace> (the intended location where the schema database
      structures and base bootstrap data are stored).
      * QUOTA UNLIMITED ON <Users Tablespace>
      * CREATE SESSION
      * CREATE SYNONYM
      * CREATE PROCEDURE
      * CREATE TRIGGER
      * CREATE TABLE
      * CREATE TYPE
      * CREATE VIEW
      * CREATE SEQUENCE


      Thanks,
      mlotfi
        • 1. Re: How to set a user the following privileges ?
          orafad
          Version?

          Please See documentation about users and privileges
          http://docs.oracle.com/cd/E17781_01/index.htm

          Hint: lookup ALTER USER and GRANT statements.

          For developing database apps, you could take a look at Oracle SQL Developer:
          http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
          • 2. Re: How to set a user the following privileges ?
            Udo
            It's always a good idea to read the documentation, as noted above.
            However, an example can't be that bad:
            ALTER USER kcptd DEFAULT TABLESPACE <your_users_tablespace_here_again> QUOTA UNLIMITED ON <your_users_tablespace_here_again>;
            GRANT CREATE SESSION,
                  CREATE SYNONYM,
                  CREATE PROCEDURE,
                  CREATE TRIGGER,
                  CREATE TABLE,
                  CREATE TYPE,
                  CREATE VIEW,
                  CREATE SEQUENCE
              TO kcptd;
            If you don't have a user-specific tablespace, you could either use the one created as default for all non-system users ("USERS") or create a new one.
            Out of the documentation section referenced above, I'd recommend you to jump into the [url http://docs.oracle.com/cd/E17781_01/server.112/e18804/toc.htm]2 Day DBA. Chapters 6 and 7 cover the topics your questions aimed at.

            -Udo
            • 3. Re: How to set a user the following privileges ?
              734946
              Hi, and thanks for your helps,

              I will use USERS as table space since I don't know and did not find an example on how to do it here in windows.

              When I installed Oracle Express in windows XP :
              1) what is the default database created ?
              2)how to create a new database ?

              Thanks.
              • 4. Re: How to set a user the following privileges ?
                Udo
                Hi,

                I guess you don't acutally mean "database" but "schema", right? Oracle uses a little different wording compared to other database systems like ACCESS or MySQL. You should read the [url http://download.oracle.com/docs/cd/E17781_01/admin.112/e18585/toc.htm]Getting Started Guide. The second chapter is about creating a new user - which you've already done. So your "database" (schema) is kcptd and you can create new ones just as simple as you created that one.

                -Udo
                • 5. Re: How to set a user the following privileges ?
                  734946
                  Thanks lot,

                  so in oracle when you create a user it is in the same time the database (schema) ?
                  • 6. Re: How to set a user the following privileges ?
                    Udo
                    Exactly.

                    Please close this thread if your question is answered and mark any helpful or correct answer accordingly, so other users searching this forum can spot relevant posts easily.

                    Thanks,

                    Udo