| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Assigning a role in Stored Procedure
A copy of this was sent to mmellin_at_my-dejanews.com
(if that email address didn't require changing)
On Fri, 11 Dec 1998 17:16:42 GMT, you wrote:
>I have create a stored procedure is which I Create a user and try to assign
>it to a role. I have system privs to Create User and grant any role. I am
>getting the error that the role is missing, but after the procedure runs I
>can grant the rights sucessfully. GRANT RNOW_USER_ROLE TO "user"; Why wont it
>recognize the name of the role in PL\SQL.
cutting and pasting from your code:
> 'GRANT rnow_user__role
^^ looks like a TYPO?
> TO '||username_in, DBMS_SQL.V7);
>Also when the username entered has
>a numeric at the first point in the string I get "missing user or role name".
>Here is the code. Any help would be great.
thats because an identifier cannot start with a number in Oracle. Specifically (from the sql reference manual)
The following rules apply when naming objects: 1. Names must be from 1 to 30 characters long with these exceptions: - Names of databases are limited to 8 characters. - Names of database links can be as long as 128 characters.
2. Names cannot contain quotation marks.
3. Names are not case–sensitive
4. A name must begin with an alphabetic character from your database character set unless surrounded by double quotation marks.
5. Names can only contain alphanumeric characters from your
database character set and the characters _, $, and #. You are
strongly discourage from using $ and #.
If your database character set contains multi–byte characters, It is
recommended that each name for a user or a role contain at least
one single–byte character.
Names of database links can also contain periods (.) and
ampersands (@).
6. A name cannot be an Oracle7 reserved word. The following list contains these reserved words. Words followed by an asterisk (*) are also ANSI reserved words.
You can use 'quoted' identifiers, so for example:
SQL> exec sp_adduser( '"123ABC"', 'test' ); PL/SQL procedure successfully completed.
SQL> grant connect to 123ABC;
grant connect to 123ABC
*
SQL> grant connect to "123ABC";
Grant succeeded.
SQL> connect 123ABC/test
Connected.
SQL> show user
user is "123ABC"
I would *NOT* suggest this (quoted identifiers) as you never know what tool, what system just might not accept them.
[snip]
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Dec 11 1998 - 12:21:18 CST
 |
 |