Understanding User Permissions in Database Management Systems
Database Management Systems (DBMS) offer security measures to control user accesses and permissions. Users can be assigned specific access rights and commands. This guide explains concepts like creating users, granting privileges, managing roles, and using GRANT commands effectively in DBMS.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Databases and security CMSC 461 Michael Wilson
DBMS user security DBMSes typically have ways of restricting accesses to specific resources Can give certain database users specific access rights Can limit which commands database users can execute
CREATE USER SQL command that allows the creation of a new user Lots of options Syntax: CREATE USER <name> WITH <options> Some options: PASSWORD password IN GROUP <group_name> LOGIN/NOLOGIN Separated by spaces
Users vs. Roles You ll oftentimes hear users referred to as roles in DBMS documentation and DBMS commands PostgresSQL only has one concept: roles A role can be a user or a group Only difference is how you treat the role conceptually No real different implementation-wise
The GRANT command After creating a user, you can control the specifics of what the user has access to You can control this using GRANT Syntax: GRANT <privilege> ON <table(s)> TO <role>;
The privileges section Can specify multiple privileges, separated by commas SELECT, INSERT, UPDATE, DELETE If you only wanted a user to have read access to a table SELECT If you wanted a user to be able to create triggers TRIGGER
The tables section Can give the user the previously specified on one or more tables table1, table2, table3
The role section Can supply the role/user you want to grant the privileges to, or the word PUBLIC PUBLIC means all roles Even if they are created later
WITH GRANT OPTION Allows users to grant the privilege granted to them to other users Kind of similar to SSL signing, if you think about it
PostgreSQL grant documentation http://www.postgresql.org/docs/9.3/static /sql-grant.html
Schemas/databases You may see the term schema thrown around in the documentation In this instance, schema and database are used interchangeably Tables are grouped into databases, multiple of which can be houses in a DBMS You can limit a user s access specifically to one database, or specific tables within a database
The REVOKE command The REVOKE command has similar syntax to the GRANT command, but no WITH GRANT OPTION Has a CASCADE/RESTRICT option that can be placed at the end instead CASCADE Users who have been granted permission by the user whose permissions are being revoked also have those permissions revoked RESTRICT Just the user you re revoking permissions from
