比如 A 用户创建了数据库tab1,创建表a,写了个packages 如下:
CREATE OR REPLACE PACKAGE pack_emsno AS
PROCEDURE pro_temp
(
........... );
END pack_emsno;
/
如何建一个用户B 对数据库tab1只有select 权限,和调用存储过程的权限?
CREATE OR REPLACE PACKAGE pack_emsno AS
PROCEDURE pro_temp
(
........... );
END pack_emsno;
/
如何建一个用户B 对数据库tab1只有select 权限,和调用存储过程的权限?
如何建一个用户B 对数据库tab1只有select 权限,和调用存储过程的权限?问题本身就是答案吧
在oracle里面可以设置这样的用户的
grant select on tab1 to B
1.Get All A user's table List
2.For each Table Dynamic grant select to B3.Get All A user's Procedure list
4.For each Table Dynamic grant "Execute" to B
( Fromuser IN Varchar2,
ToUser IN Varchar2 )
IS
TabName User_Tables.table_name%TYPE;
Cursor Tablist is select TABLE_NAME from All_Tables A where A.owner = FromUser;
BEGIN
Open TabList;
LOOP
fetch Tablist into TabName;
Execute immediate 'Grant SELECT ON ' || TabName || ' to ' || ToUser;
--DBMS_SQL.execute() Grant TabName to ToUser
EXIT WHEN TABLIST%NOTFOUND;
END LOOP;
Close TabList;
null;
END; -- Procedure
( Fromuser IN Varchar2,
ToUser IN Varchar2 )
IS
ObjName All_Objects.OBJECT_name%TYPE;
Cursor Proclist is select OBJECT_NAME from All_Objects A where A.owner = FromUser and A.Object_TYPE = 'PROCEDURE';
BEGIN
Open ProcList;
LOOP
fetch Proclist into ObjName;
Execute immediate 'Grant execute ON ' || ObjName || ' to ' || ToUser;
EXIT WHEN ProcLIST%NOTFOUND;
END LOOP;
Close ProcList;
null;
END;