Types of Privileges in dbms

sem3 | 0 comments

Types of Privileges in dbms

Types of privileges in dbms are explained below :

The account level:

  • At this level, the DBA specifies the particular privileges that each account holds independently of the relations in the database.

The relation level (or table level):

  • At this level, the DBA can control the privilege to access each individual relation or view in the database.
  • capabilities provided to the account itself and can include
  • the CREATE SCHEMA or CREATE TABLE privilege, to create a schema or base relation;
  • the CREATE VIEW privilege;
  • the ALTER privilege, to apply schema changes such adding or removing attributes from relations;
  • the DROP privilege, to delete relations or views
  • the MODIFY privilege, to insert, delete, or update tuples;
  • and the SELECT privilege, to retrieve information from the database by using a SELECT query.
  • The second level of privileges applies to the relation level
  • This includes base relations and virtual (view) relations.
  • The granting and revoking of privileges generally follow an authorization model for discretionary privileges known as the access matrix model where
  • The rows of a matrix M represents subjects (users, accounts, programs)
  • The columns represent objects (relations, records, columns, views, operations).
  • Each position M(i,j) in the matrix represents the types of privileges (read, write, update) that subject i holds on object j.
  • To control the granting and revoking of relation privileges, each relation R in a database is assigned and owner account, which is typically the account that was used when the relation was created in the first place.
  • The owner of a relation is given all privileges on that relation.
  • In SQL2, the DBA can assign and owner to a whole schema by creating the schema and associating the appropriate authorization identifier with that schema, using the CREATE SCHEMA command.
  • The owner account holder can pass privileges on any of the owned relation to other users by granting privileges to their accounts.

In SQL the following types of privileges can be granted on each individual relation R:

SELECT (retrieval or read) privilege on R:

  • Gives the account retrieval privilege.
  • In SQL this gives the account the privilege to use the SELECT statement to retrieve tuples from R.

MODIFY privileges on R:

  • This gives the account the capability to modify tuples of R.
  • In SQL this privilege is further divided into UPDATE, DELETE, and INSERT privileges to apply the corresponding SQL command to R.
  • In addition, both the INSERT and UPDATE privileges can specify that only certain attributes can be updated by the account.

In SQL the following types of privileges can be granted on each individual relation R (contd.):

REFERENCES privilege on R:

  • This gives the account the capability to reference relation R when specifying integrity constraints.
  • The privilege can also be restricted to specific attributes of R.
  • Notice that to create a view, the account must have SELECT privilege on all relations involved in the view definition.

 

Submit a Comment