Managing Rights



For multiuser systems like DBMSs it is necessary to grant and revoke rights for manipulating its objects. The GRANT command defines which user can manipulate (create, read, change, drop, ...) which object (tables, views, indexes, sequences, triggers, ...).

GRANT <privilege_name>
ON    <object_name>
TO    [ <user_name> | <role_name> | PUBLIC ]
[WITH GRANT OPTION];

The REVOKE statement deprives the granted rights.

REVOKE <privilege_name>
ON     <object_name>
FROM   [ <user_name> | <role_name> | PUBLIC ];

The example statement grants SELECT and INSERT on table person to the user hibernate. The second statement removes the granted rights.

GRANT  SELECT, INSERT ON person TO   hibernate;
REVOKE SELECT, INSERT ON person FROM hibernate;

Privileges

Privileges are actions which users can perform. The SQL standard supports only a limited list of privileges whereas real implementations offer a great bunch of different privileges. The list consists of: SELECT, INSERT, UPDATE, DELETE, CREATE <object_type>, DROP <object_type>, EXECUTE, ... .

Object Types

The list of object types to which privileges may be granted is short in the SQL standard and long for real implementations. It consists of tables, views, indexes, sequences, triggers, procedures, ... .

Roles / Public

If there is a great number of users connecting to the DBMS, it is helpful to group users with identical rights to a role and grant privileges not to the individuell users but to the role. To do so, the role must be created by a CREATE ROLE statement. Afterwards users are joined with this role.

-- Create a role
-- (MySQL supports only predefinded roles with special semantics).
CREATE ROLE department_human_resouces;

-- Enrich the role with rights
GRANT  SELECT, INSERT, UPDATE, DELETE ON person        TO department_human_resouces;
GRANT  SELECT, INSERT                 ON hobby         TO department_human_resouces;
GRANT  SELECT, INSERT, UPDATE, DELETE ON person_hobby  TO department_human_resouces;

-- Join users with the role
GRANT  department_human_resouces TO user_1;
GRANT  department_human_resouces TO user_2;

Instead of individuell usernames the key word PUBLIC denotes all known users.

-- Everybody shall be allowed to read the rows of 'person' table.
GRANT SELECT ON person TO PUBLIC;

Grant Option

If a DBA wants to delegate the managing of rights to special users, he can grant privileges to them and extend the statement with the phrase 'WITH GRANT OPTION'. This enables the users to grant the received privileges to any other user.

-- User 'hibernate' gets the right to pass the SELECT privilege on table 'person' to any other user.
GRANT SELECT ON person TO hibernate WITH GRANT OPTION;



  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.

Structured_Query_Language/Managing_Rights
 



 

Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.


Manage research, learning and skills at defaultLogic. Create an account using LinkedIn or facebook to manage and organize your Digital Marketing and Technology knowledge. defaultLogic works like a shopping cart for information -- helping you to save, discuss and share.


  Contact Us