Oracle Database User Management and Privilege Control

Oracle User Creation and Privileges

To create a user, you must have the CREATE SESSION system privilege. Use the following syntax:

CREATE USER <user> IDENTIFIED BY <password> | EXTERNALLY
DEFAULT TABLESPACE <space>
TEMPORARY TABLESPACE <space>
QUOTA <xx> K | UNLIMITED ON <space>
PROFILE <profile>
PASSWORD EXPIRE
ACCOUNT LOCK | UNLOCK;

Example:

CREATE USER zebra IDENTIFIED BY cebrapassword
DEFAULT TABLESPACE system
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON system;

Managing Sessions and User Deletion

To force the completion or termination of a session, use: ALTER SYSTEM KILL SESSION 'sid, serial#';. To delete a user, you must have the DROP USER system privilege.

Syntax:

• DROP USER <user>;
• DROP USER <user> CASCADE;

Database Administration Commands

System Objects and Resources

• Rollback Segments: CREATE, ALTER, DROP
• Roles: CREATE, ALTER ANY, DROP ANY
• Profiles: CREATE, ALTER, DROP
• Database Links: CREATE, CREATE PUBLIC, DROP PUBLIC

Table and Index Privileges

• Tables/Indexes: CREATE ANY, ALTER ANY, DROP ANY, DELETE ANY, INSERT ANY, SELECT ANY, UPDATE ANY
• Tablespaces: CREATE, ALTER, DROP, UNLIMITED

Granting and Revoking Privileges

Use the GRANT and REVOKE commands to manage access control:

GRANT <privilege> | ALL PRIVILEGES TO <user> | <role> | PUBLIC [WITH ADMIN OPTION];
REVOKE <privilege> | ALL PRIVILEGES FROM <user> | <role> | PUBLIC;

Role Management:

CREATE ROLE <role> [IDENTIFIED BY <password>];
ALTER ROLE <role> NOT IDENTIFIED | IDENTIFIED BY <password>;
GRANT <role> TO <user> | <role> | PUBLIC;

Practical Examples

• GRANT CREATE SESSION TO zebra;
• GRANT CREATE SESSION TO cebrita;
• GRANT SELECT ON cebra.usuario TO cebrita;

Table Creation:

CREATE TABLE table_name (
  name VARCHAR2(20),
  description VARCHAR2(20)
);

Connection Management:

sqlplus / nolog
CONN / AS SYSDBA