这就是Oracle的三个表 SELECT * FROM dba_users; SELECT * FROM dba_roles; SELECT * FROM dba_role_privs;
我感觉要设计两个表,两个有中间量role,然后写两个符合要求的查询语句,就是具体的不知道怎么写
那么你是打算设置成这样了,没有实体表,只有两个关系表user_roles, role_privs对吗?虽然不符合第三范式,但是对这种课程设计已经足够了。 select p.priv from user_roles u, role_privs p where u.role = p.role and u.username = 'ZHANGS'; select u.username, count(p.priv) from user_roles u, role_privs p where u.role = p.role group by u.username having count(p.priv) > 3;
create table user_roles( username varchar2(20), role varchar2(20)); create table role_privs( role varchar2(20), priv varchar2(20));
SELECT * FROM dba_users;
SELECT * FROM dba_roles;
SELECT * FROM dba_role_privs;
那么你是打算设置成这样了,没有实体表,只有两个关系表user_roles, role_privs对吗?虽然不符合第三范式,但是对这种课程设计已经足够了。
select p.priv from user_roles u, role_privs p where u.role = p.role and u.username = 'ZHANGS';
select u.username, count(p.priv) from user_roles u, role_privs p where u.role = p.role group by u.username having count(p.priv) > 3;
create table role_privs( role varchar2(20), priv varchar2(20));