OracleXE: Unterschied zwischen den Versionen

Version vom 23. April 2014, 09:31 Uhr

create/alter user

sqlplus system@xe

(enter your system password)

create user testing identified by password;
create user production identified by password;
grant connect, resource to testing;
grant connect, resource to production;
exit

this is a simple example including a simple grant

CREATE USER testing IDENTIFIED BY password DEFAULT TABLESPACE MYTBLSPACE TEMPORARY TABLESPACE "TEMP";

more complex example incl. tablespaces

ALTER USER testing QUOTA UNLIMITED ON MYTBLSPACE;

change quota to unlimited

create/alter tablespace

CREATE TABLESPACE <tablespace_name> datafile '<path>' size 32m autoextend on next 32m maxsize 2048m extent management local;

to create a new tablespace...
datafile is full path to DB-file, e.g. /u01/app/oracle/oradata/XE/myTblSpace.dbf

grant

GRANT connect, create procedure, create session, create table, create type, create view, 
  create synonym,create sequence, create trigger, resource TO testing;

or:

GRANT DBA TO testing;

grant all privileges to testing;

grants ALL privileges to the user

grant create session, grant any privilege to testing;

grants the permission to log in and the GRANT permission to the user.
the user then is able to grant all available permissions to a user.

select * from system_privilege_map
  where name like '%PRIV%';

select actual privileges from DB

select count(*),grantee
 from dba_sys_privs
 where grantee in ('MDSYS','TESTING','PRODUCTION')
 group by grantee
/

show

sho user;

shows the username

show all;

shows all values