?? ch17.htm
字號:
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The proper use of single quotation
marks when generating an SQL script is vital. Use these quotations generously and
make sure that you are including all elements that will make your generated statement
complete. In this example single quotation marks enclose the components of your generated
statement (output) that cannot be selected from a table; for example, <TT>'SELECT
COUNT(*) FROM'</TT> and <TT>';'</TT>.
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077">Granting System Privileges to Multiple Users</FONT></H2>
<P>As a database administrator or an individual responsible for maintaining users,
you will often receive requests for user IDs. In addition to having to grant privileges
to users that allow them proper database access, you also have to modify users' privileges
to accommodate their changing needs. You can get the database to generate the <TT>GRANT</TT>
statements to grant system privileges or roles to many users.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SET ECHO OFF</B>
SQL> <B>SET HEADING OFF</B>
SQL> <B>SET FEEDBACK OFF</B>
SQL> <B>SPOOL GRANTS.SQL</B>
SQL> <B>SELECT 'GRANT CONNECT, RESOURCE TO ' || USERNAME || ';'</B>
2 <B> FROM SYS.DBA_USERS</B>
3 <B>WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT','RYAN','PO7','DEMO')</B>
4 <B> /</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">GRANT CONNECT, RESOURCE TO KEVIN;
GRANT CONNECT, RESOURCE TO JOHN;
GRANT CONNECT, RESOURCE TO JUDITH;
GRANT CONNECT, RESOURCE TO STEVE;
GRANT CONNECT, RESOURCE TO RON;
GRANT CONNECT, RESOURCE TO MARY;
GRANT CONNECT, RESOURCE TO DEBRA;
GRANT CONNECT, RESOURCE TO CHRIS;
GRANT CONNECT, RESOURCE TO CAROL;
GRANT CONNECT, RESOURCE TO EDWARD;
GRANT CONNECT, RESOURCE TO BRANDON;
GRANT CONNECT, RESOURCE TO JACOB;</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>spool off</B>
SQL> <B>start grants.sql</B>
SQL> GRANT CONNECT, RESOURCE TO KEVIN;
Grant succeeded.
SQL> GRANT CONNECT, RESOURCE TO JOHN;
Grant succeeded.
SQL> GRANT CONNECT, RESOURCE TO JUDITH;
Grant succeeded.
SQL> GRANT CONNECT, RESOURCE TO STEVE;
Grant succeeded.
SQL> GRANT CONNECT, RESOURCE TO RON;
Grant succeeded.
SQL> GRANT CONNECT, RESOURCE TO MARY;
Grant succeeded.
SQL> GRANT CONNECT, RESOURCE TO DEBRA;
Grant succeeded.
SQL> GRANT CONNECT, RESOURCE TO CHRIS;
Grant succeeded.
SQL> GRANT CONNECT, RESOURCE TO CAROL;
Grant succeeded.
SQL> GRANT CONNECT, RESOURCE TO EDWARD;
Grant succeeded.
SQL> GRANT CONNECT, RESOURCE TO BRANDON;
Grant succeeded.
SQL> GRANT CONNECT, RESOURCE TO JACOB;
Grant succeeded.</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>In this example you saved many tedious keystrokes by generating<B> </B><TT>GRANT</TT>
statements using a simple SQL statement, rather than typing each one manually.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The following examples omit the
step in which you edit your output file. You can assume that the files are already
edited.
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077">Granting Privileges on Your Tables to Another User</FONT></H2>
<P>Granting privileges on a table to another user is quite simple, as is selecting
a row count on a table. But if you have multiple tables to which you wish to grant
access to a role or user, you can make SQL generate a script for you--unless you
just love to type.</P>
<P>First, review a simple <TT>GRANT</TT> to one table:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>GRANT SELECT ON HISTORY TO BRANDON;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">Grant succeeded.
</FONT></PRE>
<P>Are you ready for some action? The next statement creates a <TT>GRANT</TT> statement
for each of the 10 tables in your schema.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SET ECHO OFF</B>
SQL> <B>SET FEEDBACK OFF</B>
SQL> <B>SET HEADING OFF</B>
SQL> <B>SPOOL GRANTS.SQL</B>
SQL> <B>SELECT 'GRANT SELECT ON ' || TABLE_NAME || ' TO BRANDON;'</B>
2 <B>FROM CAT</B>
3 <B> /</B>
GRANT SELECT ON ACCT_PAY TO BRANDON;
GRANT SELECT ON ACCT_REC TO BRANDON;
GRANT SELECT ON CUSTOMERS TO BRANDON;
GRANT SELECT ON EMPLOYEES TO BRANDON;
GRANT SELECT ON HISTORY TO BRANDON;
GRANT SELECT ON INVOICES TO BRANDON;
GRANT SELECT ON ORDERS TO BRANDON;
GRANT SELECT ON PRODUCTS TO BRANDON;
GRANT SELECT ON PROJECTS TO BRANDON;
GRANT SELECT ON VENDORS TO BRANDON;
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>A <TT>GRANT</TT> statement has been automatically prepared for each table. BRANDON
is to have Select access on each table.</P>
<P>Now close the output file with the <TT>SPOOL</TT> command, and assuming that the
file has been edited, the file is ready to run.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SPOOL OFF</B>
SQL> <B>SET ECHO ON</B>
SQL> <B>SET FEEDBACK ON</B>
SQL> <B>START GRANTS.SQL</B>
SQL> GRANT SELECT ON ACCT_PAY TO BRANDON;
Grant succeeded.
SQL> GRANT SELECT ON ACCT_REC TO BRANDON;
Grant succeeded.
SQL> GRANT SELECT ON CUSTOMERS TO BRANDON;
Grant succeeded.
SQL> GRANT SELECT ON EMPLOYEES TO BRANDON;
Grant succeeded.
SQL> GRANT SELECT ON HISTORY TO BRANDON;
Grant succeeded.
SQL> GRANT SELECT ON INVOICES TO BRANDON;
Grant succeeded.
SQL> GRANT SELECT ON ORDERS TO BRANDON;
Grant succeeded.
SQL> GRANT SELECT ON PRODUCTS TO BRANDON;
Grant succeeded.
SQL> GRANT SELECT ON PROJECTS TO BRANDON;
Grant succeeded.
SQL> GRANT SELECT ON VENDORS TO BRANDON;
Grant succeeded.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Echo was set on and feedback was set on as well. Setting feedback on displayed
the statement <TT>Grant succeeded</TT>. The Select privilege has been granted to
BRANDON on all 10 tables with very little effort. Again, keep in mind that you will
often be dealing with many more than 10 tables.
<H2><FONT COLOR="#000077">Disabling Table Constraints to Load Data</FONT></H2>
<P>When loading data into tables, you will sometimes have to disable the constraints
on your tables. Suppose that you have truncated your tables and you are loading data
into your tables from scratch. More than likely, your tables will have referential
integrity constraints, such as foreign keys. Because the database will not let you
insert a row of data in a table that references another table (if the referenced
column does not exist in the other table), you may have to disable constraints to
initially load your data. Of course, after the load is successful, you would want
to enable the constraints.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SET ECHO OFF</B>
SQL> <B>SET FEEDBACK OFF</B>
SQL> <B>SET HEADING OFF</B>
SQL> <B>SPOOL DISABLE.SQL</B>
SQL> <B>SELECT 'ALTER TABLE ' || TABLE_NAME ||</B>
2 <B> 'DISABLE CONSTRAINT ' || CONSTRAINT_NAME || ';'</B>
3 <B>FROM SYS.DBA_CONSTRAINTS</B>
4 <B>WHERE OWNER = 'RYAN'</B>
5 <B> /</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">ALTER TABLE ACCT_PAY DISABLE CONSTRAINT FK_ACCT_ID;
ALTER TABLE ACCT_REC DISABLE CONSTRAINT FK_ACCT_ID;
ALTER TABLE CUSTOMERS DISABLE CONSTRAINT FK_CUSTOMER_ID;
ALTER TABLE HISTORY DISABLE CONSTRAINT FK_ACCT_ID;
ALTER TABLE INVOICES DISABLE CONSTRAINT FK_ACCT_ID;
ALTER TABLE ORDERS DISABLE CONSTRAINT FK_ACCT_ID;
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The objective is to generate a series of <TT>ALTER TABLE</TT> statements that
will disable the constraints on all tables owned by RYAN. The semicolon concatenated
to the end of what is being selected completes each SQL statement.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SPOOL OFF</B>
SQL> <B>SET ECHO OFF</B>
SQL><B> SET FEEDBACK ON</B>
SQL> <B>START DISABLE.SQL</B>
Constraint Disabled.
Constraint Disabled.
Constraint Disabled.
Constraint Disabled.
Constraint Disabled.
Constraint Disabled.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice that echo is set to <TT>off</TT>, which means that you will not see the
individual statements. Because feedback is set to <TT>on</TT>, you can see the results.</P>
<PRE><FONT COLOR="#0066FF">Constraint Disabled.
</FONT></PRE>
<P>If both echo and feedback were set to <TT>off</TT>, nothing would be displayed.
There would simply be a pause for as long as it takes to execute the <TT>ALTER TABLE</TT>
statements and then an <TT>SQL></TT> prompt would be returned.</P>
<P>Now you can load your data without worrying about receiving errors caused by your
constraints. Constraints are good, but they can be barriers during data loads. You
may use the same idea to enable the table constraints.
<H2><FONT COLOR="#000077">Creating Numerous Synonyms in a Single Bound</FONT></H2>
<P>Another tedious and exhausting task is creating numerous synonyms, whether they
be public or private. Only a DBA can create public synonyms, but any user can create
private synonyms.</P>
<P>The following example creates public synonyms for all tables owned by RYAN.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SET ECHO OFF</B>
SQL> <B>SET FEEDBACK OFF</B>
SQL> <B>SET HEADING OFF</B>
SQL> <B>SPOOL PUB_SYN.SQL</B>
SQL> <B>SELECT 'CREATE PUBLIC SYNONYM ' || TABLE_NAME || ' FOR ' ||</B>
2 <B> OWNER || '.' || TABLE_NAME || ';'</B>
3 <B>FROM SYS.DBA_TABLES</B>
4 <B> WHERE OWNER = 'RYAN'</B>
5 <B>/</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">CREATE PUBLIC SYNONYM ACCT_PAY FOR RYAN.ACCT_PAY;
CREATE PUBLIC SYNONYM ACCT_REC FOR RYAN.ACCT_REC;
CREATE PUBLIC SYNONYM CUSTOMERS FOR RYAN.CUSTOMERS;
CREATE PUBLIC SYNONYM EMPLOYEES FOR RYAN.EMPLOYEES;
CREATE PUBLIC SYNONYM HISTORY FOR RYAN.HISTORY;
CREATE PUBLIC SYNONYM INVOICES FOR RYAN.INVOICES;
CREATE PUBLIC SYNONYM ORDERS FOR RYAN.ORDERS;
CREATE PUBLIC SYNONYM PRODUCTS FOR RYAN.PRODUCTS;
CREATE PUBLIC SYNONYM PROJECTS FOR RYAN.PROJECTS;
CREATE PUBLIC SYNONYM VENDORS FOR RYAN.VENDORS;
</FONT></PRE>
<P>Now run the file.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SPOOL OFF</B>
SQL> <B>ED PUB_SYN.SQL</B>
SQL> <B>SET ECHO ON</B>
SQL> <B>SET FEEDBACK ON</B>
SQL> <B>START PUB_SYN.SQL</B>
SQL> CREATE PUBLIC SYNONYM ACCT_PAY FOR RYAN.ACCT_PAY;
Synonym created.
SQL> CREATE PUBLIC SYNONYM ACCT_REC FOR RYAN.ACCT_REC;
Synonym created.
SQL> CREATE PUBLIC SYNONYM CUSTOMERS FOR RYAN.CUSTOMERS;
Synonym created.
SQL> CREATE PUBLIC SYNONYM EMPLOYEES FOR RYAN.EMPLOYEES;
Synonym created.
SQL> CREATE PUBLIC SYNONYM HISTORY FOR RYAN.HISTORY;
Synonym created.
SQL> CREATE PUBLIC SYNONYM INVOICES FOR RYAN.INVOICES;
Synonym created.
SQL> CREATE PUBLIC SYNONYM ORDERS FOR RYAN.ORDERS;
Synonym created.
SQL> CREATE PUBLIC SYNONYM PRODUCTS FOR RYAN.PRODUCTS;
Synonym created.
SQL> CREATE PUBLIC SYNONYM PROJECTS FOR RYAN.PROJECTS;
Synonym created.
SQL> CREATE PUBLIC SYNONYM VENDORS FOR RYAN.VENDORS;
Synonym created.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Almost instantly, all database users have access to a public synonym for all tables
that RYAN owns. Now a user does not need to qualify the table when performing a <TT>SELECT</TT>
operation. (Qualifying means identifying the table owner, as in <TT>RYAN.VENDORS</TT>.)</P>
<P>What if public synonyms do not exist? Suppose that BRANDON has Select access to
all tables owned by RYAN and wants to create private synonyms.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>CONNECT BRANDON</B>
ENTER PASSWORD: <B>*******</B>
CONNECTED.
SQL> <B>SET ECHO OFF</B>
SQL> <B>SET FEEDBACK OFF</B>
SQL> <B>SET HEADING OFF</B>
SQL> <B>SPOOL PRIV_SYN.SQL</B>
SQL> <B>SELECT 'CREATE SYNONYM ' || TABLE_NAME || ' FOR ' ||</B>
2 <B> OWNER || '.' || TABLE_NAME || ';'</B>
3 <B>FROM ALL_TABLES</B>
4 <B> /</B>
CREATE SYNONYM DUAL FOR SYS.DUAL;
CREATE SYNONYM AUDIT_ACTIONS FOR SYS.AUDIT_ACTIONS;
CREATE SYNONYM USER_PROFILE FOR SYSTEM.USER_PROFILE;
CREATE SYNONYM CUSTOMERS FOR RYAN.CUSTOMERS;
CREATE SYNONYM ORDERS FOR RYAN.ORDERS;
CREATE SYNONYM PRODUCTS FOR RYAN.PRODUCTS;
CREATE SYNONYM INVOICES FOR RYAN.INVOICES;
CREATE SYNONYM ACCT_REC FOR RYAN.ACCT_REC;
CREATE SYNONYM ACCT_PAY FOR RYAN.ACCT_PAY;
CREATE SYNONYM VENDORS FOR RYAN.VENDORS;
CREATE SYNONYM EMPLOYEES FOR RYAN.EMPLOYEES;
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -