Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Who can grant Object Permissions ?
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);
IF sys.Dbms_Sys_Sql.is_open(c) THEN sys.Dbms_Sys_Sql.close_cursor(c); END IF;
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' )
--,'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 */ ) , '' )
||' QUOTA ' || DECODE(max_bytes, -1,'UNLIMITED',max_bytes) || ' ON ' || tablespace_name
||'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','',
||'USER => '''||new_user||''',' ||'consumer_group => '''||granted_group||'''' ||'); '
||' TO ' || new_user || ' ' || DECODE(admin_option,'Y',' WITH ADMIN OPTION','')FROM sys.DBA_ROLE_PRIVS
||' TO ' || new_user || ' ' || DECODE(admin_option,'Y','WITH ADMIN OPTION','')FROM sys.DBA_SYS_PRIVS
||' TO ' || new_user || ' ' || DECODE(grantable,'Y','WITH GRANT OPTION','')grant_statement
|| DECODE(column_name,NULL,'','('||column_name||')') ||' ON ' || owner ||'.'||table_name ||' TO ' || new_user || ' ' || DECODE(grantable,'Y','WITH GRANT OPTION','')FROM sys.DBA_COL_PRIVS
|| ' FOR ' || table_owner || '.' || table_name || DECODE(db_link,NULL,'','','','@'||db_link)FROM sys.DBA_SYNONYMS
t_grant string_ARRAY; t_role Dbms_Utility.UNCL_ARRAY; t_user_id user_id_array;
/* 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 ;