
P41
3.1
CONNECT scott/tiger@orcl
COLUMN USERNAME FORMAT A10
SELECT username,privilege,admin_option FROM user_sys_privs;
CONN system/systempwd@orcl
SELECT username,privilege,admin_option FROM user_sys_privs;
COLUMN GRANTEE FORMAT A22
SET PAGESIZE 800
SELECT grantee,privilege,admin_option FROM dba_sys_privs
  ORDER BY grantee,privilege;

P43
3.2
CONN sys/syspwd@orcl AS SYSDBA
GRANT CREATE TABLESPACE TO scott;
GRANT CREATE DATABASE LINK TO scott;
CONNECT scott/tiger@orcl
COLUMN USERNAME FORMAT A10
SELECT username,privilege,admin_option FROM user_sys_privs;
CONN sys/syspwd@orcl AS SYSDBA
REVOKE CREATE TABLESPACE FROM scott;
REVOKE CREATE DATABASE LINK FROM scott;


P43
3.3
CONN scott/tiger@orcl
GRANT SELECT,INSERT,DELETE ON emp TO hr;
CONN hr/hrpwd@orcl
INSERT INTO emp(EMPNO,ENAME,MGR,SAL,DEPTNO)
  VALUES(99,'TESTNAME',7934,1200,10);
INSERT INTO scott.emp(EMPNO,ENAME,MGR,SAL,DEPTNO)
  VALUES(99,'TESTNAME',7934,1200,10);
COMMIT;
COLUMN MGR FORMAT 9999
COLUMN SAL FORMAT 9999
COLUMN COMM FORMAT 9999
COLUMN DEPTNO FORMAT 9999
SELECT * FROM scott.emp WHERE deptno=10;
UPDATE scott.emp SET sal=1500 WHERE empno=99;
DELETE FROM scott.emp WHERE empno=99;
COMMIT;
COLUMN OWNER FORMAT A10
COLUMN TABLE_NAME FORMAT A10
COLUMN GRANTOR FORMAT A10
COLUMN PRIVILEGE FORMAT A10
COLUMN GRANTABLE FORMAT A10
SELECT owner,table_name,grantor,privilege,grantable
  FROM user_tab_privs_recd;
CONN scott/tiger@orcl
COLUMN GRANTEE FORMAT A10
SELECT grantee,table_name,grantor,privilege,grantable
  FROM user_tab_privs_made;
 
P45
3.4
CONN hr/hrpwd@orcl
GRANT UPDATE(employee_id, first_name, last_name)
 ON employees
 TO scott WITH GRANT OPTION;
COLUMN COLUMN_NAME FORMAT A12
SELECT * FROM user_col_privs_made;
CONN scott/tiger@orcl
SELECT * FROM user_col_privs_recd;
CONN hr/hrpwd@orcl
REVOKE UPDATE ON employees FROM scott;

P47
--ע⣺Ⱥ(=)ǰпո
orapwd file=D:\oracle10g2\product\10.2.0\db_1\database\pwdtest.ora password=my_pwd entries=15

CONN sys/syspwd@orcl AS SYSDBA
SELECT * FROM v_$pwfile_users;

P48
3.5
CONNECT system/systempwd@orcl
DROP USER stu01 CASCADE;
CREATE USER stu01 IDENTIFIED BY stu01pwd
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP;
GRANT CREATE SESSION TO stu01;
GRANT CREATE CLUSTER TO stu01;
GRANT CREATE DATABASE LINK TO stu01;
GRANT CREATE ANY INDEX TO stu01;
GRANT CREATE MATERIALIZED VIEW TO stu01;
GRANT CREATE PROCEDURE TO stu01;
GRANT CREATE SEQUENCE TO stu01;
GRANT CREATE SYNONYM TO stu01;
GRANT CREATE TABLE TO stu01;
GRANT CREATE TRIGGER TO stu01;
GRANT CREATE TYPE TO stu01;
GRANT CREATE VIEW TO stu01;
CONN stu01/stu01pwd@orcl
COLUMN USERNAME FORMAT A10
SET PAGESIZE 20
SELECT username,privilege,admin_option FROM user_sys_privs;
CONNECT system/systempwd@orcl
DROP USER stu01 CASCADE;

P50
3.6
CONNECT system/systempwd@orcl
DROP USER stu01 CASCADE;
CREATE USER stu01 IDENTIFIED BY stu01pwd
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP;
CREATE ROLE r_stu;
GRANT CREATE SESSION,CREATE CLUSTER,CREATE DATABASE LINK,
      CREATE ANY INDEX,CREATE MATERIALIZED VIEW,CREATE PROCEDURE,
      CREATE SEQUENCE,CREATE SYNONYM,CREATE TABLE,CREATE TRIGGER,
      CREATE TYPE,CREATE VIEW
  TO r_stu;
GRANT r_stu TO stu01;
CONN stu01/stu01pwd@orcl
COLUMN USERNAME FORMAT A10
SELECT username,granted_role FROM user_role_privs;
CONNECT system/systempwd@orcl
DROP ROLE r_stu;
DROP USER stu01 CASCADE;

P52
3.7
CONN sys/syspwd@orcl AS SYSDBA
DROP PROFILE stu_profile CASCADE;
CREATE PROFILE stu_profile LIMIT
CPU_PER_SESSION 1000
CPU_PER_CALL 1000
CONNECT_TIME 110
IDLE_TIME 30
SESSIONS_PER_USER 10
LOGICAL_READS_PER_SESSION  DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
PRIVATE_SGA DEFAULT
COMPOSITE_LIMIT DEFAULT
FAILED_LOGIN_ATTEMPTS 20
PASSWORD_LOCK_TIME 30
PASSWORD_GRACE_TIME 60
PASSWORD_LIFE_TIME 20
PASSWORD_REUSE_MAX 5
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL;

P52
޸ĺutlpwdmg.sqlļ

CONN sys/syspwd@orcl AS SYSDBA

CREATE OR REPLACE FUNCTION verify_function
(username VARCHAR2,
  password VARCHAR2,
  old_password VARCHAR2)
  RETURN boolean IS 
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   ispunct boolean;
   digitarray VARCHAR2(20);
   punctarray VARCHAR2(25);
   chararray VARCHAR2(52);

BEGIN 
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyz' ||
                  'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray:='!"#$%&()``*+,-/:;<=>?_';

   -- Check if the password is same as the username
   IF NLS_LOWER(password) = NLS_LOWER(username) THEN
     raise_application_error(-20001, 'ûͬ');
   END IF;

   -- Check for the minimum length of the password
   IF length(password) < 4 THEN
      raise_application_error(-20002, 'С4ַ');
   END IF;

   -- Check if the password is too simple. A dictionary of words 
   -- may be maintained and a check may be made so as not to
   -- allow the words that are too simple for the password.
   IF NLS_LOWER(password) IN ('welcome', '123456', 'abcd') THEN
      raise_application_error(-20002, '̫');
   END IF;

   -- Check if the password contains at least one letter,
   -- one digit and one punctuation mark.
   -- 1. Check for the digit
   isdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP 
      FOR j IN 1..m LOOP 
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            isdigit:=TRUE;
             GOTO findchar;
         END IF;
      END LOOP;
   END LOOP;
   IF isdigit = FALSE THEN
      raise_application_error(-20003, 
                  'Ӧٰ1ַ1ֺ1ַ');
   END IF;
   -- 2. Check for the character
   <<findchar>>
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP 
      FOR j IN 1..m LOOP 
         IF substr(password,j,1) = substr(chararray,i,1) THEN
            ischar:=TRUE;
             GOTO findpunct;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20003, 
                  'Ӧٰ1ַ1ֺ1ַ');
   END IF;
   -- 3. Check for the punctuation
   <<findpunct>>
   ispunct:=FALSE;
   FOR i IN 1..length(punctarray) LOOP 
      FOR j IN 1..m LOOP 
         IF substr(password,j,1) = substr(punctarray,i,1) THEN
            ispunct:=TRUE;
             GOTO endsearch;
         END IF;
      END LOOP;
   END LOOP;
   IF ispunct = FALSE THEN
      raise_application_error(-20003, 
                   'Ӧٰ1ַ1ֺ1ַ');
   END IF;

   <<endsearch>>
   -- Check if the password differs from the previous password 
   -- by at least 3 letters
   IF old_password IS NOT NULL THEN
     differ := length(old_password) - length(password);

     IF abs(differ) < 3 THEN
       IF length(password) < length(old_password) THEN
         m := length(password);
       ELSE
         m := length(old_password);
       END IF;

       differ := abs(differ);
       FOR i IN 1..m LOOP
         IF substr(password,i,1) != substr(old_password,i,1) THEN
           differ := differ + 1;
         END IF;
       END LOOP;

       IF differ < 3 THEN
         raise_application_error(-20004,'¿Ӧ3ַͬ');
       END IF;
     END IF;
   END IF;
   -- Everything is fine; return TRUE ;   
   RETURN(TRUE);
END;
/

ALTER PROFILE DEFAULT LIMIT
  PASSWORD_LIFE_TIME 60
  PASSWORD_GRACE_TIME 10
  PASSWORD_REUSE_TIME 1800
  PASSWORD_REUSE_MAX UNLIMITED
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LOCK_TIME 1/1440
  PASSWORD_VERIFY_FUNCTION verify_function;


P56
3.8
CONN system/systempwd@orcl
GRANT CREATE PUBLIC SYNONYM TO hr;
CONN hr/hrpwd@orcl
CREATE SYNONYM sy_emp FOR employees;
CREATE PUBLIC SYNONYM syp_country FOR countries;
GRANT SELECT ON syp_country TO SCOTT;
CONN scott/tiger@orcl
SELECT * FROM syp_country;
 

