如何创建Oracle 初始化DBA、Connect角色

|举报|字号 订阅

 

 

一、创建DBA角色

 

1.在plsql中以 sys/****  as sysdba登陆，创建角色DBA

使用下列语句进行创建，创建过程中会报错，不用管，这是给DBA赋权时有部分权限没有赋上

-- Create the role 

create role DBA;

-- Grant/Revoke object privileges 

grant execute on SYS.DBMS_DBVERIFY to DBA;

grant execute on SYS.DBMS_DEFER_QUERY to DBA;

grant execute on SYS.DBMS_DEFER_SYS to DBA;

grant execute on SYS.DBMS_FEATURE_USAGE to DBA;

grant execute on SYS.DBMS_FLASHBACK to DBA;

grant execute on SYS.DBMS_MONITOR to DBA;

grant execute on SYS.DBMS_RESUMABLE to DBA;

grant execute on SYS.DBMS_SERVER_ALERT to DBA;

grant execute on SYS.DBMS_SERVER_TRACE to DBA;

grant execute on SYS.DBMS_SERVICE to DBA;

grant execute on SYS.DBMS_STORAGE_MAP to DBA;

grant execute on SYS.DBMS_UADV_ARR to DBA;

grant execute on SYS.DBMS_UNDO_ADV to DBA;

grant execute on SYS.DBMS_WORKLOAD_REPOSITORY to DBA;

grant execute on SYS.LOAD_UNDO_STAT to DBA;

grant select, insert, update, delete, alter on SYS.MAP_OBJECT to DBA;

grant execute on SYS.OUTLN_EDIT_PKG to DBA;

grant execute on SYS.OUTLN_PKG to DBA;

grant execute on SYS.RESET_UNDO_STAT to DBA;

grant update on WKSYS.WK$SYS_CONFIG to DBA;

-- Grant/Revoke role privileges 

grant delete_catalog_role to DBA with admin option;

grant execute_catalog_role to DBA with admin option;

grant exp_full_database to DBA;

grant gather_system_statistics to DBA;

grant imp_full_database to DBA;

grant java_admin to DBA;

grant java_deploy to DBA;

grant olap_dba to DBA;

grant scheduler_admin to DBA with admin option;

grant select_catalog_role to DBA with admin option;

grant wm_admin_role to DBA;

grant xdbadmin to DBA;

-- Grant/Revoke system privileges 

grant administer any sql tuning set to DBA with admin option;

grant administer database trigger to DBA with admin option;

grant administer resource manager to DBA with admin option;

grant administer sql tuning set to DBA with admin option;

grant advisor to DBA with admin option;

grant alter any cluster to DBA with admin option;

grant alter any dimension to DBA with admin option;

grant alter any evaluation context to DBA with admin option;

grant alter any index to DBA with admin option;

grant alter any indextype to DBA with admin option;

grant alter any library to DBA with admin option;

grant alter any materialized view to DBA with admin option;

grant alter any outline to DBA with admin option;

grant alter any procedure to DBA with admin option;

grant alter any role to DBA with admin option;

grant alter any rule to DBA with admin option;

grant alter any rule set to DBA with admin option;

grant alter any sequence to DBA with admin option;

grant alter any sql profile to DBA with admin option;

grant alter any table to DBA with admin option;

grant alter any trigger to DBA with admin option;

grant alter any type to DBA with admin option;

grant alter database to DBA with admin option;

grant alter profile to DBA with admin option;

grant alter resource cost to DBA with admin option;

grant alter rollback segment to DBA with admin option;

grant alter session to DBA with admin option;

grant alter system to DBA with admin option;

grant alter tablespace to DBA with admin option;

grant alter user to DBA with admin option;

grant analyze any to DBA with admin option;

grant analyze any dictionary to DBA with admin option;

grant audit any to DBA with admin option;

grant audit system to DBA with admin option;

grant backup any table to DBA with admin option;

grant become user to DBA with admin option;

grant comment any table to DBA with admin option;

grant create any cluster to DBA with admin option;

grant create any context to DBA with admin option;

grant create any dimension to DBA with admin option;

grant create any directory to DBA with admin option;

grant create any evaluation context to DBA with admin option;

grant create any index to DBA with admin option;

grant create any indextype to DBA with admin option;

grant create any job to DBA with admin option;

grant create any library to DBA with admin option;

grant create any materialized view to DBA with admin option;

grant create any operator to DBA with admin option;

grant create any outline to DBA with admin option;

grant create any procedure to DBA with admin option;

grant create any rule to DBA with admin option;

grant create any rule set to DBA with admin option;

grant create any sequence to DBA with admin option;

grant create any sql profile to DBA with admin option;

grant create any synonym to DBA with admin option;

grant create any table to DBA with admin option;

grant create any trigger to DBA with admin option;

grant create any type to DBA with admin option;

grant create any view to DBA with admin option;

grant create cluster to DBA with admin option;

grant create database link to DBA with admin option;

grant create dimension to DBA with admin option;

grant create evaluation context to DBA with admin option;

grant create indextype to DBA with admin option;

grant create job to DBA with admin option;

grant create library to DBA with admin option;

grant create materialized view to DBA with admin option;

grant create operator to DBA with admin option;

grant create procedure to DBA with admin option;

grant create profile to DBA with admin option;

grant create public database link to DBA with admin option;

grant create public synonym to DBA with admin option;

grant create role to DBA with admin option;

grant create rollback segment to DBA with admin option;

grant create rule to DBA with admin option;

grant create rule set to DBA with admin option;

grant create sequence to DBA with admin option;

grant create session to DBA with admin option;

grant create synonym to DBA with admin option;

grant create table to DBA with admin option;

grant create tablespace to DBA with admin option;

grant create trigger to DBA with admin option;

grant create type to DBA with admin option;

grant create user to DBA with admin option;

grant create view to DBA with admin option;

grant debug any procedure to DBA with admin option;

grant debug connect session to DBA with admin option;

grant delete any table to DBA with admin option;

grant dequeue any queue to DBA with admin option;

grant drop any cluster to DBA with admin option;

grant drop any context to DBA with admin option;

grant drop any dimension to DBA with admin option;

grant drop any directory to DBA with admin option;

grant drop any evaluation context to DBA with admin option;

grant drop any index to DBA with admin option;

grant drop any indextype to DBA with admin option;

grant drop any library to DBA with admin option;

grant drop any materialized view to DBA with admin option;

grant drop any operator to DBA with admin option;

grant drop any outline to DBA with admin option;

grant drop any procedure to DBA with admin option;

grant drop any role to DBA with admin option;

grant drop any rule to DBA with admin option;

grant drop any rule set to DBA with admin option;

grant drop any sequence to DBA with admin option;

grant drop any sql profile to DBA with admin option;

grant drop any synonym to DBA with admin option;

grant drop any table to DBA with admin option;

grant drop any trigger to DBA with admin option;

grant drop any type to DBA with admin option;

grant drop any view to DBA with admin option;

grant drop profile to DBA with admin option;

grant drop public database link to DBA with admin option;

grant drop public synonym to DBA with admin option;

grant drop rollback segment to DBA with admin option;

grant drop tablespace to DBA with admin option;

grant drop user to DBA with admin option;

grant enqueue any queue to DBA with admin option;

grant execute any class to DBA with admin option;

grant execute any evaluation context to DBA with admin option;

grant execute any indextype to DBA with admin option;

grant execute any library to DBA with admin option;

grant execute any operator to DBA with admin option;

grant execute any procedure to DBA with admin option;

grant execute any program to DBA with admin option;

grant execute any rule to DBA with admin option;

grant execute any rule set to DBA with admin option;

grant execute any type to DBA with admin option;

grant export full database to DBA with admin option;

grant flashback any table to DBA with admin option;

grant force any transaction to DBA with admin option;

grant force transaction to DBA with admin option;

grant global query rewrite to DBA with admin option;

grant grant any object privilege to DBA with admin option;

grant grant any privilege to DBA with admin option;

grant grant any role to DBA with admin option;

grant import full database to DBA with admin option;

grant insert any table to DBA with admin option;

grant lock any table to DBA with admin option;

grant manage any queue to DBA with admin option;

grant manage scheduler to DBA with admin option;

grant manage tablespace to DBA with admin option;

grant on commit refresh to DBA with admin option;

grant query rewrite to DBA with admin option;

grant restricted session to DBA with admin option;

grant resumable to DBA with admin option;

grant select any dictionary to DBA with admin option;

grant select any sequence to DBA with admin option;

grant select any table to DBA with admin option;

grant select any transaction to DBA with admin option;

grant under any table to DBA with admin option;

grant under any type to DBA with admin option;

grant under any view to DBA with admin option;

grant update any table to DBA with admin option;

 

2.在sqlplus中以 sys/****  as sysdba登陆，给dba角色赋权

用 sys/**** as sysdba登录sqlplus，然后执行下列语句： 

 

      

grant all privileges to dba with admin option;  

 

grant select_catalog_role to dba with admin option;  

 

grant execute_catalog_role to dba with admin option;  

 

grant delete_catalog_role to dba with admin option;  

 

 

 

二、创建CONNECT角色

在plsql中直接创建角色CONNECT，执行下列语句即可：

-- Create the role 

create role CONNECT;

-- Grant/Revoke system privileges 

grant alter session to CONNECT;

grant create cluster to CONNECT;

grant create database link to CONNECT;

grant create sequence to CONNECT;

grant create session to CONNECT;

grant create synonym to CONNECT;

grant create table to CONNECT;

grant create view to CONNECT;

 

 

然后给system和相应的用户赋予DBA的角色，即可操作

 

注：如果是linux用户可以使用终端之后输入

su-oracle

sqlplus

之后按照以上步骤就可以了~

linux里面这个文件可以用查看的方式打开之后就能复制如果用记事本貌似复制不了~