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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/36489.html
標籤：基礎和管理
上一篇：小白剛入公司就收到SQL優化需求
下一篇：一個sql陳述句查詢優化的問題