CA 33 - Users, Roles, Groups

 Task 1: Create a login role report_user that can only read from the film table.

            Step 1 :-  We should first create a role named report_user with Login access along side a password


So now a Role 'report_User' has been created with login access and password as 'report_user123'

        Step 2 :- The role should be now able to access the database 'dvdrental' inorder to perform select query in film table 


        Step3 :- Now the role should be only able to perform select operation on the film table this we only grant the select access to this role



Task 2: Now try to access customer table. When report_user tries to query customer and gets permission denied. Fix it.

    In order to access the customer table first we will login as the report_user.

To avoid this problem also mention the db in which you can connect.


    Now that we are connected let us try to connect to perform a simple select operation on table customer. 


    As the report_user as only the access to the Select query in film table the access is denied.Now we will also Grant the permission to perform select operation in Customer table too.


Now we switch to default user to grant permission for the report_user and then switch back to the report_user and try to perform the same operation


    As we can see now the permission is not denied.


Task 3: Allow report_user to see only customer_id, first_name, last_name of the customer table.

    For this to be done first we will revoke the previous select access to the report_user role.


    Now as a default user i have revoked the access to the report_user role.

    Now assign a new access to the role with only the specific column 


    Now the access for only for the specific columns has been given to the role




Task 4: Create support_user who can, SELECT from customer, UPDATE only email column, Cannot DELETE


    The specific required access and denial of access has been given to the role support_user.


    The access for the given conditions are done


    But if we try to update any other column permission will be denied.


Task 5: Remove SELECT access on film from report_user.

    We will switch to the default user to revoke the Select access for the report_user role for the film table


    Now the SELECt access has been revoked on the table film for the role report_user. Let's cross check it by logging in as the role and perfrom the action.


    Permission is denied. Thus revoking of the access is successful. 


Task 6: Create readonly_group that has SELECT on all tables.

    Here we will create a role but not give a login and passowrd to it. Also as this group has access to read all tables a new clause in the Grant query is used. 

    IN SCHEMA public - All TABLES keyword is generic and does not point to any specific table like we previously do in "ON TABLE film", thus this clause is used. this "IN SCHEMA" tells the postgres in whcih schema to look into to get the list of tables as database can have multiple schemas 

    In between the two Create and grant query first we should allow the group to connect with the db 





Task 7: Create analyst1 and analyst2 and add them to readonly_group.

    Now we will create two role (user i.e role with login) as analyst1 and analyst2.


Now we have to add this to users to that group. Which is nothing but granting the access of the group 'readonly_group' ( role) to the other to users. 



Comments

Post a Comment

Popular posts from this blog

CA 04 - Two Sum & Sorted Two Sum

Image
  MY CODE: In brute force method the intuitive way of solving this problem with two for loop. Using nested loop to traverse through the list and try different combination of two sum to check if it matches with the target.    Time complexity: O(n^2)       OPTIMIZED:          In the optimized method of approaching the Two Sum problem. The given list is already sorted in ascending order. This gives us the leverage of using Two pointer technique to solve this problem. Thus using single loop to approach this problem. If current sum is less than target move the left pointer, if it is greater move the right pointer else return the current index (equal to target).  Time complexity: O(n) MY CODE:
Read more

CA 05 - Reverse the array

  Array Reverse Reverse an array  arr[] . Reversing an array means  rearranging  the elements such that the  first  element becomes the  last , the  second  element becomes  second last  and so on. Input: arr[] = [1, 4, 3, 2, 6, 5] Output: [5, 6, 2, 3, 4, 1] Explanation: The first element 1 moves to last position, the second element 4 moves to second-last and so on. Input: arr[] = [4, 5, 1, 2] Output: [2, 1, 5, 4] Explanation: The first element 4 moves to last position, the second element 5 moves to second last and so on. CODE:     BRUTE FORCE METHOD:          Here, in this method we will create a temporary array to store the reversed given array and then assign it to the given array class Solution :     def reverseArray ( arr ):         n = len (arr)         temp = [ 0 ] * n         for i in range (n- 1 ,- 1 ,- 1 ): ...
Read more

CA 21 - Basic Select SQL Queries

Image
 1.  Query all columns for a city in   CITY   with the   ID   1661 . The  CITY  table is described as follows: Here we make use of the where conditon cllause to retrieve the details of the Id with 1661, we select all the columns to be displayed and "*" is avoided for best practice. SOLUTION: SELECT ID,NAME,COUNTRYCODE,DISTRICT,POPULATION FROM CITY where ID = 1661 ; 2. Query all columns for all American cities in the   CITY   table with populations larger than   100000 . The   CountryCode   for America is   USA . The  CITY  table is described as follows: Here we make use of the where condtional clause, to obatin the population to be over thhe given number we make use of the' >' symbol to achieve this. Also the AND operator is used to state two where conditons where we want only the details of cities in USA so we use the countrycode check with 'USA' single quotes are used to check with the value, usage ...
Read more