KfWiki: Create Oracle user

HomePage » Database » Oracle » OracleUsers

Create Oracle user

Create a role so it can be assigned to future users

# Privileged user

CREATE ROLE "DBUSR_SUPER"  NOT IDENTIFIED;

GRANT "SELECT_CATALOG_ROLE" TO "DBUSR_SUPER";

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE ROLE, CREATE PROCEDURE  TO "DBUSR_SUPER"; 

GRANT CREATE ANY SYNONYM, CREATE PUBLIC SYNONYM, CREATE ANY SEQUENCE TO "DBUSR_SUPER";

# Unprivileged user

CREATE ROLE "DBUSR_NORMAL" NOT IDENTIFIED;

GRANT "SELECT_CATALOG_ROLE" TO "DBUSR_NORMAL";

GRANT CREATE SESSION TO DBUSR_NORMAL;

create a tablespace

CREATE tablespace "MYTABLESPACE"

    logging

    datafile 

    '/ora200g/dbfiles/marlow_tbs1.dbf' size 500M,

    '/oradata/devdb/marlow_tbs2.dbf' size 500M

    extent management LOCAL segment space management auto;

Create Users

CREATE user mytbsusr IDENTIFIED BY xxxxxx

DEFAULT tablespace mytablespace

quota unlimited ON mytablespace;

GRANT dbusr_normal TO mytbsusr;

Create index tablespace

CREATE tablespace "MYTBS_IDX" 

    logging

    datafile '/ora200g/dbfiles/mytbs_idx1.dbf' SIZE 300M 

    extent management LOCAL segment space management auto;

Changing password of sys

sqlplus /nolog
SQL> connect sys as sysdba
SQL> alter user sys identified by oracle_password;

Query privileges granted to a certain user:

select * from dba_sys_privs where grantee = 'FOO';

KfWiki : OracleUsers

Create Oracle user