oracle用户创建和权限管理

时间:2018-10-31 21:51 来源:潇湘夜雨作者:华嵩阳点击:次

登录本地系统用户

su - oracle #切换到oracle账号

sqlplus / as sysdba 登陆sys帐户

SQL> select * from V_$PWFILE_USERS; #查看哪些用户拥有SYSDBA、SYSOPER权限:

USERNAME SYSDB SYSOP SYSAS

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

SYS TRUE TRUE FALSE

SQL> show user; #查看当前登录用户

USER is "SYS"

SQL> select username,default_tablespace from user_users; #查看当前用户的默认表空间

USERNAME DEFAULT_TABLESPACE

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

ZG_MOBILE T_MOBILE

SQL> select * from user_role_privs; #查看当前用户的角色

USERNAME GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE OS_GRANTED

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

ZG_MOBILE CONNECT NO YES NO

ZG_MOBILE RESOURCE NO YES NO

SQL> select * from user_sys_privs; #查看当前用户的系统权限

USERNAME PRIVILEGE ADMIN_OPTION

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

ZG_MOBILE UNLIMITED TABLESPACE NO

SQL> select * from user_tab_privs; #查看当前用户的表权限

GRANTEE OWNER TABLE_NAME GRANTOR

SQL>select * from user_tables; #查看用户下所有的表

SQL> select TABLE_NAME from user_tables;

TABLE_NAME

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

MB_APP_LOG

MB_BSS_APP_ROLE

MB_BSS_APP_USER

MB_BSS_APP_USER_ROLE

MB_BSS_ELECTRONIC_FENCE

MB_BSS_SMS_INFO

MB_BSS_USER_TERMINAL_LOCATION

MB_SYS_APP_LOG

MB_USER_RESOURCE_MAP

MB_USER_WARING

MB_VERIYFCODE_SMSSEND

查看当前的所有数据库: select * from v$database;

SQL> select name from v$database;

NAME

---------

MOBILE

查看权限

　　select * from user_sys_privs;//查看当前用户所有权限

　　select * from user_tab_privs;//查看所用用户对表的权限

SQL> select username,default_tablespace from user_users;

USERNAME DEFAULT_TABLESPACE

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

CAMPUS T_MOBILE

SQL> select * from user_role_privs;

USERNAME GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE OS_GRANTED

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

CAMPUS CONNECT NO YES NO

CAMPUS DBA NO YES NO

CAMPUS RESOURCE NO YES NO

SQL> select * from session_privs;

create user cd_mobile identified by cd_mobile default tablespace t_mobile quota unlimited on t_mobile temporary tablespace t_temp;

grant connect,resource to cd_mobile;

grant UNLIMITED TABLESPACE to cd_mobile;

增加数据库用户

SQL> create user cd_mobile identified by cd_mobile default tablespace t_mobile quota unlimited on t_mobile temporary tablespace t_temp;

User created.

用户授权:

SQL> grant connect,resource to cd_mobile;

Grant succeeded.

SQL> grant UNLIMITED TABLESPACE to cd_mobile; #用户使用表空间的权限

Grant succeeded.

create user lz_mobile identified by lz_mobile default tablespace t_mobile quota unlimited on t_mobile temporary tablespace t_temp;

grant connect,resource to lz_mobile;

grant UNLIMITED TABLESPACE to lz_mobile;

create user zg_mobile identified by zg_mobile default tablespace t_mobile quota unlimited on t_mobile temporary tablespace t_temp;

grant connect,resource to zg_mobile;

grant UNLIMITED TABLESPACE to zg_mobile;

更改数据库用户的密码：(将sys与system的密码改为test.)

alter user sys indentified by test;

alter user system indentified by test;

SQL> select username from dba_users; #查看数据库中的用户，sys用户登录sqlplus才能使用

USERNAME

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

GA_MOBILE

YB_MOBILE

BZ_MOBILE

CAMPUS

LZ_MOBILE

XC_BIGDATA

GT_MOBILE

ZG_MOBILE

PZH_MOBILE

CD_MOBILE

NJ_MOBILE

select username, default_tablespace,TEMPORARY_TABLESPACE from dba_users; #查看用户的表空间

select * from dba_tablespaces； #查看表空间属性。

创建表空间

/*分为四步 */

/*第1步：创建临时表空间第一次必须创建，后面可以不创建*/

create temporary tablespace user_temp

tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

#extent management local参数系统默认，可不写。

/*第2步：创建数据表空间 */

create tablespace user_data

logging

datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'

size 500m

autoextend on

next 500m maxsize 20480m

extent management local;

#LOGGING实际上是对象的一个属性，用来表示在创建对象时是否记录REDO日志，包括在做DML时是否记录REDO日志。默认会添加，可不写。
创建用户并指定表空间 */

create user username identified by password

default tablespace user_data

temporary tablespace user_temp;

#数据库创建以后，系统中只会存在一个名为DEFAULT的默认PROFILE，在用户创建之后，如果不做特殊指定，每个用户的PROFILE都会默认的使用个默认的PROFILE。

/*第4步：给用户授予权限 */

grant connect,resource,dba to username;

删除表空间

drop tablespace EPOINTBID_PSP including contents and datafiles;

drop user EPOINTBID_PSP cascade;

导出

expdp EPOINTBID_TP7_GY/epointgy123@orcl directory=dir_epoint dumpfile=EPOINTBID_TP7_GY08.dmp schemas=EPOINTBID_ASP logfile=EPOINTBID_ASP.log

导入备份数据到不同的用户和表空间
impdp EPOINTFRAME/EPOINTFRAME123@localhost:1521 dumpfile=EPOINTBID_TP7_GY12.DMP remap_schema=EPOINTBID_TP7_GY:EPOINTFRAME remap_tablespace=EPOINTBID_TP7_GY:EPOINTFRAME

(责任编辑：liangzh)

oracle用户创建和权限管理

时间:2018-10-31 21:51 来源:潇湘夜雨 作者:华嵩阳 点击:次

时间:2018-10-31 21:51 来源:潇湘夜雨作者:华嵩阳点击:次