Where are the privileges PE and PD from on a procedure?

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Where are the privileges PE and PD from on a procedure?

Hello,

 

I have a quenstion about the privileges PE and PD on a procedure. I create a procedure and check in dbc.allrights the users who have the direct privileges on this procedure. I found that there is always the same user who has the two privileges PE and PD on a procedure in a specific database even if we change the creator to create the procedure.

 

For exemple, I tried creating the same procedure with userA and userB, but it's always userA having the PE and PD on the procedure. Finally, I found that I have to create the procedure with userA. Otherwise it has the problem when I try to execute the procedure with any other user who has the proper privilege to execute it.

 

Who know where are from the two privileges PE and PD on a procedure if it isn't lied to the creator?

 

Thanks


Accepted Solutions
Teradata Employee

Re: Where are the privileges PE and PD from on a procedure?

UserB creating a procedure should never grant automatic rights to UserA.

Check DBC.AllRightsV, particularly the CreatorName and CreateTimeStamp fields, to see where those privileges originated.

 

Note that if you REPLACE an existing object (versus CREATE of a new object), the existing permissions will be retained.

1 ACCEPTED SOLUTION
5 REPLIES
Teradata Employee

Re: Where are the privileges PE and PD from on a procedure?

EXECUTE PROCEDURE (PE) is not automatically granted to the Creator of the SP, unless the creator is also the Owner.

 

See the SQL Stored Procedures and Embedded SQL manual chapter on SQL Stored Procedures for more details. Also note how the SQL SECURITY clause interacts with the permissions needed to successfully execute the procedure.

 

Enthusiast

Re: Where are the privileges PE and PD from on a procedure?

Thanks for you reply, Fred. I want to know why when I create a procedure with userB, it's userA who has the PE and PD privileges. The userA is not the owner of this procedure. The PE and PD are attributed based on which rule? Thanks.

Teradata Employee

Re: Where are the privileges PE and PD from on a procedure?

UserB creating a procedure should never grant automatic rights to UserA.

Check DBC.AllRightsV, particularly the CreatorName and CreateTimeStamp fields, to see where those privileges originated.

 

Note that if you REPLACE an existing object (versus CREATE of a new object), the existing permissions will be retained.

Enthusiast

Re: Where are the privileges PE and PD from on a procedure?

Okay, thanks Fred. I understand. I thought that when I replace a procedure with a user, the creator will be this user. As you explained, when a procedure is replaced, the creator rests the same!  

PS: there is a little bug in Teradata administrator, because I visualize a procedure for a database in administrator, when I replace a procedure with userA, the creator will change to userA too, even if the origin creator is userB. And the creator information in dbc.allrights is correct always userB when I replace procedure with another user.

 

bug.jpg

Teradata Employee

Re: Where are the privileges PE and PD from on a procedure?

When you REPLACE an object, the user doing the REPLACE is the creator of the replacement object itself. (This is what you see in TD Administrator.)

However, automatic rights are granted only when a new object is created (with the creator of the object also being recorded as the creator of those access rights).

Access rights do not change when you REPLACE an existing object.