Oracle用户权限,角色操作命令

一、创建用户以及授权管理权限

SQL> create user user50 identified by user50;            //创建user50用户

User created.

SQL> grant connect,resource to user50;                   //授权user50用户登录权限和管理权限

Grant succeeded.

SQL> conn user50/user50                                  //切换user50用户
Connected.

SQL> create table t1(id int,name varchar2(20));         //创建一张表

Table created.

SQL> insert into t1 values(1,'zs');               //表中添加数据

1 row created.

SQL> commit;                                    //事物提交

SQL> select table_name from user_tables;        //查看用户创建的表

TABLE_NAME
------------------------------------------------------------
T1

SQL> desc T1                                     //查看T1表的结构
 Name                                                        Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ID                                                       NUMBER(38)
 NAME                                                         VARCHAR2(20)

二、查询普通用户拥有那些权限

SQL> conn system/123456
Connected.
SQL> set line 200;
SQL> select * from dba_role_privs where GRANTEE='USER50';

GRANTEE                              GRANTED_ROLE                   ADMIN_ DEFAUL
------------------------------------------------------------ ------------------------------------------------------------ ------ ------
USER50                               RESOURCE                       NO   YES
USER50                               CONNECT                        NO   YES


SQL> select * from dba_sys_privs where GRANTEE='USER50';

GRANTEE                              PRIVILEGE                          ADMIN_
------------------------------------------------------------ -------------------------------------------------------------------------------- ------
USER50                               UNLIMITED TABLESPACE                   NO

三、系统权限的传递

grant connect,resorce to user50 with admin option;

四、系统权限回收

>>系统权限只能由DBA用户回收
revoke connect,resource from user50;

五、普通用户授权普通权限

>>普通对象权限管理
SQL> create user user01 identified by 123;
SQL> create user user02 identified by 123;
SQL> create table t1(id int,name varchar2(20));
SQL> grant select,update,insert on t1 to user01;
SQL> grant all on t1 to user02;
>>将表的操作权限授予全体用户
SQL> grant all on t1 to public;
>>用户可以查询的表(系统表,用户表,元数据)
SQL> select owner,table_name from all_tables;
>>用户创建的表(用户表,元数据)
SQL> select table_name from user_tables;
>>获取可以存取的表(被授权的)
SQL> select grantor,table_schema,table_name,privilege from all_tab_privs;
>>授出权限的表
SQL> select grantee,owner,table_name,privilege from user_tab_privs;

image.png

六、创建一个角色

 >>创建一个空角色
SQL> create role role1;

Role created.
 >>授权给角色
SQL> grant create any table,create procedure to role1;

Grant succeeded.
 >>授予角色给用户
SQL> create user user1 identified by 123;

User created.

SQL> grant role1 to user1;

Grant succeeded.

 >>查看角色所包含的权限
sql> set line 200;  //设置列宽，竖向显示
sql> select * from role_sys_privs;
 >>删除角色
sql> drop role role1;
角色删除后，原来拥有该角色的用户就不在拥有该角色了，相应的权限也没有了

image.png

设置当前用户要生效的角色

image.png

user -----> schema -----> 对象
创建一个用户就会生成一个schema(虚拟的库，模式空间)，schema存放表对象