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;