System Privileges with SQL

System Privileges

Definition:

System Privileges are rights that allow users to perform administrative or system-level operations within the database management system (DBMS). These privileges are typically associated with the ability to manage database objects, users, and database configuration settings.

Common System Privileges:

• CREATE SESSION:
  • Allows a user to connect to the database.
• CREATE TABLE:
  • Allows a user to create new tables in the database.
• CREATE USER:
  • Allows a user to create new database users.
• ALTER USER:
  • Allows a user to modify the attributes of existing users.
• DROP USER:
  • Allows a user to remove existing users from the database.
• GRANT ANY PRIVILEGE:
  • Allows a user to grant any system or object privilege to other users.
• SHUTDOWN:
  • Allows a user to shut down the database.
• BACKUP DATABASE:
  • Allows a user to perform backup operations on the database.

Granting System Privileges:

System privileges are typically granted to users with roles such as DBA (Database Administrator) or system administrators.

Example Syntax: 

-- Grant CREATE TABLE system privilege to a user
GRANT CREATE TABLE TO userA;
-- Grant CREATE USER system privilege to a user
GRANT CREATE USER TO userB;

Object Privileges

Definition:

Object Privileges are rights that allow users to perform specific operations on individual database objects, such as tables, views, or procedures. These privileges are related to data manipulation and object management within a schema.

Common Object Privileges:

• SELECT:
  • Allows a user to read data from a table or view.
• INSERT:
  • Allows a user to add new rows to a table.
• UPDATE:
  • Allows a user to modify existing data in a table.
• DELETE:
  • Allows a user to remove rows from a table.
• EXECUTE:
  • Allows a user to execute a stored procedure or function.
• REFERENCES:
  • Allows a user to create foreign key constraints that reference the table.
• TRIGGER:
  • Allows a user to create triggers on a table.

Granting Object Privileges:

Object privileges are typically granted by the owner of the object or a user with the necessary administrative rights on the object.

Example Syntax: 

-- Grant SELECT privilege on a table to a user
GRANT SELECT ON employees TO userA;
-- Grant INSERT and UPDATE privileges on a table to a user
GRANT INSERT, UPDATE ON employees TO userB;

Key Differences Between System Privileges and Object Privileges

• Scope of Privileges:
  • System Privileges: These are broader and affect the database system as a whole. They allow users to perform administrative tasks and manage the database environment.
  • Object Privileges: These are specific to individual database objects (tables, views, etc.) and control what operations users can perform on those objects.
• Usage:
  • System Privileges: Used for database administration, user management, and system configuration. They are essential for setting up and maintaining the database system.
  • Object Privileges: Used for managing access to and manipulation of data within specific objects. They control how users can interact with data.
• Granting and Revoking:
  • System Privileges: Typically granted by database administrators or system users with high-level access. Revoking these privileges usually impacts the user’s ability to perform certain administrative functions.
  • Object Privileges: Typically granted by the owner of the object or someone with administrative rights on the object. Revoking these privileges affects the user’s ability to perform operations on specific database objects.
• Granularity:
  • System Privileges: More coarse-grained, affecting broad aspects of database management.
  • Object Privileges: More fine-grained, targeting specific objects and their associated operations.

Examples to Illustrate Differences

Granting System Privilege: 

-- Grant the ability to create new users
GRANT CREATE USER TO userA;

 User userA can now create new database users.

Granting Object Privilege: 

-- Grant SELECT privilege on the 'employees' table
GRANT SELECT ON employees TO userB;

User userB can now read data from the employees table.

Revoking System Privilege: 

-- Revoke the ability to create new users
REVOKE CREATE USER FROM userA;

User userA can no longer create new database users.

Revoking Object Privilege: 

-- Revoke SELECT privilege on the 'employees' table
REVOKE SELECT ON employees FROM userB;

 User userB can no longer read data from the employees table.