34,587
社区成员
发帖
与我相关
我的任务
分享
/*************************************
* AUser 用户
**************************************/
create table AUser
(
id int not null identity(1,1),
username varchar(20),
password varchar(20)
)
go
/*************************************
* ARole 角色
**************************************/
create table ARole
(
id int not null identity(1,1),
rolename varchar(20),
pid int not null
)
go
/*************************************
* AUserRole 授予用户角色
**************************************/
create table AUserRole
(
id int not null identity(1,1),
userid int not null,
roleid int not null
)
go
/*************************************
* APrivilege 权限
**************************************/
create table APrivilege
(
id int not null identity(1,1),
privilegename varchar(20),
moduleid int not null,
operationid int not null
)
go
/*************************************
* ARolePrivilege 角色授予权限
**************************************/
create table ARolePrivilege
(
id int not null identity(1,1),
roleid int not null,
privilegeid int not null
)
go
/*************************************
* AMoudle 模块
**************************************/
create table AModule
(
id int not null identity(1,1),
modulename varchar(20)
)
go
/*************************************
* AOperation 操作
**************************************/
create table AOperation
(
id int not null identity(1,1),
operationname varchar(20)
)
go
alter table AUser
add constraint PK_AUser_id primary key(id)
go
alter table AOperation
add constraint PK_AOperation_id primary key(id)
go
alter table ARole
add constraint PK_ARole_id primary key(id)
go
alter table AModule
add constraint PK_AModule_id primary key(id)
go
alter table APrivilege
add constraint PK_APrivilege_id primary key(id)
alter table APrivilege
add constraint FK_AMoudle_APrivilege foreign key(moduleid) references AModule(id)
alter table APrivilege
add constraint FK_AOperation_APrivilege foreign key(operationid )references AOperation(id)
go
alter table ARolePrivilege
add constraint PK_ARolePrivilege_id primary key(id)
alter table ARolePrivilege
add constraint FK_ARole_ARolePrivilege foreign key(roleid) references ARole(id)
alter table ARolePrivilege
add constraint FK_APrvilege_ARolePrivilege foreign key(privilegeid) references APrivilege(id)
go
alter table AUserRole
add constraint PK_AUserRole_id primary key(id)
alter table AUserRole
add constraint FK_AUser_AUserRole foreign key(userid) references AUser(id)
alter table AUserRole
add constraint FK_ARole_AUserRole foreign key(roleid) references ARole(id)
select * from AUserRole
left join ARole on ARole.id=AUserRole.roleid
left join ARolePrivilege on ARole.id=ARolePrivilege.roleid
left join APrivilege on ARolePrivilege.privilegeid=APrivilege.id
left join AOperation on AOperation.id=APrivilege.operationid
left join AModule on AModule.id=APrivilege.moduleid
where AUserRole.roleid=XX
'grant...with grant option'
1. 删除原有帐号s,s1
SQL> conn sys/password as sysdba;
Connected.
SQL> drop user s cascade;
User dropped.
SQL> drop user s1 cascade;
User dropped.
2. 创建帐号s,s1
SQL> create user s identified by password;
User created.
SQL> grant create session,resource to s;
Grant succeeded.
SQL> create user s1 identified by password;
User created.
SQL> grant create session ,resource to s1;
Grant succeeded.
3.给s赋给对象权限,with grant option,表示权限可以传递
SQL> grant select on hr.employees to s with grant option;
Grant succeeded.
SQL> conn s/password
Connected.
4. 传递权限
SQL> grant select on hr.employees to s1;
Grant succeeded.
SQL> conn s1/password
Connected.
SQL> select last_name from hr.employees
2 where employee_id=118;
LAST_NAME
-------------------------
Himuro
SQL> conn hr/hr
Connected.
5. 收回s的对象权限
SQL> revoke select on hr.employees from s;
Revoke succeeded.
6. s1帐号中从s传递的权限也被收回
SQL> conn s1/password;
Connected.
SQL> select last_name from hr.employees
2 where employee_id=118;
select last_name from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
注意:当收回s的权限时,s1的权限也不能使用