select * from dba_users;

create tablespace kyc_coo;

create user kyc_coo identified by "123456" default tablespace kyc_coo;

create temporary tablespace kyc_temp;

alter user kyc_coo temporary tablespace kyc_temp;

select * from dba_directories;

create directory dmp as '/ceph/fileserver/backup/oracle';

grant create session,create view,create job to resource;

grant read,write on directory dmp to resource;

grant resource to kyc_coo;

--授权，创建，修改（只修改服务名中的ip），查询，删除dblink

grant create public database link,create database link to test;

--两种方式

create public database link accbak

connect to kyc_acc identified by "Lcpsys_kyc_1130"

using '(DESCRIPTION =

(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.161)(PORT = 1521)))

(CONNECT_DATA =(SERVICE_NAME =kyc_w)))';

或者

create public database link accbak

connect to kyc_acc identified by "Lcpsys_kyc_1130" using 'tdb';

select * from dba_db_links;

drop public database link accbak;

select * from acct_account@accbak;

--最后修改job中的表名。

select * from ACCT_DAILY_BAK;

select count(*) from ACCT_DAILY_BAK;

truncate table acct_daily_bak;

set serveroutput on

BEGIN

    DBMS_SCHEDULER.RUN_JOB(job_name => '"KYC_COO"."BACKUP"', USE_CURRENT_SESSION => FALSE);

END;

--查看有默认密码的用户

select * from dba_users_with_defpwd;

select * from dba_profiles;

--创建用户

grant create session to aa identified by 123456;

--另外一种创建用户

create user aa

identified by 123456

default tablespace data_ts

quota 300m data_ts

quota 200m temp_ts

temporary tablespace temp_ts

profile clerk;

--参见e36292 2-7中的设置default role解释

alter user aa default role none;

alter user aa default role all;

--参见e41084 19-61中的set role解释

select * from session_roles;

set role all;

--------------------------------------

--grantor

--n. 授予者；[法] 让与人

--grantee

--n. 受让人；被授与者

--三类权限，对象（select,delete），系统（create session），角色（sec_admin）

--查看用户，查看角色

select * from dba_users;

select * from dba_roles;

--用户拥有的角色

select * from dba_role_privs

where grantee like 'KYC%';

--角色系统权限

select * from ROLE_SYS_PRIVS

where ROLE like 'RES%';

select * from ROLE_SYS_PRIVS

where ROLE='KYC';

--角色对象权限

select * from ROLE_TAB_PRIVS

where ROLE like 'RES%';

--用户系统权限(这里面有角色也有权限，UNLIMITED TABLESPACE不能授权给角色)

select * from dba_sys_PRIVS

where grantee like 'KYC%';

--查看分配给角色或用户的系统权限，三种形式，角色，大写，用户

--connect角色是向后兼容，尽量不用，推荐创建自己的角色，而不是依赖于数据库提供的

select * from dba_sys_privs

where grantee='CONNECT';

select * from dba_sys_privs

where grantee=upper('connect');

select * from dba_sys_privs

where grantee='KYC_ACC';

--查看每一个角色所拥有的系统权限数

select grantee,count(*) from dba_sys_privs

group by grantee;

--查看对象权限

select * from dba_tab_privs

where grantee='LCPSYS';

--查看列权限

select * from dba_col_privs;

grant select on v_$sesstat to kyc_mig;

grant select on v_$statname to kyc_mig;

grant select on v_$session to kyc_mig;

grant select,delete on sys.aud$ to kyc_mig;

revoke all on orders from hr cascade constraints;

grant insert (ename,job) on scott.emp to aa,cc;

select * from dba_col_privs

where grantee in ('AA','CC');

------------------------------

--用户当前可用的权限

select * from session_privs;

--用户当前启用的角色

select * from session_roles;

--授予给当前用户的系统权限

select * from user_sys_privs;

--然后登录用户查看对象权限

select * from user_tab_privs;

-------------------------------

-------------------------------

create role dev;

grant select any table,create session to dev;

create user kyc_dev identified by "dev-test!";

grant dev to kyc_dev;

create role kyc;

grant

CREATE SEQUENCE,

CREATE TRIGGER,

CREATE CLUSTER,

CREATE PROCEDURE,

CREATE TYPE,

CREATE SESSION,

CREATE VIEW,

CREATE OPERATOR,

CREATE JOB,

CREATE TABLE,

CREATE INDEXTYPE

to kyc;

create directory dmp as '/ceph/fileserver/backup/oracle';

grant read,write on directory dmp to kyc;

grant kyc to kyc_acc;

grant kyc to KYC_ACC;

grant kyc to KYC_APP;

grant kyc to KYC_COA;

grant kyc to KYC_FRE;

grant kyc to KYC_L1U;

grant kyc to KYC_L2U;

grant kyc to KYC_LOA;

grant kyc to KYC_MAL;

grant kyc to KYC_MAR;

grant kyc to KYC_MER;

grant kyc to KYC_OTT;

grant kyc to KYC_TRA;

grant kyc to KYC_VEH;

grant UNLIMITED TABLESPACE to KYC_ACC;

grant UNLIMITED TABLESPACE to KYC_COA;

grant UNLIMITED TABLESPACE to KYC_FRE;

grant UNLIMITED TABLESPACE to KYC_L1U;

grant UNLIMITED TABLESPACE to KYC_L2U;

grant UNLIMITED TABLESPACE to KYC_LOA;

grant UNLIMITED TABLESPACE to KYC_MAL;

grant UNLIMITED TABLESPACE to KYC_MAR;

grant UNLIMITED TABLESPACE to KYC_MER;

grant UNLIMITED TABLESPACE to KYC_OTT;

grant UNLIMITED TABLESPACE to KYC_TRA;

grant UNLIMITED TABLESPACE to KYC_VEH;

revoke create view from KYC_ACC;

revoke create view from KYC_FRE;

revoke create view from KYC_L1U;

revoke create view from KYC_LOA;

revoke create view from KYC_MAR;

revoke create view from KYC_MER;

revoke create view,CREATE PROCEDURE from KYC_OTT;

revoke create view from KYC_TRA;

revoke SELECT ANY TABLE,CREATE JOB from KYC_ACC;

revoke SELECT ANY TABLE from KYC_FRE;

revoke SELECT ANY TABLE,SELECT ANY DICTIONARY from KYC_L1U;

revoke SELECT ANY TABLE from KYC_TRA;

revoke SELECT ANY TABLE from KYC_VEH;

revoke CREATE ANY JOB,CREATE JOB,DEBUG CONNECT SESSION,SELECT ANY DICTIONARY from kyc_fre;

revoke connect,resource from KYC_ACC;

revoke connect,resource from KYC_COA;

revoke connect,resource from KYC_FRE;

revoke connect,resource from KYC_L1U;

revoke connect,resource from KYC_L2U;

revoke connect,resource from KYC_LOA;

revoke connect,resource from KYC_MAL;

revoke connect,resource from KYC_MAR;

revoke connect,resource from KYC_MER;

revoke connect,resource from KYC_OTT;

revoke connect,resource from KYC_TRA;

revoke connect,resource from KYC_VEH;

revoke dba from kyc_app,kyc_fre;