Essential SQL Commands for User and Privilege Management

Creating a User

SQL Command

CREATE USER <nombre_usuario> IDENTIFIED BY <password>;

Granting Privileges

SQL Command

GRANT CONNECT TO <nombre_usuario>;

Creating a Profile

SQL Command

CREATE PROFILE profile_name LIMIT limit(s) range;

Limits

• SESSIONS_PER_USER
• CPU_PER_SESSION
• CPU_PER_CALL
• CONNECT_TIME
• IDLE_TIME
• LOGICAL_READS_PER_SESSION
• LOGICAL_READS_PER_CALL
• COMPOSITE_LIMIT
• PRIVATE_SGA

Range Options

• UNLIMITED
• DEFAULT
• Integer value

Example Profile Creation

CREATE PROFILE MyProfile LIMIT PRIVATE_SGA 50K;

CREATE PROFILE app_user LIMIT

• SESSIONS_PER_USER UNLIMITED
• CPU_PER_SESSION UNLIMITED
• CPU_PER_CALL 3000
• CONNECT_TIME 45
• LOGICAL_READS_PER_SESSION DEFAULT
• LOGICAL_READS_PER_CALL 1000
• PRIVATE_SGA 15K
• COMPOSITE_LIMIT 5000000;

Creating Additional Profiles

CREATE PROFILE app_user2 LIMIT

• FAILED_LOGIN_ATTEMPTS 5
• PASSWORD_LIFE_TIME 60
• PASSWORD_REUSE_TIME 60
• PASSWORD_REUSE_MAX 5
• PASSWORD_VERIFY_FUNCTION verify_function
• PASSWORD_LOCK_TIME 1/24
• PASSWORD_GRACE_TIME 10;

Creating a Schema

SQL Command

CREATE USER <esq001> IDENTIFIED BY a1234 PROFILE <nombre_profile>;

Granting Privileges to Schema

GRANT RESOURCE, CONNECT TO <esq001>;

Creating a Tablespace for Auditing

Deploying Audit Data

SHOW PARAMETER AUDIT;

Activating Auditing

ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE;

Creating a User for Testing

SQL Command

CREATE USER audit_test IDENTIFIED BY password

• DEFAULT TABLESPACE users
• TEMPORARY TABLESPACE temp
• QUOTA UNLIMITED ON users;

GRANT CONNECT TO audit_test;

GRANT CREATE TABLE, CREATE PROCEDURE TO audit_test;

Activating Audit for the User

SQL Commands

• AUDIT ALL BY audit_test BY ACCESS;
• AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;
• AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;

Reviewing the Audit

SQL Command

SELECT db_user, extended_timestamp, object_schema, object_name, action

FROM v$xml_audit_trail

WHERE object_schema = 'AUDIT_TEST'

ORDER BY extended_timestamp;

Creating Roles

SQL Command

SELECT 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO rol_sel_020;'

FROM dba_tables

WHERE owner = 'HR';