130 lines
4.6 KiB
SQL
130 lines
4.6 KiB
SQL
-- +----------------------------------------------------------------------------+
|
|
-- | Jeffrey M. Hunter |
|
|
-- | jhunter@idevelopment.info |
|
|
-- | www.idevelopment.info |
|
|
-- |----------------------------------------------------------------------------|
|
|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
|
|
-- |----------------------------------------------------------------------------|
|
|
-- | DATABASE : Oracle |
|
|
-- | FILE : plsql_webdba_utl_pkg.sql |
|
|
-- | CLASS : PL/SQL |
|
|
-- | PURPOSE : Example PL/SQL package that shows how to use dynamic SQL before |
|
|
-- | Oracle8i. |
|
|
-- | NOTE : As with any code, ensure to test this script in a development |
|
|
-- | environment before attempting to run it in production. |
|
|
-- +----------------------------------------------------------------------------+
|
|
|
|
CREATE OR REPLACE PACKAGE webdba_util AS
|
|
|
|
PROCEDURE drop_user(username_in IN VARCHAR2);
|
|
PROCEDURE create_user(username_in IN VARCHAR2,
|
|
default_tablespace_in IN VARCHAR2,
|
|
temporary_tablespace_in IN VARCHAR2,
|
|
password_in IN VARCHAR2);
|
|
PROCEDURE change_user_password(username_in IN VARCHAR2,
|
|
password_in IN VARCHAR2);
|
|
PROCEDURE grant_user(username_in IN VARCHAR2,
|
|
privilege_in IN VARCHAR2);
|
|
PROCEDURE revoke_user(username_in IN VARCHAR2,
|
|
privilege_in IN VARCHAR2);
|
|
|
|
END webdba_util;
|
|
/
|
|
show errors
|
|
|
|
CREATE OR REPLACE PACKAGE BODY webdba_util AS
|
|
|
|
PROCEDURE drop_user(username_in IN VARCHAR2) IS
|
|
v_Cursor NUMBER;
|
|
v_DropUserString VARCHAR2(500);
|
|
v_Results INTEGER;
|
|
BEGIN
|
|
|
|
v_Cursor := DBMS_SQL.open_cursor;
|
|
v_DropUserString := 'DROP USER ' || username_in || ' CASCADE';
|
|
|
|
DBMS_SQL.PARSE(v_Cursor, v_DropUserString, DBMS_SQL.V7);
|
|
v_Results := DBMS_SQL.EXECUTE(v_Cursor);
|
|
|
|
DBMS_SQL.close_cursor(v_Cursor);
|
|
|
|
END drop_user;
|
|
|
|
PROCEDURE create_user(username_in IN VARCHAR2,
|
|
default_tablespace_in IN VARCHAR2,
|
|
temporary_tablespace_in IN VARCHAR2,
|
|
password_in IN VARCHAR2) IS
|
|
v_Cursor NUMBER;
|
|
v_DropUserString VARCHAR2(1500);
|
|
v_Results INTEGER;
|
|
BEGIN
|
|
|
|
v_Cursor := DBMS_SQL.open_cursor;
|
|
v_DropUserString := 'CREATE USER ' || username_in || ' DEFAULT TABLESPACE ' || default_tablespace_in ||
|
|
' TEMPORARY TABLESPACE ' || temporary_tablespace_in || ' IDENTIFIED BY ' || password_in;
|
|
|
|
DBMS_SQL.PARSE(v_Cursor, v_DropUserString, DBMS_SQL.V7);
|
|
v_Results := DBMS_SQL.EXECUTE(v_Cursor);
|
|
|
|
DBMS_SQL.close_cursor(v_Cursor);
|
|
|
|
END create_user;
|
|
|
|
|
|
PROCEDURE change_user_password(username_in IN VARCHAR2,
|
|
password_in IN VARCHAR2) IS
|
|
v_Cursor NUMBER;
|
|
v_AlterUserString VARCHAR2(500);
|
|
v_Results INTEGER;
|
|
BEGIN
|
|
|
|
v_Cursor := DBMS_SQL.open_cursor;
|
|
v_AlterUserString := 'ALTER USER ' || username_in || ' IDENTIFIED BY ' || password_in;
|
|
|
|
DBMS_SQL.PARSE(v_Cursor, v_AlterUserString, DBMS_SQL.V7);
|
|
v_Results := DBMS_SQL.EXECUTE(v_Cursor);
|
|
|
|
DBMS_SQL.close_cursor(v_Cursor);
|
|
|
|
END change_user_password;
|
|
|
|
PROCEDURE grant_user(username_in IN VARCHAR2,
|
|
privilege_in IN VARCHAR2) IS
|
|
v_Cursor NUMBER;
|
|
v_AlterUserString VARCHAR2(500);
|
|
v_Results INTEGER;
|
|
BEGIN
|
|
|
|
v_Cursor := DBMS_SQL.open_cursor;
|
|
v_AlterUserString := 'GRANT ' || privilege_in || ' TO ' || username_in;
|
|
|
|
DBMS_SQL.PARSE(v_Cursor, v_AlterUserString, DBMS_SQL.V7);
|
|
v_Results := DBMS_SQL.EXECUTE(v_Cursor);
|
|
|
|
DBMS_SQL.close_cursor(v_Cursor);
|
|
|
|
END grant_user;
|
|
|
|
PROCEDURE revoke_user(username_in IN VARCHAR2,
|
|
privilege_in IN VARCHAR2) IS
|
|
v_Cursor NUMBER;
|
|
v_AlterUserString VARCHAR2(500);
|
|
v_Results INTEGER;
|
|
BEGIN
|
|
|
|
v_Cursor := DBMS_SQL.open_cursor;
|
|
v_AlterUserString := 'REVOKE ' || privilege_in || ' FROM ' || username_in;
|
|
|
|
DBMS_SQL.PARSE(v_Cursor, v_AlterUserString, DBMS_SQL.V7);
|
|
v_Results := DBMS_SQL.EXECUTE(v_Cursor);
|
|
|
|
DBMS_SQL.close_cursor(v_Cursor);
|
|
|
|
END revoke_user;
|
|
|
|
|
|
END webdba_util;
|
|
/
|
|
|
|
show errors
|