Archives of the TeradataForum

Message Posted: Tue, 21 Nov 2006 @ 18:32:10 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Alan,

You are right.

I think you will need to create separate role for your business user.

The security ADMIN USER will need to have GRANT ALL permissions in all 2000 DB.

If you want to create a role for security these are the permissions that you would need

     GRANT
     CHECKPOINT
     ,CREATE DATABASE
     ,CREATE MACRO
     ,CREATE TABLE
     ,CREATE TRIGGER
     ,CREATE VIEW
     ,DELETE
     ,DROP DATABASE
     ,DROP MACRO
     ,DROP TABLE
     ,DROP TRIGGER
     ,DROP VIEW
     ,ALTER EXTERNAL PROCEDURE
     ,ALTER FUNCTION
     ,ALTER PROCEDURE
     ,CREATE EXTERNAL PROCEDURE
     ,CREATE FUNCTION
     ,CREATE PROCEDURE
     ,DROP FUNCTION
     ,DROP PROCEDURE
     ,DROP TRIGGER
     ,EXECUTE FUNCTION
     ,EXECUTE PROCEDURE
     ,DUMP
     ,EXECUTE
     ,INSERT
     ,RESTORE
     ,SELECT
     ,UPDATE

     ON 

      TO ROLE_SECURITY_USR WITH ADMIN OPTION;

Admin option will help the security administrator to run similar grants should there be new members who need similar permissions.

Apart from these you will need to give

     GRANT ROLE TO  WITH GRANT OPTION;
     GRANT PROFILE TO  WITH GRANT OPTION;
     GRANT CREATE USER, DROP USER TO  WITH GRANT OPTION; --
           Applicable if this UID is used for user administration too.

Above grants cannot be granted to a role, they will showup as rows in DBC.AllRights view.

After this initial setup,, the security administrator can now start creating new roles in the enviornment.

Bear in mind that roles not necessarily increase the performance.

You may want to have rights assigned to few roles than assigning multiple roles.

I believe it is CPU overhead on PE to span thru these information.

Good luck.

Thanks,

Vinay Bagare

	<Prev	Next>		<<First	<Prev	Next>	Last>>