一、创建用户以及授权管理权限
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存放表对象
