Data Control Language

Role and System Privileges

In database management, role and system privileges are essential for controlling access and permissions effectively. While roles and system privileges can be granted together, they cannot be granted with object privileges.

Data Dictionary

The data dictionary in a database contains metadata, which is data about the data stored within the database. Some important tables within the data dictionary include:

• DBA_TABLES: Contains information about all tables in the database.
• ALL_TABLES: Lists all tables that the current user has privileges to access.
• USER_TABLES: Displays all tables owned by a specific user.

Common attributes found in these tables include TABLE_NAME, STATUS, ROW_MOVEMENT, AVG_ROW_LEN, and OWNER.

System Operations

System operations involve managing privileges within a database. Some key operations include:

• GRANT privilege TO user: Grants a specific privilege to a user.
• REVOKE privileges FROM user: Removes previously granted privileges from a user.
• Use ANY: Allows granting privileges to any user without specifying a particular user.
• WITH ADMIN OPTION: Enables a user to grant the same privilege to others.
• Revoking WITH ADMIN OPTION: Revocation of this option does not cascade to other users.
• GRANT ALL PRIVILEGES TO user: Grants all available privileges to a user.
• GRANT privileges To PUBLIC: Grants specific privileges to all users (public).

Object Privileges

Object privileges control access to specific objects in the database. Important points regarding object privileges include:

• PUBLIC SYNONYMS: These do not require access permissions as they are public, but the table they reference needs specific permission.
• GRANT user PRIVILEGES ON table TO user WITH GRANT OPTION: Allows a user to grant the same privileges to others.
• ALL PRIVILEGES: Can be abbreviated as ALL when granting privileges on objects.
• REVOKE privileges ON table FROM user: Revokes privileges on a specific table, cascading to related objects.

Column-Level Privileges

Column-level privileges involve granting permissions at the column level, usually limited to INSERT, UPDATE, and REFERENCES operations. When granting INSERT at the column level, all non-null columns in the row must be included.

User Privileges

Understanding user privileges is crucial for managing access within a database. Some key aspects include:

• USER_SYS_PRIVS (PRIVILEGE, ADMIN_OPTION): Displays privileges for the current user.
• USER_TAB_PRIVS: Shows on whom the user has granted privileges.

Types of Users

Users in a database are categorized based on their roles and permissions. Common types of users include:

• CONNECT: Typically used for generic user sessions.
• RESOURCE: Commonly assigned to application developers for creating tables.
• DBA: Designates a database administrator with extensive privileges.
• CREATE ROLE rolename: Creates a new role in the database.
• GRANT privilege TO rolename: Grants a specific privilege to a defined role.
• GRANT role TO user: Assigns a role to a user without cascading effects.
• Role and privilege assignments: These assignments are independent of each other.