Books > Les16_rev2
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Books > Les16_rev2 as PDF for free.
More details
- Words: 765
- Pages: 26
16 Managing Privileges
Copyright © Oracle Corporation, 2002. All rights reserved.
Objectives After completing this lesson, you should be able to do the following: • Identify system and object privileges • Grant and revoke privileges • Obtain privilege information
16-2
Copyright © Oracle Corporation, 2002. All rights reserved.
Managing Privileges There are two types of Oracle user privileges: • System: Enables users to perform particular actions in the database • Object: Enables users to access and manipulate a specific object
16-3
Copyright © Oracle Corporation, 2002. All rights reserved.
System Privileges • More than 100 distinct system privileges • ANY keyword in privileges signifies that users have the privilege in any schema. • GRANT command adds a privilege to a user or a group of users. • REVOKE command deletes the privileges.
16-4
Copyright © Oracle Corporation, 2002. All rights reserved.
System Privileges: Examples
16-5
Category
Examples
INDEX
CREATE ANY INDEX ALTER ANY INDEX DROP ANY INDEX
TABLE
CREATE TABLE CREATE ANY TABLE ALTER ANY TABLE DROP ANY TABLE SELECT ANY TABLE UPDATE ANY TABLE DELETE ANY TABLE
SESSION
CREATE SESSION ALTER SESSION RESTRICTED SESSION
TABLESPACE
CREATE TABLESPACE ALTER TABLESPACE DROP TABLESPACE UNLIMITED TABLESPACE
Copyright © Oracle Corporation, 2002. All rights reserved.
Granting System Privileges • Use the GRANT command to grant system privileges. • The grantee can further grant the system privilege with the ADMIN option. GRANT CREATE SESSION TO emi; GRANT CREATE SESSION TO emi WITH ADMIN OPTION;
16-6
Copyright © Oracle Corporation, 2002. All rights reserved.
16-7
Copyright © Oracle Corporation, 2002. All rights reserved.
16-8
Copyright © Oracle Corporation, 2002. All rights reserved.
SYSDBA and SYSOPER Privileges
16-9
Category
Examples
SYSOPER
STARTUP SHUTDOWN ALTER DATABASE OPEN | MOUNT ALTER DATABASE BACKUP CONTROLFILE TO RECOVER DATABASE ALTER DATABASE ARCHIVELOG RESTRICTED SESSION
SYSDBA
SYSOPER PRIVILEGES WITH ADMIN OPTION CREATE DATABASE ALTER TABLESPACE BEGIN/END BACKUP RESTRICTED SESSION RECOVER DATABASE UNTIL Copyright © Oracle Corporation, 2002. All rights reserved.
System Privilege Restrictions The O7_DICTIONARY_ACCESSIBILITY parameter: • Controls restrictions on SYSTEM privileges • If set to TRUE, allows access to objects in SYS schema • The default is FALSE: ensures that system privileges that allow access to any schema do not allow access to SYS schema
16-10
Copyright © Oracle Corporation, 2002. All rights reserved.
Revoking System Privileges • Use the REVOKE command to remove a system privilege from a user. • Users with ADMIN OPTION for system privilege can revoke system privileges. • Only privileges granted with a GRANT command can be revoked. REVOKE CREATE TABLE FROM emi;
16-11
Copyright © Oracle Corporation, 2002. All rights reserved.
16-12
Copyright © Oracle Corporation, 2002. All rights reserved.
Revoking System Privileges with the ADMIN OPTION DBA
Jeff
Emi
DBA
Jeff
Emi
GRANT
REVOKE
16-13
Copyright © Oracle Corporation, 2002. All rights reserved.
Object Privileges
Object priv.
Table View
ALTER
√ √ √ √
EXECUTE √ √ √ √
INSERT REFERENCES
√
SELECT
√ √√
UPDATE
16-14
Procedure
√ √
DELETE
INDEX
Sequence
√ √
Copyright © Oracle Corporation, 2002. All rights reserved.
Granting Object Privileges • Use the GRANT command to grant object privileges. • Grant must be in grantor’s schema or grantor must have GRANT OPTION. GRANT EXECUTE ON dbms_output TO jeff; GRANT UPDATE ON emi.customers TO jeff WITH GRANT OPTION;
16-15
Copyright © Oracle Corporation, 2002. All rights reserved.
16-16
Copyright © Oracle Corporation, 2002. All rights reserved.
16-17
Copyright © Oracle Corporation, 2002. All rights reserved.
Revoking Object Privileges • Use the REVOKE command to revoke object privileges. • User revoking the privilege must be the original grantor of the object privilege being revoked. REVOKE SELECT ON emi.orders FROM jeff;
16-18
Copyright © Oracle Corporation, 2002. All rights reserved.
16-19
Copyright © Oracle Corporation, 2002. All rights reserved.
16-20
Copyright © Oracle Corporation, 2002. All rights reserved.
Revoking Object Privileges with GRANT OPTION Bob
Jeff
Emi
Bob
Jeff
Emi
GRANT
REVOKE
16-21
Copyright © Oracle Corporation, 2002. All rights reserved.
Obtaining Privileges Information Information about privileges can be obtained by querying the following views: • DBA_SYS_PRIVS • SESSION_PRIVS • DBA_TAB_PRIVS • DBA_COL_PRIVS
16-22
Copyright © Oracle Corporation, 2002. All rights reserved.
Summary In this lesson, you should have learned how to: • Identify system and object privileges • Grant and revoke privileges • Obtain privilege information
16-23
Copyright © Oracle Corporation, 2002. All rights reserved.
Practice 16 Overview This practice covers the following topics: • Creating user and granting system privileges • Granting object privileges to users
16-24
Copyright © Oracle Corporation, 2002. All rights reserved.
16-25
Copyright © Oracle Corporation, 2002. All rights reserved.
16-26
Copyright © Oracle Corporation, 2002. All rights reserved.
Copyright © Oracle Corporation, 2002. All rights reserved.
Objectives After completing this lesson, you should be able to do the following: • Identify system and object privileges • Grant and revoke privileges • Obtain privilege information
16-2
Copyright © Oracle Corporation, 2002. All rights reserved.
Managing Privileges There are two types of Oracle user privileges: • System: Enables users to perform particular actions in the database • Object: Enables users to access and manipulate a specific object
16-3
Copyright © Oracle Corporation, 2002. All rights reserved.
System Privileges • More than 100 distinct system privileges • ANY keyword in privileges signifies that users have the privilege in any schema. • GRANT command adds a privilege to a user or a group of users. • REVOKE command deletes the privileges.
16-4
Copyright © Oracle Corporation, 2002. All rights reserved.
System Privileges: Examples
16-5
Category
Examples
INDEX
CREATE ANY INDEX ALTER ANY INDEX DROP ANY INDEX
TABLE
CREATE TABLE CREATE ANY TABLE ALTER ANY TABLE DROP ANY TABLE SELECT ANY TABLE UPDATE ANY TABLE DELETE ANY TABLE
SESSION
CREATE SESSION ALTER SESSION RESTRICTED SESSION
TABLESPACE
CREATE TABLESPACE ALTER TABLESPACE DROP TABLESPACE UNLIMITED TABLESPACE
Copyright © Oracle Corporation, 2002. All rights reserved.
Granting System Privileges • Use the GRANT command to grant system privileges. • The grantee can further grant the system privilege with the ADMIN option. GRANT CREATE SESSION TO emi; GRANT CREATE SESSION TO emi WITH ADMIN OPTION;
16-6
Copyright © Oracle Corporation, 2002. All rights reserved.
16-7
Copyright © Oracle Corporation, 2002. All rights reserved.
16-8
Copyright © Oracle Corporation, 2002. All rights reserved.
SYSDBA and SYSOPER Privileges
16-9
Category
Examples
SYSOPER
STARTUP SHUTDOWN ALTER DATABASE OPEN | MOUNT ALTER DATABASE BACKUP CONTROLFILE TO RECOVER DATABASE ALTER DATABASE ARCHIVELOG RESTRICTED SESSION
SYSDBA
SYSOPER PRIVILEGES WITH ADMIN OPTION CREATE DATABASE ALTER TABLESPACE BEGIN/END BACKUP RESTRICTED SESSION RECOVER DATABASE UNTIL Copyright © Oracle Corporation, 2002. All rights reserved.
System Privilege Restrictions The O7_DICTIONARY_ACCESSIBILITY parameter: • Controls restrictions on SYSTEM privileges • If set to TRUE, allows access to objects in SYS schema • The default is FALSE: ensures that system privileges that allow access to any schema do not allow access to SYS schema
16-10
Copyright © Oracle Corporation, 2002. All rights reserved.
Revoking System Privileges • Use the REVOKE command to remove a system privilege from a user. • Users with ADMIN OPTION for system privilege can revoke system privileges. • Only privileges granted with a GRANT command can be revoked. REVOKE CREATE TABLE FROM emi;
16-11
Copyright © Oracle Corporation, 2002. All rights reserved.
16-12
Copyright © Oracle Corporation, 2002. All rights reserved.
Revoking System Privileges with the ADMIN OPTION DBA
Jeff
Emi
DBA
Jeff
Emi
GRANT
REVOKE
16-13
Copyright © Oracle Corporation, 2002. All rights reserved.
Object Privileges
Object priv.
Table View
ALTER
√ √ √ √
EXECUTE √ √ √ √
INSERT REFERENCES
√
SELECT
√ √√
UPDATE
16-14
Procedure
√ √
DELETE
INDEX
Sequence
√ √
Copyright © Oracle Corporation, 2002. All rights reserved.
Granting Object Privileges • Use the GRANT command to grant object privileges. • Grant must be in grantor’s schema or grantor must have GRANT OPTION. GRANT EXECUTE ON dbms_output TO jeff; GRANT UPDATE ON emi.customers TO jeff WITH GRANT OPTION;
16-15
Copyright © Oracle Corporation, 2002. All rights reserved.
16-16
Copyright © Oracle Corporation, 2002. All rights reserved.
16-17
Copyright © Oracle Corporation, 2002. All rights reserved.
Revoking Object Privileges • Use the REVOKE command to revoke object privileges. • User revoking the privilege must be the original grantor of the object privilege being revoked. REVOKE SELECT ON emi.orders FROM jeff;
16-18
Copyright © Oracle Corporation, 2002. All rights reserved.
16-19
Copyright © Oracle Corporation, 2002. All rights reserved.
16-20
Copyright © Oracle Corporation, 2002. All rights reserved.
Revoking Object Privileges with GRANT OPTION Bob
Jeff
Emi
Bob
Jeff
Emi
GRANT
REVOKE
16-21
Copyright © Oracle Corporation, 2002. All rights reserved.
Obtaining Privileges Information Information about privileges can be obtained by querying the following views: • DBA_SYS_PRIVS • SESSION_PRIVS • DBA_TAB_PRIVS • DBA_COL_PRIVS
16-22
Copyright © Oracle Corporation, 2002. All rights reserved.
Summary In this lesson, you should have learned how to: • Identify system and object privileges • Grant and revoke privileges • Obtain privilege information
16-23
Copyright © Oracle Corporation, 2002. All rights reserved.
Practice 16 Overview This practice covers the following topics: • Creating user and granting system privileges • Granting object privileges to users
16-24
Copyright © Oracle Corporation, 2002. All rights reserved.
16-25
Copyright © Oracle Corporation, 2002. All rights reserved.
16-26
Copyright © Oracle Corporation, 2002. All rights reserved.
Related Documents
Books > Les06_rev2
November 2019 0
Books > Les16_rev2
November 2019 1
Books > Les04_rev2
November 2019 1
Books > Les10_rev2
November 2019 1
Books > Les11_rev2
November 2019 1