2 Replies Latest reply: Sep 13, 2013 4:40 AM by Paul M. RSS

    user name

    ABDULLAH18

      i can create a new connection using SYSTEM as a username but can i have another username and if yes how can i get it
      ty in advance

        • 1. Re: user name
          jariola

          Hi,

           

          Login as SYSTEM user and create another user e.g.

          CREATE USER myuser IDENTIFIED BY mypasswd;
          GRANT CONNECT,RESOURCE TO myuser;
          

           

          You can also grant other privileges to new user e.g.

          GRANT CONNECT TO myuser;
          GRANT RESOURCE TO myuser;
          GRANT CREATE SESSION TO myuser;
          GRANT CREATE CLUSTER TO myuser;
          GRANT CREATE DIMENSION TO myuser;
          GRANT CREATE INDEXTYPE TO myuser;
          GRANT CREATE JOB TO myuser;
          GRANT CREATE MATERIALIZED VIEW TO myuser;
          GRANT CREATE OPERATOR TO myuser;
          GRANT CREATE PROCEDURE TO myuser;
          GRANT CREATE SEQUENCE TO myuser;
          GRANT CREATE SNAPSHOT TO myuser;
          GRANT CREATE SYNONYM TO myuser;
          GRANT CREATE TABLE TO myuser;
          GRANT CREATE TRIGGER TO myuser;
          GRANT CREATE TYPE TO myuser;
          GRANT CREATE VIEW TO myuser;
          

           

          Regards,
          Jari

          • 2. Re: user name
            Paul M.

            CREATE USER myuser IDENTIFIED BY mypasswd; GRANT CONNECT,RESOURCE TO myuser;

             

            Assuming we are speaking of XE database (we are on this forum), I'd change something...

             

            On XE the default tablespace, when not specified, is SYSTEM, so I'd assign a different one. Also, RESOURCE role (and this is true for any versions) includes UNLIMITED TABLESPACE privilege, so I wouldn't use it. Example :

            SQL> create user test identified by test;

             

            User created.

             

            SQL> grant connect,resource to test;

             

            Grant succeeded.

             

            SQL> select DEFAULT_TABLESPACE from dba_users

              2  where username='TEST';

             

            DEFAULT_TABLESPACE

            ------------------------------

            SYSTEM

             

            SQL> conn test/test

            Connected.

            SQL> select * from session_privs;

             

            PRIVILEGE

            ----------------------------------------

            CREATE SESSION

            UNLIMITED TABLESPACE

            CREATE TABLE

            CREATE CLUSTER

            CREATE SEQUENCE

            CREATE PROCEDURE

            CREATE TRIGGER

            CREATE TYPE

            CREATE OPERATOR

            CREATE INDEXTYPE

             

            10 rows selected.

             

            SQL>