Database Management: Users, Privileges, Roles, Profiles, Tablespaces

Database User Management

Creating and Modifying Users

Use the CREATE USER or ALTER USER command to manage user accounts, defining their authentication, default storage, resource limits, and account status:

CREATE/ALTER USER username IDENTIFIED BY password
[DEFAULT TABLESPACE tablespace_name]
[TEMPORARY TABLESPACE tablespace_name]
[QUOTA {integer {K|M} | UNLIMITED} ON tablespace_name]
[PROFILE profile_name]
[ACCOUNT {LOCK|UNLOCK}];

Dropping Users

To remove a user and all their associated schema objects, use the DROP USER command with the CASCADE option:

DROP USER user_name CASCADE;

Database Privilege Management

Granting Object and System Privileges

The GRANT command assigns specific object or system privileges to users or roles:

GRANT SELECT ANY TABLE TO PUBLIC;
GRANT INSERT, UPDATE ON table1 TO francisco;
GRANT ALL ON table1 TO PUBLIC;
GRANT UPDATE(column_name) ON table1 TO juan;

Revoking Object and System Privileges

The REVOKE command removes previously granted privileges from users or roles:

REVOKE UPDATE, SELECT ON table1 FROM francisco;
REVOKE ALL ON table1 FROM francisco, juan;
REVOKE DROP USER FROM milagros;
REVOKE SELECT ANY TABLE FROM PUBLIC;

Privilege Information Views

Query these data dictionary views to inspect granted privileges:

• USER_TAB_PRIVS, DBA_TAB_PRIVS, ALL_TAB_PRIVS
• USER_TAB_PRIVS_MADE, ALL_TAB_PRIVS_MADE
• USER_TAB_PRIVS_RECD, ALL_TAB_PRIVS_RECD
• USER_COL_PRIVS, DBA_COL_PRIVS, ALL_COL_PRIVS
• USER_COL_PRIVS_MADE, ALL_COL_PRIVS_MADE
• USER_COL_PRIVS_RECD, ALL_COL_PRIVS_RECD

Database Role Management

Creating and Assigning Roles

Roles simplify privilege management by grouping related privileges, which can then be granted to users:

CREATE ROLE access_role;
GRANT SELECT, INSERT ON employees TO access_role;
GRANT INSERT ON departments TO access_role;
GRANT CREATE SESSION TO access_role;
GRANT access_role TO username;

Revoking Role Privileges

Remove specific privileges from a role using the REVOKE command:

REVOKE INSERT ON employees FROM access_role;
REVOKE CREATE TABLE FROM access_role;

Dropping Roles

To remove a role from the database:

DROP ROLE access_role;

Setting Default Roles for Users

Assign a default role to a user so it is automatically enabled upon session creation:

ALTER USER username DEFAULT ROLE access_role;

Role Information Views

These views provide details about roles and their assigned privileges:

• ROLE_SYS_PRIVS
• ROLE_TAB_PRIVS
• ROLE_ROLE_PRIVS
• SESSION_ROLES
• USER_ROLE_PRIVS

Database Profile Management

Enabling Resource Limits

Before using profiles to enforce resource limits, ensure this feature is enabled at the system level:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

Creating and Altering Profiles

Profiles enforce resource limits and password policies for users. Use CREATE PROFILE or ALTER PROFILE:

CREATE/ALTER PROFILE performance_profile
LIMIT SESSIONS_PER_USER 1
CONNECT_TIME 2;

CREATE/ALTER PROFILE profile1
LIMIT SESSIONS_PER_USER UNLIMITED
CONNECT_TIME DEFAULT;

Dropping Profiles

Remove a profile using the DROP PROFILE command. The CASCADE option reassigns users to the DEFAULT profile:

DROP PROFILE profile1 CASCADE;

Tablespace Management

Creating Tablespaces

Tablespaces are logical storage units in a database. Use CREATE TABLESPACE to define them:

CREATE TABLESPACE small_ts
DATAFILE 'work1.ora' SIZE 10M,
         'work2.ora' SIZE 5M
DEFAULT STORAGE (
    INITIAL 10K
    NEXT 10K
    PCTINCREASE 25
);

CREATE TABLESPACE small_ts
DATAFILE 'small_ts.ora' SIZE 100K
AUTOEXTEND ON NEXT 120K MAXSIZE 1M;

Modifying Tablespaces

Adjust tablespace properties or add datafiles using ALTER TABLESPACE:

ALTER TABLESPACE small_ts ADD DATAFILE 'new_datafile.ora' SIZE 6M;
ALTER TABLESPACE small_ts OFFLINE;

Dropping Tablespaces

Remove a tablespace and its contents, including datafiles, with DROP TABLESPACE:

DROP TABLESPACE small_ts INCLUDING CONTENTS AND DATAFILES;

Table Storage Parameters

Defining Storage for Tables

Specify storage characteristics when creating a table to control how data is allocated and managed:

CREATE TABLE table_name (
    column1 datatype NOT NULL,
    CONSTRAINT pk_table PRIMARY KEY (column1)
)
STORAGE (
    INITIAL size
    NEXT size
    MINEXTENTS size
    MAXEXTENTS size
    PCTINCREASE value
)
[TABLESPACE tablespace_name];

Tablespace Information Views

These data dictionary views provide details about tablespaces, their datafiles, and free space:

• DBA_DATA_FILES
• USER_FREE_SPACE, DBA_FREE_SPACE
• USER_TABLESPACES, DBA_TABLESPACES
• USER_TS_QUOTAS, DBA_TS_QUOTAS