Oracle administrative notes Login as system: sqlplus / as sysdba Users: SELECT * FROM all_users; create a user/schema: CREATE USER cmis320 IDENTIFIED BY thepassword; needs privilege to logon: (actually, connect is a role of various permissions) GRANT connect TO cmis320; Or: GRANT CREATE SESSION TO cmis320; needs privilege to create objects: resource: another role of privileges GRANT resource TO cmis320; remove a privilege: REVOKE Create Session FROM cmis320; System privileges allow user to perform certain database actions, viz. DDL like create and drop table, sequence, session, synonym, procedure, user, view. execute any procedure *********************** from web interface: User Privileges Roles: CONNECT RESOURCE DBA Direct Grant System Privileges: CREATE DATABASE LINK CREATE MATERIALIZED VIEW CREATE PROCEDURE CREATE PUBLIC SYNONYM CREATE ROLE CREATE SEQUENCE CREATE SYNONYM CREATE TABLE CREATE TRIGGER CREATE TYPE CREATE VIEW *********************** Object privileges allow user to perform certain actions on objects (e.g. tables and columns) viz. DML like Select, Insert, Update, Delete, Execute GRANT SELECT,UPDATE ON schemaname.tablename TO cmis320; GRANT UPDATE(columnname,columnname2) ON schemaname.tablename TO cmis320; who am i: SHOW User what system privileges do I have: SELECT * FROM user_sys_privs; what roles have I been granted: SELECT * FROM user_role_privs; what system privileges have been granted to a role: SELECT * FROM role_sys_privs; table object privileges I have received from other users: SELECT * FROM user_tab_privs_recd; column object privileges I have received from other users: SELECT * FROM user_col_privs_recd; table object privileges I given to other users: SELECT * FROM user_tab_privs_made; column object privileges I given to other users: SELECT * FROM user_col_privs_made; change password: ALTER USER cmis320 IDENTIFIED BY thenewpassword; change your own password: PASSWORD connect as different user: CONNECT cmis320 CONNECT cmis320/thepassword remove a user: DROP USER cmis320 CASCADE; (cascade needed if has any objects)