Home » RDBMS Server » Security » GRANT inside procedure
GRANT inside procedure [message #4111] Sat, 09 November 2002 09:37 Go to next message
Denis
Messages: 8
Registered: December 2000
Junior Member
Hello, I was wondering if it is possible to do grant statements or specifying roles inside stored procedures.
I want to make a procedure that would distribute rights to its callers. I.e I would create a procedure as an administrator, then when it is called with user id I want to GRANT certain rights to a person with such login.
Re: GRANT inside procedure [message #4117 is a reply to message #4111] Sat, 09 November 2002 16:44 Go to previous messageGo to next message
Trifon Anguelov
Messages: 514
Registered: June 2002
Senior Member
User DBMS_SQL package. It enables you to parse any data manipulation language (DML) or data definition language (DDL) statement. Therefore, you can parse DDL statements directly using PL/SQL.

Here is an example from the documentation:

CREATE PROCEDURE income(amount number)
AUTHID current_user IS
c number;
n number;
BEGIN
c:= dbms_sql.open_cursor;
dbms_sql.parse(c, 'insert into accts(''income'', :1)', dbms_sql.native);
dbms_sql.bind_variable(c, '1', amount);
n := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
END;

Hope that helps,

clio_usa
OCP - DBA

Visit our Web site

Re: GRANT inside procedure [message #4120 is a reply to message #4117] Sat, 09 November 2002 23:01 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Much easier to use native dynamic SQL:

execute immediate 'grant some_role to ' || p_user;
Previous Topic: ORACLE SECURITY - URGENT!!
Next Topic: SECURITY on PUBLIC GRANTEES (Urgent)
Goto Forum:
  


Current Time: Mon Oct 18 06:44:38 CDT 2021