Re: Who can grant Object Permissions ?

From: Stuart Turton <sturton_at_maderich.demon.co.uk>
Date: Sun, 24 Mar 2002 01:39:09 GMT
Message-ID: <3C9CFC81.3010301@maderich.demon.co.uk>

mdlbikes wrote:

> Yes, I have already found the change password solution on a news
> group.Buy, I didn't like it. That is why I posted this mail.
>
> I have to get more familiar with the concepts you are talking about
> (dbms_sql) to understand the second option. It sound much safer.
>
> Thank You Both.
> Marcelo
>

Or use this method that I found here. The gu who first described it looked at what WebDB was doing in the background. Apologies for the (lack of ) coding style.

Create the execute_as_user() procedure in an account that is trusted to execute the sys.dbms_sys_sql package (for me no-one, so it got created in the sys schema), then create the clone_user() procedure as anyone you trust to run that procedure (for me system)

CREATE OR REPLACE PROCEDURE execute_as_user( UID IN NUMBER, SqlTxt IN VARCHAR2 ) AS
c INTEGER;
BEGIN

	c:=sys.Dbms_Sys_Sql.open_cursor();
	sys.Dbms_Sys_Sql.parse_as_user(
		c,
		sqltxt ,
		Dbms_Sql.native,
		UID
	);
	sys.Dbms_Sys_Sql.close_cursor(c);

EXCEPTION
WHEN OTHERS THEN

	 IF sys.Dbms_Sys_Sql.is_open(c)
	 THEN
	 
	 
	sys.Dbms_Sys_Sql.close_cursor(c);
	 END IF;

END execute_as_user ;
/

Then use it as in the procedure below.

CREATE OR REPLACE PROCEDURE Clone_User (template_user USER_USERS.username%TYPE

                      ,new_user USER_USERS.username%TYPE
					 , test_account VARCHAR2 := 'FALSE'
					  )

AUTHID CURRENT_USER
AS
CURSOR c_ddl_string (p_user USER_USERS.username%TYPE)IS SELECT 'CREATE USER ' || new_user
|| ' IDENTIFIED '|| DECODE (password, 'EXTERNAL', 'EXTERNALLY'

                                     --,'GLOBAL','GLOBALLY AS ' || 
external_name
                                     ,'BY ' || /* Anything else reset 
password to username */ new_user
                          )
||'  DEFAULT TABLESPACE ' || default_tablespace
|| ' TEMPORARY TABLESPACE ' || temporary_tablespace

|| DECODE(PROFILE, NULL ,'', ' PROFILE ' || PROFILE) || DECODE(test_account,'FALSE',' ACCOUNT LOCK '

								|| DECODE (password
								   ,'EXTERNAL', ''
								   ,'GLOBAL',''
								   ,'PASSWORD EXPIRE ' /* If the template user has a password expire the new 
users */
								   )
		 , ''
	     )

grant_statement
FROM sys.DBA_USERS
WHERE username = p_user
UNION ALL
SELECT 'ALTER USER ' || new_user

        ||' QUOTA ' ||  DECODE(max_bytes, -1,'UNLIMITED',max_bytes)
	   || ' ON ' || tablespace_name

FROM sys.DBA_TS_QUOTAS
WHERE username = p_user
UNION ALL
SELECT
'BEGIN ' || CHR(10)

||'Dbms_Resource_Manager_Privs.grant_switch_consumer_group('
||'grantee_name => '''||new_user||''','
||'consumer_group => '''||granted_group||''','
||'grant_option => '|| DECODE(grant_option,'YES','TRUE','FALSE')
||');'
||DECODE(initial_group,'NO','',

CHR(10)||'Dbms_Resource_Manager.set_initial_consumer_group('

||'USER => '''||new_user||''','
||'consumer_group => '''||granted_group||''''
||'); '

)
|| CHR(10) ||'END; '
FROM sys.DBA_RSRC_CONSUMER_GROUP_PRIVS
WHERE Grantee = p_user
UNION ALL
SELECT 'GRANT ' || granted_role

        ||' TO ' || new_user
        || ' ' || DECODE(admin_option,'Y',' WITH ADMIN OPTION','')

FROM sys.DBA_ROLE_PRIVS
WHERE Grantee = p_user
AND granted_role != 'PUBLIC' -- This 'Magic' Role is allocated automatically
UNION ALL
SELECT 'GRANT ' || privilege

        ||' TO ' || new_user
        || ' ' || DECODE(admin_option,'Y','WITH ADMIN OPTION','')

FROM sys.DBA_SYS_PRIVS
WHERE Grantee = p_user
;
/*
It is not possible to directly make
grants on another schema's objects
TO another USER
SO, bypass it
*/
CURSOR c_object_grants (p_user USER_USERS.username%TYPE) IS SELECT u.user_ID, g.grant_statement
FROM (
SELECT owner, 'GRANT ' || privilege ||' ON ' || owner ||'.'||table_name

        ||' TO ' || new_user
        || ' ' || DECODE(grantable,'Y','WITH GRANT OPTION','') 

grant_statement
FROM sys.DBA_TAB_PRIVS
WHERE Grantee = p_user
UNION ALL
SELECT owner, 'GRANT ' || privilege

        || DECODE(column_name,NULL,'','('||column_name||')')
        ||' ON ' || owner ||'.'||table_name
        ||' TO ' || new_user
        || ' ' || DECODE(grantable,'Y','WITH GRANT OPTION','')

FROM sys.DBA_COL_PRIVS
WHERE Grantee = p_user
UNION ALL
SELECT UPPER(new_user), 'CREATE SYNONYM ' || new_user || '.' || synonym_name

       || ' FOR ' || table_owner || '.' || table_name
	  || DECODE(db_link,NULL,'','','','@'||db_link)

FROM sys.DBA_SYNONYMS
WHERE owner = p_user
) g
, sys.DBA_USERS u
WHERE g.owner = u.username
;
/*
Collect all default roles for the template user into a comma separated string
Use dbma_utility.table_to_comma()
*/
CURSOR c_default_roles (p_user USER_USERS.username%TYPE)IS SELECT granted_role
FROM sys.DBA_ROLE_PRIVS
WHERE default_role= 'YES'
AND Grantee = p_user
AND granted_role != 'PUBLIC' -- This 'Magic' Role is allocated automatically ORDER BY granted_role
;
TYPE string_array IS TABLE OF VARCHAR2(512) INDEX BY BINARY_INTEGER; --TYPE t_string IS TABLE OF c_ddl_string.grant_statement%TYPE INDEX BY BINARY_INTEGER;
TYPE user_id_array IS TABLE OF sys.DBA_USERS.user_id%TYPE INDEX BY BINARY_INTEGER;

t_grant string_ARRAY;
t_role  Dbms_Utility.UNCL_ARRAY;
t_user_id user_id_array;

roles_defaulted INTEGER;
default_roles VARCHAR2(2000);
BEGIN

	/*
		Perform BULK COLLECT of required DDL statements into PL/SQL table
		1 trip to the database (new in Oracle 8i)
	*/
	OPEN c_ddl_string (UPPER(template_user));
	FETCH c_ddl_string BULK COLLECT INTO t_grant;
	IF t_grant.FIRST IS NULL
	THEN
		Dbms_Output.put_line('No grants exist FOR the template 
USER:"'||template_user||'"');
	ELSE
		FOR i IN t_grant.FIRST .. t_grant.LAST
		LOOP
			Dbms_Output.put_line(t_grant(i) ) ;
			BEGIN
			EXECUTE IMMEDIATE t_grant(i) ;
			EXCEPTION WHEN OTHERS THEN
					Dbms_Output.put_line(t_grant(i) ||'==>'
					            ||SQLERRM
								);
			END;
		END LOOP;
	END IF;
	/*
		Perform BULK COLLECT of template user's DEFAULT ROLES INTO PL/SQL TABLE
		1 trip TO the DATABASE (NEW IN Oracle 8i)
	*/
	OPEN c_default_roles (UPPER(template_user));
	FETCH c_default_roles BULK COLLECT INTO t_role;
	IF t_role.FIRST IS NULL
	THEN
		Dbms_Output.put_line('No default roles exist for the template 
user:"'||template_user||'"');
	ELSE
		/*
		Convert the table of default roles into a comma separated list
		*/
		Dbms_Utility.table_to_comma(t_role, roles_defaulted, default_roles) ;
		IF t_role.LAST != roles_defaulted
		THEN
			RAISE_APPLICATION_ERROR(-20001,'Default roles in table and list are not the 
same');
		END IF;
		/*
		 Grant user the template user's DEFAULT ROLES USING
		 Native Dynamic  SQL (NEW IN 8i)
		 */
		Dbms_Output.put_line(--DEBUG
			'ALTER USER ' || new_user
	    		||' DEFAULT ROLE ' || default_roles
	    	)
	    	;
		BEGIN
		EXECUTE IMMEDIATE
			'ALTER USER ' || new_user
	    		||' DEFAULT ROLE ' || default_roles
	    	;
			EXCEPTION WHEN OTHERS THEN
					Dbms_Output.put_line('ALTER USER ' || new_user
						    		||' DEFAULT ROLE ' || default_roles
									 ||'==>'
		                                                ||SQLERRM
								);
			END;
	END IF;
	OPEN c_object_grants (UPPER(template_user));
	FETCH c_object_grants BULK COLLECT INTO t_user_id, t_grant;
	IF t_grant.FIRST IS NULL
	THEN
		Dbms_Output.put_line('No object grants exist FOR the template 
USER:"'||template_user||'"');
	ELSE
		FOR i IN t_grant.FIRST .. t_grant.LAST
		LOOP
			Dbms_Output.put_line(t_user_id(i)||','|| t_grant(i) );
			BEGIN
			sys.Execute_As_User(t_user_id(i), t_grant(i) );
			EXCEPTION WHEN OTHERS THEN
					Dbms_Output.put_line(t_grant(i) ||'==>'
					            ||SQLERRM
								);
			END;
		END LOOP;
	END IF;
EXCEPTION WHEN OTHERS
	THEN
		IF c_ddl_string%ISOPEN
		THEN
			CLOSE c_ddl_string;
		END IF;

		IF c_default_roles%ISOPEN
		THEN
			CLOSE c_default_roles;
		END IF;

	RAISE_APPLICATION_ERROR (-20001,'Clone_User: unexpected problem'
	                               ,keeperrorstack =>TRUE
	                             );
	RAISE ;

END Clone_User;
/ Received on Sat Mar 23 2002 - 19:39:09 CST