iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 |

 

Lesson 13

“It is the mark of an educated mind to be able to entertain a thought without accepting it.” Aristotle (384 BC - 322 BC)

 

Read first then play the video:

   PLS-VIDEO -Granting Objects Privileges

  
Section 3: Dependency

Granting Object privileges

 

Hands-On introduction

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 that is going to be created by system/manager (dba).

 

Login “SQLPLUS” as "system/manager"

 

Create User Default Tablespace

Create a username "newuser" with password "newpass". And assign its default and temporary tablespaces.

SQL> CREATE USER newuser IDENTIFIED BY newpass

                  DEFAULT TABLESPACE users

                  TEMPORARY TABLESPACE temp;

 

GRANT RESOURCE …

Once you create an object in the Oracle database, it can be administered by either you or a user who has granted any privilege. The object privileges will allow users to manipulate the object by adding, changing, removing, or viewing data plus the ALTER, REFERENCES, and EXECUTE privileges in the database object.

 

On the other hand, System privileges control the altering, dropping, and creating of all database objects, such as rollback segments, synonyms, tables, and triggers.

 

Grant resource and connect roles to it. So, the newuser can login to the database and create its own objects.

SQL> GRANT resource, connect TO newuser;

Note that the CONNECT role contains the following system privileges:

 

ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, and CREATE VIEW.

 

The RESOURCE role contains the following system privileges:

CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CRREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, and CREATE TYPE.

 

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.

SQL> SELECT * FROM iself.customer;

 

Query the customers table as a public table.

SQL> SELECT * FROM customer;

No access.

 

Go to the third window.

Login to sqlplus as "iself/schooling.”

 

GRANT SELECT ON …

Grant select on the customers table to the newuser.

SQL> GRANT SELECT ON customer TO 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.

SQL> SELECT * FROM iself.customer;

No problem to query.

We know that the newuser has no objects.

 

Check to see the customers table is a public table?

 

CREATE PUBLIC SYNONYM …

Go to the "system" session; and create a public synonym for the iself table.

SQL> CREATE PUBLIC SYNONYM customer FOR iself.customer;

 

Go to the "Newuser" session; and query iself's customers table using the public synonym.

SQL> SELECT * FROM customer;

No problem this time.

 

GRANT EXECUTE ON …

Go back to the "iself" session; and grant execute privilege on the "dept_name" function to the "newuser".

SQL> GRANT EXECUTE ON dept_name TO newuser;

Notice that the iself user does not grant select privilege on the department table to the "newuser."

 

Grant the select privilege on the employee table to the "newuser".

SQL> GRANT SELECT ON emp TO newuser;

 

Go to the "system" session and create a public synonym for those two objects.

SQL> CREATE PUBLIC SYNONYM dept_name FOR iself.dept_name;

SQL> CREATE PUBLIC SYNONYM emp FOR iself.emp;

 

Go to the newuser session; and query against the department table.

SQL> SELECT * FROM dept;

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.

SQL> SELECT dept_name(10) as DEPARTMENT_NAME

                  FROM dual;

 

Use the "column" command to change the DEPARTMENT_NAME output format length to 20 characters.

SQL> COL department_name FORMAT a20

 

Use the “department name” function to query the department name against the employee table.

SQL> SELECT dept_name(deptno) as department_name

                  FROM customer

                  GROUP BY department_name;

Notice that although you did not join these two table but still you can get the department name.

 

REVOKE SELECT ON …

Go to the iself session; and revoke the select privilege on the employee table from the "newuser"

SQL> REVOKE SELECT ON emp FROM newuser;

 

Then go back to the newuser session; and query against the employee table.

SQL> SELECT * FROM emp;

No access.

 

Query the customer last names of the customers table.

SQL> SELECT last_name FROM customers;

You should have access to that table.

 

Then, try to delete the entire customers table.

SQL> DELETE FROM customers;

No delete privilege was granted.

 

“What's money? A man is a success if he gets up in the morning and goes to bed at night and in between does what he wants to do.” Bob Dylan (1941)

 

Questions:

Q: What is an Object Privilege?

Q: What are System Privileges?

Q: How do you create a user in the Oracle database?

Q: How do you assign a default and temporary tablespace to a user in the Oracle database?

Q: What are the System Privileges in the RESOURCE and CONNECT roles?

Q: How do you grant an object privilege to a user?

Q: How do you grant a system privilege to a user?

Q: What is the Public Synonym in the Oracle database?

Q: How do you create a PUBLIC SYNONYM?

Q: Why do you need a PUBLIC SYNONYM?

Q: What is the EXECUTE privilege? Is it a system privilege or an object privilege?

Q: Can you grant the EXECUTE privilege to a table?

Q: What is the Private Synonym in the Oracle database?

Q: What are the differences between a private synonym and public synonym?

Q: How do you revoke a system privilege from an Oracle user?

Q: How do you revoke an object privilege from an Oracle user?

Q: Mr. A granted to Mr. B an object privilege with a ‘WITH GRANT OPTION’ and then Mr. B granted the same privilege to Mr. C. You decide to revoke the Mr. B’s object privilege. What would be happen to Mr. C’s granted object privilege?

Q: Mr. A granted to Mr. B a system privilege with a ‘WITH ADMIN OPTION’ and then Mr. B granted the same privilege to Mr. C. You decide to revoke the Mr. B’s system privilege. What would be happen to Mr. C’s granted system privilege?

Q: How do you know that a privilege is the system privilege or object privilege?

Q: On the GRANT ALL statement, what ALL means if your grant is on a PL/SQL procedure?