|
|
Topics: Hands-On 07 – Granting Object Privileges In this Hands-On, you will open three sessions. You login as iself/schooling in the first session, login as system/manager in the second session, and login to the third session as a newuser/newpass which is going to be created by system/manager (dba). You will: 1- Copy, alter, and drop a table. 2- Create a table. 3- Create, alter, and drop a view. 4- Create a private synonym. 5- Create a public synonym. 6- Create, alter and drop a user. 7- Grant object and system privileges. 8- Revoke object and system privileges.
Manuscript Login “SQLPLUS” as "system/manager" = Create a username "newuser" with password "newpass". And assign its default and temporary tablespaces. = Grant resource and connect roles to it. So, the newuser can login to the database and create its own objects. === Go to the “NEXT” window. = Login to “sqlplus” as "newuser" password “newpass” = Query the customers information under the iself schema. Notice that no access to the table was granted to the user, yet. = Query the customers table as a public table. No access. = Go to the third window. Login to sqlplus as "iself/schooling.” = Grant select on the customers table to the newuser. The newuser should not be able to update, insert or delete a record. === Go to the "newuser" session; and query iself's customers table. No problem to query. = We know that the newuser has no objects. Check to see the customers table is a public table?
Go to the "system" session; and create a public synonym for the iself table. === Go to the "Newuser" session; and query iself's customers table using the public synonym. No problem this time. === Go back to the "iself" session; and grant execute privilege on the "dept_name" function to the "newuser". = Notice that the iself use does not grant select privilege on the department table to the "newuser."
= Grant the select privilege on the employee table to the "newuser". = Go to the "system" session and create a public synonym for those two objects. === For the function's synonym, make a syntax error, just to see the error message. Then, read the error message and use the "command line editor" to correct the error. (Do not get frustrated when you get an error, instead try to solve it patiently.) = Go to the newuser session; and query against the department table. Notice that the access privilege to the department table was not granted to the "newuser." = Use the “department name” function to query the department name for the department number 10. = Use the "column" command to change the output format length to 20 characters. = Use the “department name” function to query the department name against the employee table. Notice that although you did not join these two table but still you can get the department name. = Go to the iself session; and revoke the select privilege on the employee table from the "newuser" = Then go back to the newuser session; and query against the employee table. no access. Query the customer last names of the customers table. Then, try to delete the entire customers table. No delete privilege was granted. = Now, you should practice this over and over, until you become a master at it. Good Luck. |
|