Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6829
  • Last Modified:

grants after schema import

Hello members,
Can someone please answer this and clear my confusion. I had to refresh some schemas in test from production on this oracle 10g database. I dropped the schemas, imported them using data pump. I then created a grant file using the same dump file and ran the grant file as sys. Is this enough to create the grants? What needs to be done to make sure that all these schema grants were all the same as before the import.... I am asking this because one of the application logics is not working and I am still not sure if it is a role/grant thing that is causing this.
0
dbguy0
Asked:
dbguy0
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
Shaju KumbalathDeputy General Manager - ITCommented:
COMPARE THE RESULTS OF FOLLOWING QUERY IN BOTH THE PRODUCTION AND TEST TATABASES. PLEASE REPLACE WITH THE SCHEMA NAME
 

Select PRIVILEGE, GRANTEE, GRANTABLE, GRANTOR
from dba__privs
where
owner='SCOTT'
order by grantee
0
 
Franck PachotCommented:
Hi,
Here is a way to get create user DLL with all grants:

set long 10000
select
  dbms_metadata.get_ddl ('USER',username)
 ,dbms_metadata.get_granted_ddl ('SYSTEM_GRANT',username)
 ,dbms_metadata.get_granted_ddl ('ROLE_GRANT',username)
 ,dbms_metadata.get_granted_ddl ('OBJECT_GRANT',username)
 ,dbms_metadata.get_granted_ddl ('DEFAULT_ROLE',username)
from all_users where username=user;

Regards,
Franck.

0
 
logictankCommented:
A few ways you can do this:
Run a full import to a sqlfile to get the missing grants, you could then run them manually against the schema (Looks like you may have already tried this):
impdp full=y dumpfile=schema1.dmp sqlfile=schema1.sql INCLUDE=GRANT

Or run a full import of all grants:

impdp full=y dumpfile=schema1.dmp  INCLUDE=GRANT
Also verify that any Public Synonyms that your application might depend on that were created in your PRODUCTION instance are also created in your TEST instance.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dbguy0Author Commented:
Thanks.. If I run the grant file (from include=grant) that should cover all grants/privileges include system grants right. Also I dont need to edit the grant file right? I ran the grant file as sys.
0
 
logictankCommented:
Correct, the full import to a sqlfile will give you DDL for all object level grants from granter and grantee objects including system privileges but you will lose grants on 'sys' objects. You would need to use a sql script to extract object level grants on sys objects and recreate them in the target schemas if necessary.  You can use the sql above from 'franckpachot' to extract the DDL statements for 'sys' objects.
0
 
dbguy0Author Commented:
Hello,

Thanks for your clarifications I appreciate it. That means after I run the sql file, the below statement that retrieves
Object grants from sys should suffice right?

dbms_metadata.get_granted_ddl ('OBJECT_GRANT','BHJ') FROM DUAL;


For this schema 'BHJ' it results in two grants. Whereas the second query shown below that queries the dba_tab_privs is resulting in six grants...



SQL> SELECT dbms_metadata.get_granted_ddl ('OBJECT_GRANT','BHJ') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','BHJ')
--------------------------------------------------------------------------------


  GRANT EXECUTE ON "SYS"."UTL_FILE" TO "BHJ"

  GRANT EXECUTE ON "SYS"."UTL_SM


SQL> Select 'grant '||privilege||' on '||table_name||' to '||grantee||';'
  2  from dba_tab_privs
  3  where
  4  owner='SYS'
  5  AND grantee='BHJ';

'GRANT'||PRIVILEGE||'ON'||TABLE_NAME||'TO'||GRANTEE||';'
--------------------------------------------------------------------------------

grant WRITE on BASE to BHJ;
grant READ on BASE to BHJ;
grant EXECUTE on UTL_FILE to BHJ;
grant EXECUTE on UTL_SMTP to BHJ;
grant EXECUTE on DBMS_RLS to BHJ;
grant EXECUTE on DBMS_RANDOM to BHJ;

6 rows selected.


0
 
Franck PachotCommented:
The result is a long and you see only part of it.
in sqlplus, do the following before:
set long 1000000
Regards,
Franck.
0
 
dbguy0Author Commented:
Hi, before i close the question, the script ran well however, there are no semicolons or / after each grant statement. They are only appearing after a block of grant statements. I dont think all statements will run. I tried adding a pipe and a semicolon or / after each line but it did not work. The script doesn't output them....

dbms_metadata.get_granted_ddl ('SYSTEM_GRANT',username)

Any ideas? Thanks..
0
 
logictankCommented:
Do the following to set the SQL Terminator (;):
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

SQL> set long 100000

SQL> SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','SCOTT') FROM DUAL;

Sample Output:

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
--------------------------------------------------------------------------------
  GRANT CREATE TYPE TO "SCOTT";
  GRANT CREATE MATERIALIZED VIEW TO "SCOTT";
  GRANT CREATE VIEW TO "SCOTT";
  GRANT CREATE TABLE TO "SCOTT";
  GRANT UNLIMITED TABLESPACE TO "SCOTT";

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
--------------------------------------------------------------------------------
  GRANT CREATE SESSION TO "SCOTT";
 
SQL>
0
 
dbguy0Author Commented:
Gentlemen, Thank you so much for your answers and help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now