-----Lession16 Controlling User Access-----
* Database security can be classified into two categories: system security and data security. System security covers access and use of the database at the system level, such as username and password, disk space allocated to users, and system operations allowed by the user. atabase security covers access and use of the database objects and the actions that those users can have on the objects.
* Typical User Privileges
System Privilege Operations Authorized
---------------- ---------------------
CREATE SESSION Connect to the database.
CREATE TABLE Create tables in the user schema.
CREATE SEQUENCE Create a sequence in the user schema.
CREATE SYNONYM Create a synonym in the user schema.
CREATE VIEW Create a view in the user schema.
CREATE PROCEDURE Create a stored procedure, function, or package in the user schema.
* A role is a named group of related privileges that can be granted to the user. This method makes granting and revoking privileges easier to perform and maintain.
* A user can have access to several roles, and several users can be assigned the same role. Roles typically are created for a database application.
* Object Privileges
-------------------------------------------------------------------------------------------
Object
Privilege Directory DB_Object Library Operator Type Table View Sequence Procedure Snapshot
-------------------------------------------------------------------------------------------
ALTER T T
DELETE T T
EXECUTE T T T T T T
INDEX T
INSERT T T
READ T
REFERENCES T
SELECT T T T
UPDATE T T
-------------------------------------------------------------------------------------------
* The REFERENCES object privilege can only be granted to a user and not to a role.
* If a statement does not use the full name of an object, then the Oracle7 Server implicitly prefixes the object name with the current user’s name (or schema). And if the current user does not own an object of that name, then the system will prefix the object name with PUBLIC.
* A user must own the table or have been granted the SELECT privilege on the table WITH GRANT OPTION to be able to give everyone access to it using the PUBLIC keyword.
* A privilege that is granted WITH GRANT OPTION can be passed on to other users and roles by the grantee. Object privileges granted WITH GRANT OPTION are revoked when the grantor’s privilege is revoked.
* A system privileges give the grantee the ability to perform system-level activities, such as :
Connecting to the database
Altering the user session
Creating Tables
Creating Users
* 系统权限和对象权限的一个语法上的区别是，当传递授权时，system privilege use the : WITH ADMIN OPTION and the object privilege use the WITH GRANT OPTION, the funcionality is identical, but the syntax is different.
* When a user is created, if they have been granted the privilege to connect, they have the ability to alter their own password without having been explicitly granted the ALTER USER privilege. The CREATE SESSION privilege allows a user to connect. Users must be explicitly granted the privileges that allow them to create tables. When a user creates a table, they automatically have the ability to select data from the table and to grant privileges on the table.
* Data Dictionary Table Description
----------------------------------------------------------------------
ROLE_SYS_PRIVS System privileges granted to roles.
ROLE_TAB_PRIVS Table privileges granted to roles.
USER_ROLE_PRIVS Roles accessible by the user.
USER_TAB_PRIVS_MADE Object privileges granted on the user’s objects.
USER_TAB_PRIVS_RECD Object privileges granted to the user.
USER_COL_PRIVS_MADE Object privileges granted on the columns of the user’s objects.
USER_COL_PRIVS_RECD Object privileges granted to the user on specific columns.
* The ALL keyword grants the grantee all of the object privileges that have been granted to the grantor for a particular object. Using the ALL keyword in this GRANT command gives the user the ALTER, INDEX, INSERT, REFERENCES, SELECT, UPDATE, and DELETE privileges on the table.
* If a user is granted a privilege WITH GRANT OPTION, then that user can also grant the privilege WITH GRANT OPTION, so that a long chain of grantees is possible, but no circular grants are permitted. If the owner revokes a privilege from a user who granted the privilege to other users, then the REVOKE cascades to all privileges granted.
* An object need not currently exist and you need not have privileges to access the object when creating the synonym.
* The DICTIONARY data dictionary view provides descriptions of the data dictionary tables and views that are accessible to the user.
* USER_ Objects that user owned, Privileges granted to or from user schema.
ALL_ Objects and privileges that user accessed (had privileges on)
DBA_ Objects and privileges in the Database
? A procedure refers to standalone procedures and functions, and public package constructs. The INDEX and REFERENCES privileges cannot be granted to a role.
? Revoke [CASCADE CONSTRAINTS]: are required to remove any referential integrity constraints made to the object by means of the REFERENCES privilege.
Lession17 Summary of SQL and SQL*Plus
* The following are some frequently accessed data dictionary tables:
DICTIONARY
USER_OBJECTS
OBJ (alias for USER_OBJECTS)
USER_CONSTRAINTS
USER_SEQUENCES
-----------------------------------------------
Lession18 Overview of PL/SQL
* SQL is non-procedural and a 4GL, PL/SQL (Procedural Language/SQL) is an extension to SQL.
* PL/SQL groups SQL statements together within a single block and sends the entire block to the server in a single call, therefore reducing network traffic.
* PL/SQL program constructs
Anonymous Block
Stored Procedure or Function
Application Procedure or Function
Package
Database Trigger
Application Trigger
* Procedure Builder’s development environment contains a built-in editor for you to create or edit subprograms. You can compile, test, and debug your code.
-----------------------------------------------
Lession19 Basic of Procedure Builder
* 在Procedure Builder中建立Procedure和Function的
Lession20 Modularizing Programing with subprograms
* Subprograms are composed of a number of sections:
A header to name and type the block
An optional declarative section to set up local identifiers
An executable part to perform the actions
An optional exception handling section to handle exceptions
* Use CREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION so you do not have to issue a DROP statement.
* When creating the procedure from Procedure Builder, the CREATE OR REPLACE portion of the syntax is implied. Therefor
