Wednesday, May 03, 2006

Snippet://Oracle/User

Create Tablespace
CREATE TABLESPACE ts_username
DATAFILE '/path/to/oracle/oradata/SID/ts_username.dbf'
SIZE 10M -- Initial Size
AUTOEXTEND ON NEXT 10M -- Increment Size
MAXSIZE 100M; -- Maximum Quota

Create New User
CREATE USER username
IDENTIFIED BY password
DEFAULT TABLESPACE ts_username -- Defaults to SYS if not specified
TEMPORARY TABLESPACE ts_temp
ACCOUNT LOCK -- Account Locked
PASSWORD EXPIRE; -- Force user to change password at next login

Lock/Unlock User Account:
ALTER USER username ACCOUNT LOCK;
ALTER USER username ACCOUNT UNLOCK;

Change User Password
ALTER USER username IDENTIFIED BY newpassword;

Grant User DDL/DML Rights
GRANT CONNECT TO username;
GRANT RESOURCE TO username;
GRANT DBA TO username; -- Make user a DB Administrator
GRANT SELECT ON tablename TO username;
GRANT CREATE TABLE TO username;
GRANT CREATE VIEW TO username;
GRANT CREATE SEQUENCE TO username;

Delete User Including Related Items Excluding Tables/Views/Sequence
DROP USER username CASCADE;

COMMIT/ROLLBACK
COMMIT; -- Commit Changes
ROLLBACK; -- Roll back changes since last commit

Who Am I
SELECT user, uid FROM dual

Find Active Sessions
SELECT sid, serial#, username, TO_CHAR(logon_time,'Month dd hh24:mi:ss')
FROM sys.v_$session;

Kill Session
ALTER SYSTEM KILL SESSION '12,33' -- 12,33 is the sid and serial#

TNSNAMES
local_SID =
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= hostname.network)(Port= 1521))
(CONNECT_DATA = (SID = remote_SID))
)

1 comment:

pipsqueak said...

grant create session to user;

and also grant the select tables after that. grant connect only if you allow the user to create his/her own schema.

security?