- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: grant "almost" dba privies on specific schema
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-04-2008 01:07 PM
тАО12-04-2008 01:07 PM
I know... the question will be, well what do you want to grant him.
I need to make this easy, I thought of doing this in OEM, but click-i-tis started happening.
I do not want to grant CREATE ANY, etc.
the resource role looked good, but is does not allow to create views, drop views, create anything from within his schema, triggers, sequences, procedures, as well as DROP and ALTER etc...
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-14-2008 03:59 AM
тАО12-14-2008 03:59 AM
Re: grant "almost" dba privies on specific schema
you can grant these roles if you don't need it to create any
SQL> GRANT connect TO user;
SQL> GRANT dba TO user;
SQL> GRANT exp_full_database TO user;
SQL> GRANT imp_full_database TO user;
SQL> GRANT unlimited tablespace TO user;
SQL> GRANT select any table TO user;
SQL> GRANT select any dictionary TO user;
SQL> GRANT execute ON sys.dbms_lock TO user;
SQL> COMMIT;
SQL> CONNECT user/password;
HTH
AINMS
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-15-2008 03:58 AM
тАО12-15-2008 03:58 AM
Re: grant "almost" dba privies on specific schema
CONNECT role is a good start. See the system privileges of roles in dba_sys_privs:
select *
from ROLE_SYS_PRIVS
where role = 'CONNECT'
Regardless of roles, the user will be able to drop and alter all objects in HIS schema.
Best Regards,
Eric Antunes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-29-2008 06:02 AM
тАО12-29-2008 06:02 AM
SolutionI would recommend you to create a new role - ROLE_XXX.
Then you can either grant role CONNECT (if you use Oracle 9i - http://my.opera.com/onyxluo/blog/change-of-connect-role-in-oracle-10g) to ROLE_XXX or grant all appropriate system privileges to ROLE_XXX.
Grant CREATE SESSION to ROLE_XXX
Grant CREATE VIEW to ROLE_XXX
Grant CREATE TABLE to ROLE_XXX
Grant CREATE SYNONYM to ROLE_XXX
Grant CREATE SEQUENCE to ROLE_XXX
├в ┬ж├в ┬ж.
The list of all (Oracle) system privileges is here: http://www.adp-gmbh.ch/ora/admin/system_privileges.html
Finally, grant ROLE_XXX to your user.
Regards,
Vadim
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-29-2008 02:50 PM
тАО12-29-2008 02:50 PM
Re: grant "almost" dba privies on specific schema
create session
alter session
create table
create view
create trigger
create procedure
create sequence
create type
create synonym
create cluster
create materialized view
create materialized view log
Two things to note:
First, if you own an object, you automagically have privileges to alter, drop, whatever on that object. However, "create session" and "alter session" are different, because these are seperate system privleges, not object privleges.
Second, any object that takes up space (tables, indexes, anything that has a "tablespace" clause ...) also seperately requires a quota on a tablespace somewhere. You still have to do this manually, can't grant a quote to a role.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-06-2009 12:31 PM
тАО01-06-2009 12:31 PM