11-Managing Privileges

Systemprivileges role systemprivileges role to

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: | role} [, {system_privileges | role} ] … TO {user | role | PUBLIC} [, {user | role | PUBLIC} ] … [WITH ADMIN OPTION] Where, System_privileges specifies the system privileges to be granted Role specifies the role to be granted PUBLIC grants privileges to all users WITH ADMIN OPTION enables the grantee to further grant the privileges or role to other users or roles Managing Privileges Tathagata Bhattacharjee Example: SQL> GRANT CREATE SESSION TO scott; SQL> GRANT CREATE SESSION TO scott WITH ADMIN OPTION; Managing Privileges Tathagata Bhattacharjee SYSDBA Privileges SYSOPER PRIVILEGES WITH ADMIN OPTION CREATE DATABASE ALTER DATABASE BEGIN/END BACKUP RESTRICT SESSION RECOVER DATABASE UNTIL Managing Privileges Tathagata Bhattacharjee SYSOPER Privileges STARTUP SHUTDOWN ALTER DATABASE OPEN | MOUNT ALTER DATABASE BACKUP CONTROLFILE TP RECOVER DATABASE ALTER DATABASE ARCHIVELOG Managing Privileges Tathagata Bhattacharjee Revoking System Privileges REVOKE {system_privileges | role} [, {system_privileges | role} ] … FROM {user | role | PUBLIC} [, {user | role | PUBLIC} ] … Managing Privileges Tathagata Bhattacharjee • • The REVOKE command can only revoke privileges that have been granted directly with a GRANT command Revoking system privileges may have an effect on some dependent objects. E.g., if SELECT ANY TABLE is granted to a user, and that user created any procedure or views that use a table in some other schema, revoking the privilege invalidates the procedures or views Managing Privileges Tathagata Bhattacharjee Example SQL> REVOKE CREATE TABLE FROM scott; Managing Privileges Tathagata Bhattacharjee Revoking System Privileges WITH ADMIN OPTION GRANT DBA scott henry REVOKE DBA scott henry CONTINUED… Managing Privileges Tathagata Bhattacharjee Scenario The DBA grants the CREATE TABLE system privilege to scott with the WITH DMIN OPTION scott created a table scott grants the CREATE TABLE system privilege to henry henry creates a table The DBA revokes the CREATE TABLE system privilege from scott Managing Privileges Tathagata Bhattacharjee Result Scott’s table still exists, but no new tables can be created Henry’s table still exists and he still has the CREATE TABLE system privilege Managing Privileges Tathagata Bhattacharjee Object Privilege An object privilege is a privilege or right to perform a particular action on a specific table, view, sequence, procedure, function or package Each object object has a particular set of g...
View Full Document

This note was uploaded on 07/15/2011 for the course ECO 2023 taught by Professor Mr.raza during the Summer '10 term at FAU.

Ask a homework question - tutors are online