SQL - Privileges (Grant and Revoke) [DCL]

• Granting Privileges:

  GRANT SELECT ON karad.emp TO scott@localhost;
  

  • GRANT ALL PRIVILEGES ON karad.emp TO scott@localhost;
  • GRANT INSERT ON karad.emp TO scott@localhost, king@localhost;
  • GRANT SELECT, INSERT ON karad.emp TO scott@localhost, king@localhost;
  • GRANT SELECT ON karad.emp TO PUBLIC;

• Checking Privileges:

  SELECT * FROM information_schema.table_privileges;
  

• Revoking Privileges:

  REVOKE SELECT ON karad.emp FROM scott@localhost;
  

SQL - System Tables

• MySQL v9 has 78 system tables

• These are automatically created when MySQL is installed.

• System tables are stored in information_schema

  USE information_schema;
  SHOW TABLES;
  

• System tables store complete information about the database, such as:

  • Constraints info
  • Indexes info
  • Privileges info, etc.

• The set of 78 system tables is known as the Database Dictionary (also called the Database Catalog).

System Table Details

• Examples of system tables:
  • STATISTICS (for indexes)
  • TABLE_CONSTRAINTS
  • KEY_COLUMN_USAGE
  • TABLE_PRIVILEGES
• System tables are READ-ONLY
  • You can only SELECT from system tables.
  • You cannot INSERT, UPDATE, or DELETE.
• System tables are owned by mysql.sys
  • DDL for users is DML for system tables.

Types of Data in System Tables

1. User Data
   • Created by users (e.g., user tables and indexes).