#眉標=Oracle、Object、ROWID
#副標=管理Oracle資料庫必要的功力(6)
#大標=運用物件讓資料庫管理增加彈性(下)
#作者=文／圖 張天慧


========<反灰>========
UNDO_MANAGEMENT = AUTO
UNDO_TABLESPACE = UNDO
=============<END>==================



=======<反灰>================
CREATE PUBLIC SYNONYM TEST_TABLE01 
FOR TEST.TEST_TABLE01;
========<END>====================




========<反灰>=======
CREATE PROFILE profile_name LIMIT
  [SESSION_PER_USER integer | UNLIMITED]
  [CPU_PER_SESSION integer | UNLIMITED]
  [CPU_PER_CALL integer | UNLIMITED]
  [CONNECT_TIME integer | UNLIMITED]
  [IDLE_TIME integer | UNLIMITED]
  [LOGICAL_READS_PER_SESSION integer | UNLIMITED]
  [LOGICAL_READS_PER_CALL integer | UNLIMITED]
  [PRIVATE_SGA integer [K|M] | UNLIMITED]
  [COMPOSIT_LIMIT integer | UNLIMITED]
  [FAILED_LOGIN_ATTEMPTS integer | UNLIMITED]
  [PASSWORD_LIFE_TIME expression | UNLIMITED]
  [PASSWORD_REUSE_TIME expression | UNLIMITED]
  [PASSWORD_LOCK_TIME expression | UNLIMITED]
  [PASSWORD_GRACE_TIME expression | UNLIMITED]
  [PASSWORD_VERIFY_FUNCTION expression | UNLIMITED]
========<END>=======




========<反灰>=======
CREATE PROFILE TEST_PROFILE LIMIT
  SESSIONS_PER_USER 2m CPU_PER_SESSION 100
  CONNECT_TIME 120  LOGICAL_READS_PER_CALL 100
  PASSWORD_LIFE_TIME 30  PASSWORD_REUSE_TIME 3;
========<END>=======




========<反灰>=======
ALTER PROFILE TEST_PROFILE LIMIT
 SESSIONS_PER_USER 10
 CONNECT_TIME 60
 PASSWORD_LIFE_TIME 10;
========<END>=======




==========<反灰>====================
CREATE USER username
  [IDENTIFIED BY [password | EXTERNALLY]]
  [DEFAULT TABLESPACE tablespace]
  [TEMPORARY TABLESPACE tablespace]
  [QUOTA [INTEGER [K|M] | UNLIMITED] ON tablespace]
  [QUOTA [INTEGER [K|M] | UNLIMITED] ON tablespace]
  [PROFILE profilename]
==========<END>====================




==========<反灰>====================
CREATE USER TEST_ACCOUNT
  IDENTIFIED BY “TEST” DEFAULT TABLESPACE TABLESPACE1
  TEMPORARY TABLESPACE TEMP
  QUOTA 500 M ON TABLESPACE_NAME PROFILE PROFILE_NAME;
==========<END>====================




===========<反灰>================
GRANT CREATE SESSION TO test_account;
GRANT CREATE TABLE TO test_account;
==================<END>====================



===============<反灰>=====================
CREATE ROLE rolename
  [NOT IDENTIFIED | IDENTIFIED BY password | IDENTIFIED BY EXTERNALLY][NOTIDENTIFIED | IDENTIFIED BY password | IDENTIFIED BY EXTERNALLY]：
==================<END>====================



===============<反灰>=====================
CREATE ROLE test_role;
CREATE ROLE test_role IDENTIFIED BY “TEST”;
CREATE ROLE test_role IDENTIFIED BY EXTERNALLY;
==================<END>====================



===============<反灰>=====================
ALTER ROLE test_role IDENTIFIED BY “test_password_new”;
ALTER ROLE sales_clerk IDENTIFIED BY EXTERNALLY;
ALTER ROLE test_role NOT IDENTIFIED;
==================<END>====================



===============<反灰>=====================
GRANT ROLE test_role TO test_account;
GRANT ROLE test_role TO test_account WITH ADMIN OPTION;
ALTER USER test_account DEFAULT ROLE test_role;
ALTER USER test_account DEFAULT ROLE test_role, test_role1;
==================<END>====================