Oracle 帳號基本管理 收藏

1. Oracle安裝完成後的使用者名稱和密碼
sys/change_on_install
system/manager
scott/tiger
sysman/oem_temp
internal/oracle
 
2.修改使用者的密碼
SQL> conn sys/change_on_install as sysdba
Connected.
SQL> alter user sys identified by ******;
User altered.
 
3.為使用者解鎖
SQL> conn scott/tiger
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SQL>conn sys/change_on_install as sysdba
Connected.
SQL> alter user scott account unlock;
User altered.
 
鎖定使用者：
SQL> alter user scott account lock;
 
4.檢視所有使用者：
  select * from dba_users;
  select * from all_users;
  select * from user_users;

5.檢視使用者或角色系統許可權：
  select * from dba_sys_privs;
  select * from user_sys_privs;

6.檢視使用者物件許可權：
  select * from dba_tab_privs;
  select * from all_tab_privs;
  select * from user_tab_privs;

7..檢視所有角色：
  select * from dba_roles;

8.檢視使用者或角色所擁有的角色：
  select * from dba_role_privs;
  select * from user_role_privs;
 
9.建立使用者
SQL> create user kevin identified by password
2 default tablespace users
3 temporary tablespace temp
4 quota 10M on users;
User created.
SQL> conn kevin/password
ERROR:
ORA-01045: user KEVIN lacks CREATE SESSION privilege; logon denied
SQL> grant create session to kevin;  //授權使用者可以連線資料庫
Grant succeeded.
 
10.授權使用者connect和resource角色
SQL> grant connect to kevin; 
Grant succeeded.
SQL> grant resource to kevin;
Grant succeeded.
SQL> grant connect,resource to kevin;
Grant succeeded.
 
11.檢視connect和resource的許可權
SQL> select * from dba_sys_privs where grantee='CONNECT';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        ALTER SESSION                            NO
CONNECT                        CREATE CLUSTER                           NO
CONNECT                        CREATE DATABASE LINK                     NO
CONNECT                        CREATE SEQUENCE                          NO
CONNECT                        CREATE SESSION                           NO
CONNECT                        CREATE SYNONYM                           NO
CONNECT                        CREATE TABLE                             NO
CONNECT                        CREATE VIEW                              NO

8 rows selected.
 
SQL> select * from dba_sys_privs where grantee='RESOURCE';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO
8 rows selected.
 
CONNECT角色：--是授予終端使用者的典型權利，最基本的  
   ALTER    SESSION    --修改會話  
   CREATE    CLUSTER    --建立聚簇  
   CREATE    DATABASE    LINK    --建立資料庫連結  
   CREATE    SEQUENCE    --建立序列  
   CREATE    SESSION    --建立會話  
   CREATE    SYNONYM    --建立同義詞  
   CREATE    VIEW    --建立檢視  
      
RESOURCE角色：--是授予開發人員的  
   CREATE    CLUSTER    --建立聚簇  
   CREATE    PROCEDURE    --建立過程  
   CREATE    SEQUENCE    --建立序列  
   CREATE    TABLE    --建表  
   CREATE    TRIGGER    --建立觸發器  
   CREATE    TYPE    --建立型別
   CREATE    INDEXTYPE    --建立索引型別
   CREATE    OPERATOR  --建立操作符
 
12.授權使用者對錶的操作許可權
SQL> grant alter any table to kevin;  //授權Kevin使用者可以訪問任何表
Grant succeeded.
SQL> grant alter,insert,update on scott.emp to kevin; //授權使用者kevin修改,插入，更新表emp的許可權
 
SQL> grant create session, create table to kevin with admin option;
Grant succeeded.
SQL> grant alter,insert,update on scott.emp to kevin with grant option;
Grant succeeded.
 
13.with admin option 和with grant option的區別
WITH ADMIN OPTION
enables the grantee to grant the system privilege or role to other users or roles
如果撤銷Kevin的system privilege，James的system privilege許可權還存在

WITH GRANT OPTION
enables the grantee to grant the object privilege to other users or roles
如果撤銷Kevin的object privilege，James的system privilege許可權也被撤銷
 
14.刪除使用者：
SQL> drop user kevin;
User dropped.
 
15.建立和刪除角色
SQL> create role myrole;
Role created.
SQL> drop role myrole;
Role dropped.
 
16.將角色繫結到使用者
SQL> grant myrole to kevin;
Grant succeeded.
 
17.建立概要檔案（Profile）
建立一個使用者只能訪問三次（密碼錯誤）的概要檔案
create profile myprofile limit
sessions_per_user default
cpu_per_session default
cpu_per_call default
connect_time default
idle_time default
logical_reads_per_session default
logical_reads_per_call default
composite_limit default
private_sga default
failed_login_attempts default
password_life_time default
password_reuse_time default
password_reuse_max default
password_lock_time 3
password_grace_time default
password_verify_function null;
 
18.將概要檔案繫結至使用者
create user kevin1  identified by t profile myprofile;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25897606/viewspace-705140/，如需轉載，請註明出處，否則將追究法律責任。