0
4.2kviews
Short notes on Data Control Commands in SQL
1 Answer
0
61views
  • The Data Control Language (DCL) is a subset of the Structured Query Lanaguge (SQL) that allows database administrators to configure security access to relational databases.
  • DCL is the simplest of the SQL subsets, as it consists of only three commands:

    i. GRANTCommand- Give user access privileges to a database

    • Oracle operates a closed system in that you cannot perform any action at all unless you have been authorized to do so. This includes logging onto the database, creating tables, views, indexes and synonyms, manipulating data in tables created by other users, etc.
    • The SQL command to grant a privilege on a table: GRANT SELECT, INSERT, UPDATE, DELETE ON tablename TO username;
    • Example:

      GRANT SELECT ON employee TO cr7; GRANT SELECT, UPDATE, DELETE ON employee TO cr7;

    ii. REVOKE Command- withdraws access privileges given with the GRANT or taken with the DENY command

    • The SQL command to revoke a privilege on a table: REVOKE SELECT, INSERT, UPDATE, DELETE ON tablename FROM username;
    • Example:

      REVOKE SELECT ON employee FROM cr7;

      REVOKE SELECT, UPDATE, DELETE FROM cr7;

    iii. Deny Command- deny user access

    • The DENY command may be used to explicitly prevent a user from receiving a particular permission.
    • This is helpful when a user may be a member of a role or group that is granted a permission and you want to prevent that user from inheriting the permission by creating an exception.
    • The SQL Command for this:

      DENY [permission]

      ON [object]

      TO [user]

      Example:

      DENY DELETE

      ON HR.employees

  • Access is a four-part relationship -

  • Grantor- a user or admin who controls the privileges on a Schema Object
  • Privileges- the actions that can be done on a Schema Object
  • Schema Object- Table, View, Domain, Collation, stored procedure, trigger, etc.
  • Grantee- a user who is given Privileges on aSchema Object
Please log in to add an answer.