如保用SQL语句创建一个用户，同时使这个用户有DBA的权限，谢谢高手帮忙!!!1

create user U identified by U;
grant dba to U;
grant connect to U;

create user _user identified by _password
default tablesapce users
temporary tablespace temp;grant dba to _user;---------------
_user 为指定用户名
_password 为指定密码
users,temp 为指定表空间dba 为oralce制定dba角色

谢谢二位帮忙，上面的问题搞定，我还想问问如用SQL语句导出某一用户的数据，我想通过SQL用PB程序来控制，不是在DOS下

desc all_users;
名称             是否为空? 类型
---------------- -------- ----------------
USERNAME         NOT NULL VARCHAR2(30)
USER_ID          NOT NULL NUMBER
CREATED          NOT NULL DATE

SQL> select table_name from dict where table_name like '%PRIV%';
--此语句查询出39个权限表
SQL> desc dict;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
TABLE_NAME                                         VARCHAR2(30)
COMMENTS                                           VARCHAR2(4000)SQL> select * from user_sys_privs;
--查看直接授予用户的系统权限(使用spool off; spool path ;selec……; 保存结果到文本)
SQL> select  *  from dict where table_name like '%PRIV%';
TABLE_NAME       COMMENTS
--------------------------------------------------------------------------------
ALL_COL_PRIVS
Grants on columns for which the user is the grantor, grantee, owner,
or an enabled role or PUBLIC is the grantee
ALL_COL_PRIVS_MADE
Grants on columns for which the user is owner or grantor
ALL_COL_PRIVS_RECD
Grants on columns for which the user, PUBLIC or enabled role is the grantee
ALL_REPGROUP_PRIVILEGES
Information about users who are registered for object group privileges
ALL_TAB_PRIVS
Grants on objects for which the user is the grantor, grantee, owner,
or an enabled role or PUBLIC is the grantee
ALL_TAB_PRIVS_MADE
User's grants and grants on user's objects
ALL_TAB_PRIVS_RECD
Grants on objects for which the user, PUBLIC or enabled role is the grantee
ALL_WORKSPACE_PRIVS
DBA_COL_PRIVS
All grants on columns in the database
DBA_PRIV_AUDIT_OPTS
Describes current system privileges being audited across the system and by user
DBA_REPGROUP_PRIVILEGES
Information about users who are registered for object group privileges
DBA_ROLE_PRIVS
Roles granted to users and roles
DBA_RSRC_CONSUMER_GROUP_PRIVS
Switch privileges for consumer groups
DBA_RSRC_MANAGER_SYSTEM_PRIVS
system privileges for the resource manager
DBA_SYS_PRIVS
System privileges granted to users and roles
DBA_TAB_PRIVS
All grants on objects in the database
DBA_WM_SYS_PRIVS
DBA_WORKSPACE_PRIVS
USER_COL_PRIVS
Grants on columns for which the user is the owner, grantor or grantee
USER_COL_PRIVS_MADE
All grants on columns of objects owned by the user
USER_COL_PRIVS_RECD
Grants on columns for which the user is the grantee
USER_REPGROUP_PRIVILEGES
Information about users who are registered for object group privileges
USER_ROLE_PRIVS
Roles granted to current user
USER_RSRC_CONSUMER_GROUP_PRIVS
Switch privileges for consumer groups for the user
USER_RSRC_MANAGER_SYSTEM_PRIVS
system privileges for the resource manager for the user

USER_SYS_PRIVS
System privileges granted to current user
USER_TAB_PRIVS
Grants on objects for which the user is the owner, grantor or grantee
USER_TAB_PRIVS_MADE
All grants on objects owned by the user
USER_TAB_PRIVS_RECD
Grants on objects for which the user is the grantee
USER_WM_PRIVS
USER_WORKSPACE_PRIVS
COLUMN_PRIVILEGES
Grants on columns for which the user is the grantor, grantee, owner, or
an enabled role or PUBLIC is the grantee
ROLE_ROLE_PRIVS
Roles which are granted to roles
ROLE_SYS_PRIVS
System privileges granted to roles
ROLE_TAB_PRIVS
Table privileges granted to roles

SESSION_PRIVS
Privileges which the user currently has set
TABLE_PRIVILEGES
Grants on objects for which the user is the grantor, grantee, owner,
or an enabled role or PUBLIC is the grantee
GV$ENABLEDPRIVS
Synonym for GV_$ENABLEDPRIVS
V$ENABLEDPRIVS
Synonym for V_$ENABLEDPRIVS
已选择39行。
SQL> spool off;
SQL> select * from user_role_privs;
--用户有哪些角色 (USERNAME 需要大写) 并且只能查出当前用户的信息
SQL> desc user_role_privs;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
USERNAME                                           VARCHAR2(30)
GRANTED_ROLE                                       VARCHAR2(30)
ADMIN_OPTION                                       VARCHAR2(3)
DEFAULT_ROLE                                       VARCHAR2(3)
OS_GRANTED                                         VARCHAR2(3)
SQL> select * from user_role_privs;
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
SYSTEM                         AQ_ADMINISTRATOR_ROLE          YES YES NO
SYSTEM                         DBA                            YES YES NOSQL> select * from user_tab_privs;
--检查一个用户被授予和授予其他用户的对象级权限,显示的只有owner为 sys和system的数据SQL> select * from dba_roles;
--查询系统中的所有角色
SQL> desc dba_roles;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
ROLE                                      NOT NULL VARCHAR2(30)
PASSWORD_REQUIRED                                  VARCHAR2(8)
SQL> select * from dba_roles where role='DBO';ROLE                           PASSWORD
------------------------------ --------
DBO                            NO
ALTER ROLE "DBO"
IDENTIFIED BY "111111"
SQL>  select * from dba_roles where role='DBO';
ROLE                           PASSWORD
------------------------------ --------
DBO                            YESSQL> select * from dba_sys_privs;
--查询角色的权利;(全部数目共有836个)
SQL> desc dba_sys_privs
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
GRANTEE                                   NOT NULL VARCHAR2(30)
PRIVILEGE                                 NOT NULL VARCHAR2(40)
ADMIN_OPTION                                       VARCHAR2(3)SQL> select * from dba_role_privs;
--查询角色是谁定义的 SQL> select * from column_privileges;
--原始无数据SQL> select * from role_role_privs; --查询角色的授予和被授予关系
SQL> select * from role_sys_privs; --查询角色被授予的系统级权限
SQL> select * from role_tab_privs; --查询角色的对象级权限
SQL> select * from session_privs; --
SQL> select * from tab_privileges;

create user <用户名> identified by <密码>
default tablesapce <表空间>
temporary tablespace <临时表空间>;grant dba to <用户名>;

调试易

如保用SQL语句创建一个用户，同时使这个用户有DBA的权限，谢谢高手帮忙!!!1

解决方案 »