1 / 9

Managing User Access Through Grants, Views, and Roles

Managing User Access Through Grants, Views, and Roles. Spring 2013. Access Control View Example. CREATE OR REPLACE VIEW AZ_CUSTOMER AS SELECT * FROM CUSTOMER WHERE State = ‘AZ’ WITH CHECK OPTION; update jnm2.az_customer set l_name = ‘Green’ where Cust_No = 10;.

livvy
Download Presentation

Managing User Access Through Grants, Views, and Roles

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Managing User AccessThrough Grants, Views, and Roles Spring 2013

  2. Access Control View Example CREATE OR REPLACE VIEW AZ_CUSTOMER AS SELECT * FROM CUSTOMER WHERE State = ‘AZ’ WITH CHECK OPTION; update jnm2.az_customer set l_name = ‘Green’ where Cust_No = 10; Works if user has UPDATE privilege on the VIEW and if the Cust_no identified here lives in Arizona

  3. GRANTing Rights to Access Control Views • By default only the creator of a view (or a table) has rights to access it. • Rights are given to others thru the GRANT statement: GRANT action ON object TO user GRANT SELECT ON AZ_Customer TO jg7; • Granting a user access to the view and not the table it comes from lets them see and manipulate only the parts of the table for which they need to have access.

  4. GRANTs and REVOKEs • Rights can also be granted to tables: GRANT INSERT ON CUSTOMER TO jg7; • Rights to a table or view are revoked through the REVOKE statement REVOKE INSERT ON CUSTOMER FROM jg7;

  5. ROLE BASED SECURITY • Most multi-user database use role-based security • A user’s needs to access database objects are determined by her/his ROLE in the organization, so: • GRANT rights to database objects to ROLEs • Then GRANT these ROLEs to users who occupy these roles.

  6. ROLE BASED SECURITYStatement Syntax CREATE ROLE role_name; CREATE ROLE Sales_Clerk; • Then use the GRANT statement covered on a previous slide • Rights can be GRANTed to a ROLE & • ROLEs can be GRANTed to Users. E. G. GRANT SELECT ON AZ_Customer TO Sales_Clerk; & GRANT Sales_Clerk TO jg7;

  7. Role Based Security – Why? • There may be many users performing the same role • e.g. 25 sales clerks • A given role may require access to many database objects • E.g. sales clerks need access to 20 different tables and views • Using ROLEs simplifies assignment of rights

  8. GRANTING RIGHTS WITH AND WITHOUT ROLES • Based upon our sales clerk example: • With ROLES • 1 CREATE ROLE Statement • 20 GRANT statements (1 for each object) • 25 statements GRANTing the ROLE to users • Without Roles • 20 GRANT statements for each of the 25 users • 20*25 or 500 GRANT statements required

  9. REVOKING RIGHTS WITH AND WITHOUT ROLES • If one of the sales clerks moves on to a different job (ROLE) • With ROLEs • 1 REVOKE statement to revoke the Sales_Clerk ROLE from the user • Without ROLEs • 20 revoke statements • 1 for each object to which the user had been granted rights

More Related