How to use the DBMS_SQL Package In SQL
For a more programmatic approach to user privilege management in PL/SQL, the DBMS_SQL Package is used. This package allows for creating and running SQL statements within PL/SQL that are dynamic.
DECLARE
sql_cursor INTEGER;
sql_stmt VARCHAR2(100);
BEGIN
sql_stmt := 'GRANT DBA TO anya';
sql_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(sql_cursor, sql_stmt, DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR(sql_cursor);
END;
Output:
Using the DBMS_SQL Package
Explanation: In this dynamic example, the GRANT command assigns the DBA role to user “Anya”. The statement is parsed using the DBMS_SQL.PARSE procedure and then closed cursor.
How to Create a User With Full Privileges in PL/SQL?
In database administration, the ability to manage user privileges is very important. As a database administrator, we may find ourselves in the position of creating users with full privileges and granting them the authority to manage and manipulate the database.
In this article, we will learn about CREATE USERS and give them GRANT or Privileges to access information using various methods and examples too.